What is a SQL stored procedure? SQL stored procedure is a saved collection of Transact-SQL statements that that can take and return user-supplied parameters. It is kind of the programming for SQL. It is supporting Microsoft .NET Framework common language runtime (CLR) method with Microsoft SQL Server 2008.

Example of CREATE PROCEDURE (Transact-SQL),

IF OBJECT_ID ( ‘dbo.sp_test’, ‘P’ ) IS NOT NULL
DROP PROCEDURE dbo.sp_test;
GO
CREATE PROCEDURE dbo.sp_test
AS
SELECT LastName, FirstName, JobTitle, Department
FROM dbo.test;
GO

And the stored procedure can be executed by

EXECUTE dbo.sp_test;
GO
– Or
EXEC dbo.sp_test;
GO
– Or, if this procedure is the first statement within a batch:
dbo.sp_test;

Tags: Transact-SQL