SQL Server sp_execute - Error with DateTime Variable in the Dynamic SQL

Question

I tried to create dynamic SQL using sp_executesql, but it gives me this error message:

Msg 137, Level 15, State 2, Line 20
Must declare the scalar variable "@start".

Here is my stored procedure script

CREATE PROCEDURE sp_test (@start datetime, @end datetime)
AS
BEGIN
   DECLARE @sql nvarchar(MAX)

   SET @sql = 'SELECT * FROM table1 WHERE '
   SET @sql = @sql + N'startDate BETWEEN @start AND @end'

   EXEC sp_executesql @sql

Any input will be appreciated.

Answer 1

The below T-SQL should take care of your problem. Although, I would not recommend prefixing your stored procedure name with "sp_" since the system stored procedures use this naming convention. You wouldn't want to have your stored procedures confused with system stored procedures, or worse, Microsoft decides to name one of their future system stored procedures with the name of yours.

Things to note:

• You define/declare all the custom parameters that you want to pass into your dynamic SQL statement in the @ParameterDefinition variable.

• You add each of your custom variables to the sp_executesql call as if they were already part of the procedure.

  IF OBJECT_ID('sp_test', 'P') IS NOT NULL DROP PROCEDURE sp_test
  GO
  -- ============================================================================
  -- CALLING EXAMPLE:
  -- EXEC sp_test '01/01/1901', '01/02/1901'
  -- ============================================================================
  CREATE PROCEDURE sp_test (@start datetime, @end datetime)
  AS
  BEGIN
  
  DECLARE @sql nvarchar(max)
  
  SET @sql = 'SELECT * FROM table1 WHERE '
  SET @sql = @sql + N'startDate BETWEEN @start AND @end'
  
  -- Build the Parameter Definition list for the dynamic SQL statement below  
  DECLARE @ParameterDefinition nvarchar(1000);
  SELECT @ParameterDefinition = ''  
  + ' @start datetime'  
  + ',@end datetime' 
  
  EXEC sp_executesql  
      @statement  = @sql  
     ,@params     = @ParameterDefinition  
     -- Assign values to any of custom parameters defined in @ParameterDefinition:  
     ,@start      = @start  
     ,@end        = @end  
  
  END
  GO