Ejemplo de como crear un Stored Procedure en SQL Server 2005

Ejemplo de como crear un Stored Procedure en SQL Server 2005:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER|CREATE PROCEDURE [dbo].[NombreStoredProcedure] (
@param1 varchar(50) = NULL,
@param2 int = NULL,
....

) WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @where_string varchar(500)
DECLARE @order_string varchar(100)
DECLARE @join_string varchar(500)

SET @where_string = '';
SET @order_string = '';
SET @join_string = '';


IF (@filtervalCaseNum IS NOT NULL OR @filtervalCaseNum != '' )
BEGIN
SET @where_string = ' ccaseno LIKE '''+@filtervalCaseNum+'%'' AND ';
END
IF (@filtervalJudicialReview IS NOT NULL OR @filtervalJudicialReview != '' )
BEGIN
SET @where_string = @where_string + ' CONVERT(varchar(12),CaseProgram.dt_jreview,101) LIKE '''+@filtervalJudicialReview+'%'' AND ';
END
IF (@filtervalProgType IS NOT NULL OR @filtervalProgType != '' )
BEGIN
SET @where_string = @where_string + ' Program.description LIKE '''+@filtervalProgType+'%'' AND '
SET @join_string = @join_string + ' INNER JOIN Program ON CaseProgram.program_id=Program.program_id ';

END

DECLARE @str_sql varchar(3000)

SET @str_sql = '
SELECT count(CaseProgram.caseprogram_id)
FROM CaseProgram LEFT OUTER JOIN [Case] ON CaseProgram.case_id=[Case].case_id
LEFT OUTER JOIN Client on Client.client_id=[Case].client_id
'+ @join_string +'
WHERE '+ @where_string+' 1=1 '


PRINT @str_sql ;
EXEC (@str_sql)
END