none
TSQL-Dynamic SQL: Issues with using the sp_executesql option

    Question

  • SQL Server:  Version 2008 R2

    I'm new to Dynamic SQL which is probably obvious.  I'm trying to use the sp_executesql  option to execute the code since I need to pass parameter values.

    Any ideas where my syntax is incorrect or I'm off base?

    Thanks,

    ...bob sutor

    THE ERROR CODE:

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
    Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

    MY OFFENDING CODE:

    Use Viewpoint
    Go
    Declare @SQL varchar(1000)
    Declare @AuditResult tinyint
    Set @AuditResult = 0
    Set @SQL = N'
     IF EXISTS(Select * from HRTC)
     BEGIN
      SET @AuditResult = 1
     END
     ELSE
      SET @AuditResult = 0;'
    BEGIN
    EXEC sp_executesql @Stmt = @SQL, @params = N'@AuditResult int'
    END
    Select @AuditResult AS Result


    Bob Sutor

    Friday, November 08, 2013 10:35 PM

Answers

  • 1. You are using type varchar and you should use UNICODE that mean using one of those type: ntext/nchar/nvarchar

    2. You should pass the parameter to the SP like this:

    EXEC sp_executesql @Stmt = @SQL, @params = N'@AuditResult int', @AuditResult=0;
    try this:
    Declare @SQL nvarchar(1000)
    
    Set @SQL = N'
     IF EXISTS(Select * from [2121212121212]) 
     BEGIN 
      SET @AuditResult = 1
     END 
     ELSE 
      SET @AuditResult = 0;
     
     select @AuditResult'
    
    BEGIN
    EXEC sp_executesql @Stmt = @SQL, @params = N'@AuditResult int', @AuditResult=0;
    END



    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Friday, November 08, 2013 11:13 PM
    • Proposed as answer by Rechousa Friday, November 08, 2013 11:18 PM
    • Marked as answer by ConstPM Friday, November 08, 2013 11:46 PM
    Friday, November 08, 2013 11:00 PM
  • The same rules apply regardless of the number of parameters passed.  I suggest the following methodology for dynamic SQL statement development.

    1.  Start with the final parameterized SQL statement batch you want to execute:

    SET @dsTestString = 'Test String Works'; SELECT * FROM dbo.[HRTC] WHERE TrainCode = @dsTrainCode;

    2.  Enclose the statement batch in single quotes and double all single quotes embedded in the string:

    'SET @dsTestString = ''Test String Works''  SELECT * FROM dbo.[HRTC] WHERE TrainCode = @dsTrainCode;'

    3.   Remove embedded table/columns names and use concatenation to supply the run-time values (from trusted source):

    'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;'

    4.   Assign the final SQL expression to an nvarchar variable:

    SET @SQL = N'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;';

    5.   Assign the parameter declaration (containing each parameter embedded in the SQL string) to an nvarchar variable, with each parameter specifying the name, data type and direction (which defaults to INPUT):

    SET @params = N' @dsTrainCode varchar(10), @dsTestString varchar(20) OUTPUT';

    6.  Create the execute sp_executesql statement, supplying the SQL statement, declared parameters and mapping of declared parameters to local parameter/variables:

    EXECUTE sp_executesql
    	  @SQL
    	, @params
    	, @dsTrainCode = @TrainCode
    	, @dsTestString = @TestString OUTPUT;

    7.  Test the final SQL is:

    DECLARE @debug bit = 0,
      @TableName sysname = N'HRTC',
      @TrainCode nvarchar(10) = N'SF',
      @SQL nvarchar(1000),
      @params nvarchar(1000),
      @TestString nvarchar(50);
    
    SET @SQL = N'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;';
    
    SET @params = N' @dsTrainCode varchar(10), @dsTestString varchar(20) OUTPUT';
    
    EXECUTE sp_executesql
    	  @SQL
    	, @params
    	, @dsTrainCode = @TrainCode
    	, @dsTestString = @TestString OUTPUT;
    
    IF @debug = 1 PRINT @SQL;
    
    PRINT @TestString;
    GO

     

     

     

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, November 10, 2013 2:02 PM corrected step 2 script
    • Marked as answer by ConstPM Sunday, November 10, 2013 5:34 PM
    Sunday, November 10, 2013 2:00 PM

All replies

  • 1. You are using type varchar and you should use UNICODE that mean using one of those type: ntext/nchar/nvarchar

    2. You should pass the parameter to the SP like this:

    EXEC sp_executesql @Stmt = @SQL, @params = N'@AuditResult int', @AuditResult=0;
    try this:
    Declare @SQL nvarchar(1000)
    
    Set @SQL = N'
     IF EXISTS(Select * from [2121212121212]) 
     BEGIN 
      SET @AuditResult = 1
     END 
     ELSE 
      SET @AuditResult = 0;
     
     select @AuditResult'
    
    BEGIN
    EXEC sp_executesql @Stmt = @SQL, @params = N'@AuditResult int', @AuditResult=0;
    END



    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Friday, November 08, 2013 11:13 PM
    • Proposed as answer by Rechousa Friday, November 08, 2013 11:18 PM
    • Marked as answer by ConstPM Friday, November 08, 2013 11:46 PM
    Friday, November 08, 2013 11:00 PM
  • A couple of things.  First, I need to provide the value of @AuditResult to the stored procedure in which the dynamic sql is located.  I also added @User which I wanted as an OUTPUT parameter.  Based on pituach's suggestions and suggestions in Erland Sommarskog's article, The Curse and Blessings of Dynamic SQL I made the code change below; But now I'm getting this error message:

    Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

    Msg 102, Level 15, State 1, Line 20

    Incorrect syntax near ')'.

    REVISED CODE:

    Use Viewpoint
    Go

    Declare @SQL nvarchar(1000)
    Declare @params nvarchar(1000)
    Declare @AuditResult int

    Declare @UserName nvarchar(50)

    SET @UserName = N'JSmith'

    Set @SQL = N'

    DECLARE @Result int, @User nvarchar(50)

     IF EXISTS(Select * from HRTC)
     BEGIN
     SET @Result = 1
     SET @User = @UserName + ''-Pass''
     END
     ELSE
      SET @Result = 0;'
     
    Set @params = N'@UserName, @Result = @AuditResult OUTPUT, @User = @UserName OUTPUT'
     
    BEGIN
    EXEC sp_executesql @Stmt = @SQL, @params;
    Print cast(@AuditResult as nchar(1))


    Bob Sutor



    • Edited by ConstPM Saturday, November 09, 2013 12:18 AM clarify hopefully
    Saturday, November 09, 2013 12:00 AM
  • Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

    What happens if you actually read the error message? This is the line of code the message pertains to:

        EXEC sp_executesql @Stmt = @SQL, @params;

    (Which you could have found out by double-clicking the error message.)

    Once you have corrected this error, you will find that there are more prolems with this line. And once you have the call working, you will find that there are problems inside your dynamic SQL.

    And, no, I am not saying more. It's by reading error messages and understand what the errors that you learn.

    (And there is little reason to use dynamic SQL at all in the code you have, but it may still serve as a good learning exercise.)

    Msg 102, Level 15, State 1, Line 20
    Incorrect syntax near ')'.

    So this is a little more puzzling, since I can't see a right parenthesis around line 20 in the code you posted. But again, double-clicking the line will take you to the right line.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, November 09, 2013 9:19 AM
  • Pretty clear, I think your problem starts before using dynamic query. You can not jump in the middle of the book and start reading, if you want to understand what is really going on :-)

    We can see that you set out a procedure internal parameters of the procedure, outside the procedure. You use the same names for the external parameters and internal parameters so you get confused.

    For examples: why do you declare @params?

    ** I highly recommend you leave now the use of a dynamic procedure for a few hours. Go back study of the use of your own procedure. Get used to the writing and the use of using standard procedures. Focus on working with internal parameters and external data transfer to the parameters. Then come check to the use of a procedure that someone else wrote for you, the sp_executesql :-)

    * @params is sp_executesql internal parameter that get an external value of a list with all the dynamic internal parameters that you are going to use in your dynamic query.

    * If you declare an internal parameter using @params then you should pass his value as another external data pass to the SP like this:

    EXEC sp_executesql @Stmt = @SQL, @params = N'@A1 int', @A1=0;
    -- Or using two parameters like this:
    EXEC sp_executesql @Stmt = @SQL, @params = N'@A1 int,@A2 nvarchar(10)', @A1=0, @A2='Yes I Can';

    Using @params i declare an internal parameter names @AuditResult for my dynamic query, then i pass the value to this parameter by using ", @AuditResult=0".


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituach Saturday, November 09, 2013 10:31 AM
    Saturday, November 09, 2013 10:06 AM
  • To add to the other responses, there is no need to use dynamic SQL here:

    DECLARE @AuditResult tinyint = 0;
    SELECT @AuditResult = 1 WHERE EXISTS(SELECT * FROM HRTC);
    SELECT @AuditResult AS Result;

    It is generally best to avoid dynamic SQL unless there is a specific need to do so.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, November 09, 2013 1:32 PM
  • Dan, That's True, but as i understood he is just trying to learn using dynamic query, and not to use on real sever. If so, his goal is not the result, but the understanding :-)


    [Personal Site] [Blog] [Facebook]signature

    Saturday, November 09, 2013 3:12 PM
  • Thanks to everyone for your input.  As pituach suggested I have already read a ton of stuff on this and I'm still not clear.  My code is an abstraction, I fully realize dynamic sql is unnecessary if all I want to do is perform the work of this code.  What I'm hoping to accomplish by getting this code to run is:

    1. Understand how to pass multiple variables from the outer SP to the dynamic code.
    2. Understand how to pass values of multiple OUTPUT variables from the dynamic sql back to the outer SP.

    Based on suggestions to clarify my code I have prefixed common variables used in both the outer SP and the dynamic sql code using different names.  The variable I assume belong to the dynamic sql are prefaced with a "ds" using the same name as the related variable used in the outer SP. 

    So even after reading all the web stuff, etc., etc., I'm still really unclear on how to structure the @params parameters so they will work.  I want to be able to know how to set values of the INPUT parameters in @params using the related variables in the outer SP, setting using constants doesn't help.  Again this code is an abstraction for learning dynamic sql.  

    The following code runs but it still fails to function correctly as follows:

    1. The value of @TrainCode of 'SF' is not being passed to the dynamic sql variable, @dsTrainCode.  Accordingly when executing; WHERE TrainCode = ''@dsTrainCode'' it is returning FALSE since @dsTrainCode is NULL rather than 'SF' and I get an empty result set returned.
    2. The PRINT @TestString at the end of the script is not executing.
    3. @debug doesn't seem to do anything??

    My revised code is below.

    Thanks

    ...bob sutor

    REVISED CODE:

    Declare @debug bit = 0,
     @TableName nvarchar(20) = 'HRTC',
     @TrainCode nvarchar(10) = 'SF',
     @SQL nvarchar(1000),
     @params nvarchar(1000),
     @TestString nvarchar(50)
     
    Set @SQL = N' Set @dsTestString = ''Test String Works''  Select * From dbo.'
    Set @SQL = @SQL + quotename(@TableName)
    SET @SQL = @SQL + ' WHERE TrainCode = ' '@dsTrainCode' ' '
    SET @params = N' @dsTrainCode varchar(10), @dsTestString varchar(20) OUTPUT'

    EXECUTE sp_executesql @SQL, @params, @dsTrainCode = @TrainCode, @dsTestString = @TestString OUTPUT
    IF @debug = 1 PRINT @SQL
    PRINT @TestString

     


    Bob Sutor

    • Edited by ConstPM Sunday, November 10, 2013 4:13 AM
    Sunday, November 10, 2013 2:27 AM
  • Please read my previous post depth.

    You should get some experience in practice and not just in reading (using stored procedure). After you will succeed and you will understand how to do all the things you wish using your own procedure , then we will go back to using someone else's procedure. Now seems that we are wasting our time games, when you lack the basics. It's not about using sp_executesql, but about using parameters in stored procedure.

    * I do not like to pull out the trump card of the experience :-). I do not think ability is measured by time experience. There are many people that after 20 years in the field do not know more than they knew 20 years ago, but maybe in this case i will use it.

    Please try to rely on the experience of over 25 years of teaching courses, high school and university, and practical experience in the field of approximately 15 years. Your mistakes are aimed to your problem, which is not specific related using sp_executesql procedure, but use parameters in general.

    * English is not my native language, and is difficult to express myself freely. I can not put the full explanation in a forum thread. It is better to find a good tutorial for SP and parameters on net. and again read my previous post :-)


    [Personal Site] [Blog] [Facebook]signature

    Sunday, November 10, 2013 9:33 AM
  • 1. The value of @TrainCode of 'SF' is not being passed to the dynamic sql variable, @dsTrainCode. 

    Why do you think that? That part looks OK to me.

    Accordingly when executing; WHERE TrainCode =''@dsTrainCode''
    it is returning FALSE since @dsTrainCode is NULL rather than 'SF' and I

    The value of the variable @dsTrainCode is of absolutely no importance here, as it does not appear in the expression. You are comparing the column to a string constant. Look at this:

    CREATE TABLE #junk (a int NOT NULL, b nvarchar(20) NOT NULL)
    INSERT #junk (a, b)
       VALUES (1, '@alfa'), (2, 'beta')
    DECLARE @alfa nvarchar(20) = 'beta'
    SELECT * FROM #junk WHERE b = '@alfa'
    DROP TABLE #junk

    A string constant that starts with a @ is still a string constant. There is no string interpolation in T-SQL as there is in some languages like Perl.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 10, 2013 1:00 PM
  • The same rules apply regardless of the number of parameters passed.  I suggest the following methodology for dynamic SQL statement development.

    1.  Start with the final parameterized SQL statement batch you want to execute:

    SET @dsTestString = 'Test String Works'; SELECT * FROM dbo.[HRTC] WHERE TrainCode = @dsTrainCode;

    2.  Enclose the statement batch in single quotes and double all single quotes embedded in the string:

    'SET @dsTestString = ''Test String Works''  SELECT * FROM dbo.[HRTC] WHERE TrainCode = @dsTrainCode;'

    3.   Remove embedded table/columns names and use concatenation to supply the run-time values (from trusted source):

    'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;'

    4.   Assign the final SQL expression to an nvarchar variable:

    SET @SQL = N'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;';

    5.   Assign the parameter declaration (containing each parameter embedded in the SQL string) to an nvarchar variable, with each parameter specifying the name, data type and direction (which defaults to INPUT):

    SET @params = N' @dsTrainCode varchar(10), @dsTestString varchar(20) OUTPUT';

    6.  Create the execute sp_executesql statement, supplying the SQL statement, declared parameters and mapping of declared parameters to local parameter/variables:

    EXECUTE sp_executesql
    	  @SQL
    	, @params
    	, @dsTrainCode = @TrainCode
    	, @dsTestString = @TestString OUTPUT;

    7.  Test the final SQL is:

    DECLARE @debug bit = 0,
      @TableName sysname = N'HRTC',
      @TrainCode nvarchar(10) = N'SF',
      @SQL nvarchar(1000),
      @params nvarchar(1000),
      @TestString nvarchar(50);
    
    SET @SQL = N'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;';
    
    SET @params = N' @dsTrainCode varchar(10), @dsTestString varchar(20) OUTPUT';
    
    EXECUTE sp_executesql
    	  @SQL
    	, @params
    	, @dsTrainCode = @TrainCode
    	, @dsTestString = @TestString OUTPUT;
    
    IF @debug = 1 PRINT @SQL;
    
    PRINT @TestString;
    GO

     

     

     

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, November 10, 2013 2:02 PM corrected step 2 script
    • Marked as answer by ConstPM Sunday, November 10, 2013 5:34 PM
    Sunday, November 10, 2013 2:00 PM
  • 3.   Remove embedded table/columns names and use concatenation to supply the run-time values (from trusted source):

    'SET @dsTestString = ''Test String Works''; SELECT * FROM dbo.' + @TableName + ' WHERE TrainCode = @dsTrainCode;'

    And wrap all table column names in quotename (as Bob actually had in his post):
    'SET @dsTestString = ''Test String Works'';
    SELECT * FROM dbo.' + quotename(@TableName) +
    ' WHERE TrainCode = @dsTrainCode;'

    (This gets difficult if you use multiple schemas. But we save that for another day.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 10, 2013 3:27 PM
  • Well as it turns out there were two fatal errors in my code.  First enclosing the @dsTrainCode variable in quotes, thereby converting it to a literal (as pointed out in the MSDN responses).  Second, misspelling the @dsTrainCode as @dsTainCode.  After that is ran great.

    A special thanks to Dan Guzman for his wonderful approach which was super helpful in pointing out building dynamic sql from existing code that works.  Also Erland Sommarskog's article, The Curse and Blessings of Dynamic SQL was very helpful .  These are must reading for anyone new to dynamic sql.  Both of you guys really helped me understand dynamic sql and it is much appreciated.

    ....bob sutor


    Bob Sutor


    • Edited by ConstPM Sunday, November 10, 2013 5:33 PM
    Sunday, November 10, 2013 5:32 PM