Stored procedure is very good example of sql scripting. Early in my career, i worked as a sql developer in that phase learnt a lot like how to write store procedures and triggers? How to implement business functionality using stored procedure without changing front-end code of the application? Writing scripts in sql server is less costly compare to front-end level changes and consume less time too. Back-end level or database object level changes are easy to deploy and implement on production compare to front-end level.
Sql scripting can be done with the help of stored procedures, views and triggers. Most of developers makes use of stored procedure for validating data on click of event. Stored Procedure is nothing but the set of T-SQL statements which execute at run time.
Syntax for Stored procedure:
CREATE PROCEDURE uspPrcExample
DECLARE @EMPID Int
Select @EMPID= [Column_name] from [tablename] where [Column_name]= ‘xyz’
In above example we are creating a simple procedure, we have declared variable to hold temp value from table (user/system table name) and printing variable value using select statement.
How to execute stored procedure?
Alternative option for execute stored procedure is select the procedure name that is “uspPrcExample” and press F5 to execute in query window or analyzer from SSMS in sql sever 2008 (SQL SERVER MANAGEMENT STUDIO) .
You can modify procedure using ALTER PROCEDURE command and compile the same.
To know more follow the link : Understanding of Store Procedure in SQL Server 2005