This example demonstrates how to modify a store procedure in the current database. In my previous example, i have explained how to create and delete a store procedure from the current database, i will further elaborate the previous example to modify the store procedure using T-SQL.
Syntax
ALTER { PROC | PROCEDURE } [owner.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Following script will modify the store procedure from the database.
ALTER PROCEDURE XP_GETDATETIME
AS
BEGIN
SELECT CONVERT(VARCHAR, GETDATE(), 109)
END
Note:If you don't have alter permission then you can not modify the store procedure from the current database.
Tips: Do not create store procedure with sp_ as prefix to avoid conflict with system store procedure.