Programming Code Examples

Different Ways to script out stored procedures in SQL Server4 min read

Creating a new procedure is like visiting a new place ,while knowing how to script them is like reliving those memories.

What is Stored procedure ?

A Stored procedure is something like a set of code that contains many insert ,update ,select ,delete ,etc( basically all the ddl,dml commands that you use in sql server). An example – A situation may arise in which data has to be inserted into a table and updated simultaneously ,but it will be difficult to use these separately as queries .We have to get the inputs each time separately for insert and update and run the query manually each time . But this is ultimately the reason to go for a stored procedure. Stored procedures bind some set of operations into the same set and just run all operation in one go. That’s pretty simple !!!

Simple Stored procedure creation and basics :

The below is an example stored procedure used for insertion and updation . The example is to create a store procedure that inserts data into StudentsData_test table . The insertion into the table is based on the Reg_no (Register number ). If the Reg_no is already present updation must happen or else insertion has to happen.

Insertion and Updation just in one go !!!. Just by passing different inputs we can make the procedure work as per our need. After this we will need to script out the stored procedure for various purposes and let’s see few of them.

Few ways to script out store procedure:

1. Using Sp_Helptext:

The above query can be used to script out existing procedure .

2. Object Explorer DrillDown:

This is also one of the easiest ways to script out procedures using object explorer. Click on Databases –>LessonDBDev (Choose the name of your database) —>Programmability —>Stored Procedures —>Name of the procedure

Now right click on the name of the procedure then choose ‘Script Stored procedure as ‘ and then use ‘Create To’ —> New query window . Now we shall find a new query window opening up along with the script of the procedure .

During this process One can also choose the option File (below new query window ) just in case the script is needed to be written to a file .

3. Using sys.sql_modules :

Sys.Sql_modules is another method for viewing the definition of the procedure .The below query can be used.

4. Using Object_Definition:

Object_Definition is similar to Sys.Sql_Modules which also provides the definition of the procedure.

Hence these are the few methods to script out procedure that is already existing .

Add comment