none
Dynamic TSQL Statement with Dynamic Parameters

    Question

  • I'm trying to utilize a dynamic TSQL Statement where I can have various parameters passed of differing kinds, e.g. In some cases parameter 1 would be an int, other cases it may be a datetime, or varchar, etc.

    I'm going to keep  a table of with certain key SQL Statements, and then parameters in another column so this can be resusable.

    Here is my code:

    Case 1

    
    
    Declare @FromDate as DATE='2013-10-01'
    Declare @ToDate as DATE='2013-10-31'
    Declare @FamilyMember as nvarchar(2)='20'
    DECLARE @retval int   
    DECLARE @sSQL nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);
    
    DECLARE @tablename nvarchar(50)  
    
    --Select Convert(nvarchar(15), @FromDate,126)
    
    SELECT @sSQL = N'select count(distinct id) as AggregateCount from [Table] where familyMember = @FamilyMember 
    and  DateStamp between @FromDate and @ToDate';
    
    
    
    SET @ParmDefinition = N'@retvalOUT int OUTPUT';
    
    EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
    
    

    Case 2

    Declare @FromDate as DATE='2013-10-01'
    Declare @ToDate as DATE='2013-10-31'
    Declare @Id as int=3510021
    DECLARE @retval int   
    DECLARE @sSQL nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);
    
    DECLARE @tablename nvarchar(50)  
    
    --Select Convert(nvarchar(15), @FromDate,126)
    
    SELECT @sSQL = N'select count(distinct id) as AggregateCount from [Table] where Id=@Id
    and  DateStamp between @FromDate and @ToDate';
    
    
    
    SET @ParmDefinition = N'@retvalOUT int OUTPUT';
    
    EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;


    John

    Friday, December 20, 2013 10:46 PM

Answers

  • SET @ParameterList = N'@P_StudentNumber integer'
     SET @SQLSnippit = N'Select Count(*) from dbo.student where patid=@StudentNumber'

    This should be @P_StudentNumber. The variable @StudentNumber is not visible inside the dynamic SQL as the dynamic SQL is a scope of its own.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by vsla Friday, December 20, 2013 11:51 PM
    Friday, December 20, 2013 11:18 PM

All replies

  • The following is an example I found, but I am receiving a Message "Must declare the scalar variable @StudentNumber"

    Alter Procedure [dbo].[spInsertStudentDoc2] 
    (
     @StudentNumber  integer 
     )           
    AS
    Begin
    
    
     DECLARE @P_StudentNumber integer
     DECLARE @ParameterList nvarchar(max)
     DECLARE @SQLSnippit as nvarchar(max)
     
     SET @ParameterList = N'@P_StudentNumber integer'
     SET @SQLSnippit = N'Select Count(*) from dbo.student where patid=@StudentNumber'
      PRINT @SqlSnippit -- debug & test
     Exec SP_EXECUTESQL @SqlSnippit, @ParameterList, @P_StudentNumber=@StudentNumber
    End                 
    
    


    John

    Friday, December 20, 2013 11:02 PM
  • SET @ParameterList = N'@P_StudentNumber integer'
     SET @SQLSnippit = N'Select Count(*) from dbo.student where patid=@StudentNumber'

    This should be @P_StudentNumber. The variable @StudentNumber is not visible inside the dynamic SQL as the dynamic SQL is a scope of its own.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by vsla Friday, December 20, 2013 11:51 PM
    Friday, December 20, 2013 11:18 PM
  • Awesome. Thank you!

    New code:

    Alter Procedure [dbo].[spInsertStudentDoc2] 
    (
     @StudentNumber  integer 
     )           
    AS
    Begin
    
    
     DECLARE @P_StudentNumber integer
     DECLARE @ParameterList nvarchar(max)
     DECLARE @SQLSnippit as nvarchar(max)
     
     SET @ParameterList = N'@P_StudentNumber integer'
     SET @SQLSnippit = N'Select Count(*) from dbo.student where patid=@P_StudentNumber'
      PRINT @SqlSnippit -- debug & test
     Exec SP_EXECUTESQL @SqlSnippit, @ParameterList, @P_StudentNumber=@StudentNumber
    End                 
    


    John

    Friday, December 20, 2013 11:51 PM