Syntax
CREATE PROC [ EDURE ] procedure _name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
(source SQL Help documentation)
That's a brief description and syntax about Stored Procedure.
Simple sample code :
/*
created : 10-12-2008 by Wihendro
Note : here you explain about the procedure function and purpose
*/
Create Proc dbo.spr_Test
as
set nocount on
select * from orders
That's all the brief SP best on my experience.
The /*...*/ ( a comment )filled in SP use to help us to monitor the changes made by the programmer. It may help us to track back the purpose of the SP. In application development using sql server as database, it may contain a lot of SP. After a decade of the development, if you need to change your code, you may encounter some difficulty about the purpose of the SP. So, in my opinion, you should use a comment above your SP.
"set nocount on" syntax use to set the message from sql server to off. every single statement either success or failed to execute, the sql server may send the message as part of the result execution. I used set nocount on to set it off. despite of receiving it, you may hide it. because you don't need it from your SP.
Ok, that's all for this time. For whom that doesn't know the SP , at least now know how to create it. and for others, may use my tips to add the comment and "set nocount on" on your SP. Wait for my next post more about Stored Procedure.
Have a Nice Day
No comments:
Post a Comment