dynamic1.sql

-- Prepare Dynamic SQL
			SET @sql = '' +
			'SELECT @exists = COUNT(*) from ats.R_RECORD ' +
			'WHERE R_ID = @id ' + 
			CASE
				WHEN @R_PARENT IS NULL THEN ''
				ELSE 'AND R_PARENT = @R_PARENT '
			END +
			CASE
				WHEN @STAT_ID IS NULL THEN ''
				ELSE 'AND STAT_ID = @STAT_ID '
			END +
			CASE
				WHEN @FY_ID IS NULL THEN ''
				ELSE 'AND FY_ID = @FY_ID '
			END +
			CASE
				WHEN @ASUB_ID IS NULL THEN ''
				ELSE 'AND ASUB_ID = @ASUB_ID '
			END +
			CASE
				WHEN @AGRT_ID IS NULL THEN ''
				ELSE 'AND AGRT_ID = @AGRT_ID '
			END +
			CASE
				WHEN @R_LEVERAGED_CONTRACT_NUM IS NULL THEN ''
				ELSE 'AND R_LEVERAGED_CONTRACT_NUM = @R_LEVERAGED_CONTRACT_NUM '
			END +
			CASE
				WHEN @R_BRANCH_ORDER_NO IS NULL THEN ''
				ELSE 'AND R_BRANCH_ORDER_NO = @R_BRANCH_ORDER_NO '
			END +
			CASE
				WHEN @R_ITB_ORDER_NO IS NULL THEN ''
				ELSE 'AND R_ITB_ORDER_NO = @R_ITB_ORDER_NO '
			END +
			CASE
				WHEN @R_TERM_START IS NULL THEN ''
				ELSE 'AND R_TERM_START = @R_TERM_START '
			END +
			CASE
				WHEN @R_TERM_END IS NULL THEN ''
				ELSE 'AND R_TERM_END = @R_TERM_END '
			END +
			CASE
				WHEN @R_DESCRIPTION IS NULL THEN ''
				ELSE 'AND R_DESCRIPTION = @R_DESCRIPTION '
			END +
			CASE
				WHEN @R_LOWBID IS NULL THEN ''
				ELSE 'AND R_LOWBID = @R_LOWBID '
			END +			
			CASE
				WHEN @R_BIDS IS NULL THEN ''
				ELSE 'AND R_BIDS = @R_BIDS '
			END +
			CASE
				WHEN @R_DVBE_INCENTIVE IS NULL THEN ''
				ELSE 'AND R_DVBE_INCENTIVE = @R_DVBE_INCENTIVE '
			END +
			CASE
				WHEN @R_CONSULTING_SERVICE IS NULL THEN ''
				ELSE 'AND R_CONSULTING_SERVICE = @R_CONSULTING_SERVICE '
			END +
			CASE
				WHEN @BRNT_ID IS NULL THEN ''
				ELSE 'AND BRNT_ID = @BRNT_ID '
			END +
			CASE
				WHEN @S_ID IS NULL THEN ''
				ELSE 'AND S_ID = @S_ID '
			END +
			CASE
				WHEN @AMTH_ID IS NULL THEN ''
				ELSE 'AND AMTH_ID = @AMTH_ID '
			END +
			CASE
				WHEN @VADR_ID IS NULL THEN ''
				ELSE 'AND VADR_ID = @VADR_ID '
			END +
			CASE
				WHEN @R_AMOUNT IS NULL THEN ''
				ELSE 'AND R_AMOUNT = @R_AMOUNT '
			END +
			CASE
				WHEN @R_MULTI_YEAR IS NULL THEN ''
				ELSE 'AND R_MULTI_YEAR = @R_MULTI_YEAR '
			END +
			CASE
				WHEN @R_REGISTRATION_NO IS NULL THEN ''
				ELSE 'AND R_REGISTRATION_NO = @R_REGISTRATION_NO '
			END +
			CASE
				WHEN @R_CAL_CARD IS NULL THEN ''
				ELSE 'AND R_CAL_CARD = @R_CAL_CARD '
			END +
			''
			
			-- Safely execute this Dynamic SQL
			EXEC sp_executesql @sql,
				N'@R_PARENT INT, @STAT_ID INT, @FY_ID INT, @ASUB_ID INT, @AGRT_ID INT, @R_LEVERAGED_CONTRACT_NUM VARCHAR(25),@R_BRANCH_ORDER_NO varchar(15), @R_ITB_ORDER_NO NUMERIC(5,0),@R_TERM_START DATETIME, @R_TERM_END DATETIME, @R_DESCRIPTION VARCHAR(150), @R_LOWBID bit, @R_BIDS int, @R_DVBE_INCENTIVE bit, @R_CONSULTING_SERVICE bit, @BRNT_ID INT, @S_ID INT, @AMTH_ID INT, @VADR_ID INT, @R_AMOUNT MONEY, @R_MULTI_YEAR BIT, @R_REGISTRATION_NO NUMERIC(14, 0), @R_CAL_CARD BIT, @id INT, @exists BIT output',
				@R_PARENT,
				@STAT_ID,
				@FY_ID,
				@ASUB_ID,
				@AGRT_ID,
				@R_LEVERAGED_CONTRACT_NUM,
				@R_BRANCH_ORDER_NO,
				@R_ITB_ORDER_NO,
				@R_TERM_START,
				@R_TERM_END,
				@R_DESCRIPTION,
				@R_LOWBID,
				@R_BIDS,
				@R_DVBE_INCENTIVE,
				@R_CONSULTING_SERVICE,
				@BRNT_ID,
				@S_ID,
				@AMTH_ID,
				@VADR_ID,
				@R_AMOUNT,
				@R_MULTI_YEAR,
				@R_REGISTRATION_NO,
				@R_CAL_CARD,
				@id,
				@exists output

dynamic2.sql

IF( @STAT_ID = @CLOSED ) BEGIN
				UPDATE ats.R_RECORD 
				SET STAT_ID = @CLOSED
				WHERE R_ID IN (select r_id from ats.vw_r where deleted = 'false' 
					and r_record_no in (select r_record_no from ats.r_record where r_id = @id and deleted='false')
					and fy_id in (select fy_id from ats.r_record where r_id = @id and deleted='false')
					and asub_id in (select asub_id from ats.r_record where r_id = @id and deleted='false')
					and r_id != @id)
			END

gistfile4.txt

skeleton.sql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/*
=============================================
Acquisition Tracking System
Author:	Eric Hayes
Create date: 10th April 2009 15:30
=============================================
Procedure:	ats.[FT_query]
Description:	Record funding details
Table(s) Affected: ats.FT_FUND_TITLE, ats.jn_FT_FUND_TITLE
=============================================
*/
ALTER PROCEDURE [ats].[FT_query]
	@qry nvarchar(max) = '',
	@cols nvarchar(max) = '',
	@success bit OUTPUT,
	@errormsg varchar(255) OUTPUT,
	@errno int OUTPUT,
	@size int OUTPUT
AS
BEGIN
	
	
	BEGIN TRY
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;

		IF( @qry = '' AND @cols = '' )
			BEGIN
				-- Static SQL for the full query with no where clause stipulations
				SELECT FT_ID,
				FT_NAME,
				FT_ITEM_NO
				FROM ats.FT_FUND_TITLE
				WHERE DELETED = 'false'
				
				SET @size = @@ROWCOUNT
			END
		ELSE
			BEGIN
				-- Context switch to the query user (limited user with select access to specific tables only)
				EXECUTE AS USER = 'bso_proc'
			
				-- Dynamic SQL provides us the most query flexibility here (which columns/query conditions)
				DECLARE @sql nvarchar(max)
				SET @sql = 'SELECT ' + 
				
				CASE
					WHEN @cols = '' THEN 'FT_ID,FT_NAME,FT_ITEM_NO'
					ELSE upper(@cols)
				END +
				
				' FROM ats.FT_FUND_TITLE WHERE DELETED = ''FALSE'' ' +
				
				CASE
					WHEN @qry = '' THEN ''
					ELSE 'AND ' + @qry
				END
				
				EXEC(@sql)
				SET @size = @@ROWCOUNT
				REVERT
			END
			
			-- If we get to this point then everything worked
			SET @success = 'true'
	END TRY
	BEGIN CATCH
		SET @success = 'false'
		SET @errormsg = ERROR_MESSAGE()
		SET @errno = ERROR_NUMBER()
		SET @size = 0
	END CATCH

END