Specify the parameter values part of sp_executeSQL as a parameter

Question

I am using SQL Server 2008 R2. I need to use a parameter for the last part of T-SQL below.

In this part, the values of parameters to sp_executesql are specified, but I want to supply it dynamically.

EXECUTE sp_executesql @sql,N'@startIndex1 int,@maxRows1 int',
                      @startIndex1 = @startIndex,@maxRows1 = @maxRows--LAST PART

Answer 1

Based on your further explanation, I think you are making this more complicated than it has to be. You can do the following:

DECLARE @p1 INT = 5, @p2 INT = 3, @p3 INT = NULL;

DECLARE @sql NVARCHAR(MAX) = N'SELECT cols FROM dbo.table WHERE @p1 = @p1';

SET @sql += CASE WHEN @p2 IS NOT NULL THEN N' AND @p2 = @p2' ELSE '' END;

SET @sql += CASE WHEN @p3 IS NOT NULL THEN N' AND @p3 = @p3' ELSE '' END;

EXEC sp_executesql @sql, N'@p1 INT, @p2 INT, @p3 INT', @p1, @p2, @p3;

This will work no problem - any parameters that aren't used in the actual statement are merely dropped and ignored.