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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE PROCEDURE dbo.[TestProcedure] ( --Input Parameters @Reg_No INT, @Name_Of_Student VARCHAR(30), @Age INT, @Marks INT, @Remarks VARCHAR(30) ) AS BEGIN /* Checking whether the reg number is already present or not If not present we are inserting new row( considering it as a new student) , but of it is present then we have to Update */ IF NOT EXISTS(SELECT 1 FROM dbo.[StudentsData_test] WHERE Reg_No = @Reg_No) BEGIN INSERT INTO dbo.[StudentsData_test] (Reg_No,Name_Of_Student,Age,Marks,Remarks) VALUES (@Reg_No, @Name_Of_Student, @Age, @Marks,@Remarks) ; END ELSE BEGIN UPDATE dbo.[StudentsData_test] SET Name_Of_Student = @Name_Of_Student, Age = @Age , Marks = @Marks, Remarks = @Remarks WHERE Reg_No = @Reg_No; END; END; -- HINT - To make changes to this procedure use 'ALTER' procedure in the first line instead of 'CREATE'. |
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:
|
1 2 3 |
EXEC SP_HELPTEXT TestProcedure; |
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.
|
1 2 3 4 |
SELECT DEFINITION FROM SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('TestProcedure'); |
4. Using Object_Definition:
Object_Definition is similar to Sys.Sql_Modules which also provides the definition of the procedure.
|
1 2 3 |
SELECT OBJECT_DEFINITION( OBJECT_ID('TestProcedure') ); |
Hence these are the few methods to script out procedure that is already existing .
