2

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
Sunil
  • 20,653
  • 28
  • 112
  • 197
  • Can you expand a bit and describe exactly what you're trying to do (or avoid)? I'm trying to wrap my head around how many times you're going to have to nest dynamic SQL here to achieve what you want to do, and still have to hard-code something somewhere, and it makes me wonder: ***WHY???*** – Aaron Bertrand Oct 05 '13 at 21:51
  • I am having grid in a web page that has filters in each of its columns. If no filter is specified then the last part is not a problem as values for startIndex and maxRows are always passed. But if there is a filter on Supplier column then I need to add a parameter of 'supplier' dynamically. When multiple columns are filtered, then there will be multiple parameters. I can do this using inline SQL in C# code but I thought stored procedure would be better. The T-SQL code mentioned in my post is part of a stored procedure. – Sunil Oct 05 '13 at 21:58

1 Answers1

10

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • So your approach is to specify a parameter with sp_executesql for every filterable column in the web page grid, BUT only include needed parameters in the sql query. Excellent. That makes sense. THANKS. I did not know that one could specify parameters that do not exist in sql query, but that is the TRICK here. – Sunil Oct 05 '13 at 22:11