Stored Procedures

Question

Hi all,

I don't know the process for writing a stored procedures

please some on let me know how to write stored procedures in a easy way to understand quickly.

Thank you

---

Regards, shiva

Answer 1

Hope this helps:

    http://msdn.microsoft.com/en-us/library/ms345415.aspx

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 2

In SSMS click on New Query, write

CREATE PROCEDURE Test 

AS
 

   BEGIN

          SET NOCOUNT ON ;

         SELECT 'This is my first stored procedure'

  END

RETURN

GO

and run this script - you created the stored procedure.

Now, write

EXECUTE dbo.Test;

and it will execute this stored procedure.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

In SSMS click on New Query, write

CREATE PROCEDURE Test 

AS
 

   BEGIN

          SET NOCOUNT ON ;

         SELECT 'This is my first stored procedure'

  END

RETURN

GO

and run this script - you created the stored procedure.

Now, write

EXECUTE dbo.Test;

and it will execute this stored procedure.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

i have executed as

execute test  and i got o/p This is my first stored procedure'

but what is Nocount on, what does it mean?

---

Regards, shiva

Answer 4

SET NOCOUNT ON is used to prevent output of '1 row affected' messages. Otherwise all DML (data manipulation) commands will print this type of messages about how many rows were affected by the statement.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

First of all you must know that :

1. One of the main benefit of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become more important benefit when the bandwidth of the network is less. Since if we send the sql query (statement)which is executing in a loop to the server through network and the network get disconnected then the execution of the sql statement don't returns the expected results, if the sql query is not used between Transaction statement and rollback statement is not used.
2. Compilation step is required only once when the stored procedure is created. Then after it does not required recompilation before executing unless it is modified and re utilizes the same execution plan whereas the sql statements needs to be compiled every time whenever it is sent for execution even if we send the same sql statement every time.
3. It helps in re usability of the sql code because it can be used by multiple users and by multiple client since we needs to just call the stored procedure instead of writing the same sql statement every time. It helps in reduces the development time.
4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving the permission on the tables used in the Stored procedure.
5. Sometime it is useful to use the database for storing the business logic in the form of stored procedure since it make it secure and if any change is needed in the business logic then we may only need to make changes in the stored procedure and not in the files contained on the web server.

How can write :

Create Procedure Procedure-name 
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
     Sql statement used in the stored procedure
End

And to execute a stored procedure you can write this

Execute StoreProcedurName 1
Exec  StoreProcedurName 1

1 is a parameter

---

OR Vs XOR

Answer 6

but what is Nocount on, what does it mean?

I think Naomi was a little to quick to include the advanced stuff.

Well, advanced and advanced. Many people include SET NOCOUNT ON by routine, but it's not what you need to learn as a beginner.

SET NOCOUNT ON suppresses the message "1 row(s) affected" which is printed after a SELECT, INSERT, UPDATE or DELETE operation.

---

Erland Sommarskog, SQL Server MVP, [e-mail]