none
SP_executesql Syntax error RRS feed

  • Question

  • I am trying to execute below Sp_executesql with multiple parameters,but keep getting a syntax error

    Must declare the table variable "@DBname".

    SELECT   @FileDateList = filedate  files from TableA

    SELECt   @DataTimeField   = DataTimeField from TableB

    DECLARE @DBname  Nvarchar(100)

    SET @DBname = @DataBaseName + '.' + @TableName

    SET  @FileDateList =   '''' + @FileDateList + ''''

    SET @sql = N'SELECT  DISTINCT filedate  FROM  @DBname  

           

    WHERE   @DataSetTimeField  IN (@FileDateList)'

       SET @ParameterDefinition = N'

                                    @DBname  nvarchar(100),

                                    @DataSetTimeField nvarchar(50),

                                   @FileDateList as Nvarchar(1000) ';

             EXECUTE sp_executesql @sql, @ParameterDefinition,

                                                      @DBname=  @DBname  , @DataSetTimeField=@DataSetTimeField, @FileDateList=@FileDateList

    I am not sure what i am doing wrong and how can i fix this error?

    • Edited by SSAS_5000 Monday, April 7, 2014 10:04 PM
    Monday, April 7, 2014 10:03 PM

Answers

  • SET @sql = N'SELECT  DISTINCT filedate  FROM  @DBname  

    This fails, because @DBName must be a table variable, but you have

     SET @ParameterDefinition = N'@DBname  nvarchar(100),

    That is @DBName is a string. And, no, just because you have put a table name there does not make it a table variable.

    As for the actual solution depends on what you want to achieve, which is somewhat difficult to understand from your sketched-out code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 8, 2014 7:17 AM
  • I am trying to execute below Sp_executesql with multiple parameters,but keep getting a syntax error

    Must declare the table variable "@DBname".

    SELECT   @FileDateList = filedate  files from TableA

    SELECt   @DataTimeField   = DataTimeField from TableB

    DECLARE @DBname  Nvarchar(100)

    SET @DBname = @DataBaseName + '.' + @TableName

    SET  @FileDateList =   '''' + @FileDateList + ''''

    SET @sql = N'SELECT  DISTINCT filedate  FROM  @DBname  

           

    WHERE   @DataSetTimeField  IN (@FileDateList)'

       SET @ParameterDefinition = N'

                                    @DBname  nvarchar(100),

                                    @DataSetTimeField nvarchar(50),

                                   @FileDateList as Nvarchar(1000) ';

             EXECUTE sp_executesql @sql, @ParameterDefinition,

                                                      @DBname=  @DBname  , @DataSetTimeField=@DataSetTimeField, @FileDateList=@FileDateList

    I am not sure what i am doing wrong and how can i fix this error?

    For dynamic SQL, look at the parameters you're using to check whether the syntax is correct within that parameter string:

    Declare @sql nvarchar(100)
    
    SELECT @sql = 'Bla bla bla ' + @whatever + ' bla bla'
    
    
    --return the parameter you built into a dataset and check syntax
    
    SELECT @sql

    Secondly, why are you using sp_executesql for two parameters? I thought sp_executesql only runs 1 parameter and uses the string within the parameter and runs it as a essentially a new query window. For example if you were to return data which is determined by an inputted parameter (say if you're using a vb.net program for this stored proc) and you have a string field you want to add to your query which will need single quotes for the string, use the ASCII code for the single quote in order to avoid a syntax error=

    Alter Procedure [dbo].[whatever_procedure]
    
    @ID NvarChar(20)
    @comment NvarChar(300)
    
    AS
    BEGIN
    
    Declare @SQL nvarchar(200) =
    'INSERT INTO [TABLE1] SELECT '+ CHAR(39) + @ID + CHAR(39) + ' as [ID],' + CHAR(39) + @comment + CHAR(39) + ' as [comment]'
    
    
    --execute the query:
    
    EXEC sp_executesql @SQL

    You will have to use CHAR(39) as the single quote within dynamic SQL (to my knowledge) because the dynamic SQL itself is within single quotes. If you don't use them your query will insert a string without quotes for example you wouldn't use this code:

    Declare @string nvarchar(20) = this is my string

    INSTEAD you will use single quotes:

    Declare @string nvarchar(20) = 'this is my string'

    You would notice this if you looked within your @sql parameter after it's filled.


    • Edited by SnorreDenStora Tuesday, April 8, 2014 1:22 PM saw a random close bracket where it shouldn't of been!
    • Marked as answer by Elvis LongModerator Wednesday, April 16, 2014 1:42 AM
    Tuesday, April 8, 2014 1:20 PM

All replies

  • What you have written makes no sense. Can you explain in English, please, what are you trying to do and why do you need dynamic sql at all?

    I suggest you to start from the regular SQL statement, make sure it works. Dynamic SQL is a little bit more trickier and you need to master regular SQL statements first before attempting dynamic.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, April 7, 2014 10:06 PM
    Moderator
  • Use the PRINT statement to debug the dynamic SQL string.

    Dynamic SQL examples:

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    http://www.sqlusa.com/bestpractices/dynamicsql/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Monday, April 7, 2014 10:28 PM
    Moderator
  • Hi,

    The error message explains it: it's expecting @DBname to be a table variable.

    Executesql doesn't work the same way as by replacing string in the SQL statement passes in.

    Anyway, as a work around, may I suggest this:

    SET @sql = N'SELECT  DISTINCT filedate  FROM ' + @DBname + ' WHERE @DataSetTimeField  IN (@FileDateList)'
    

    Hope this helps.
    ~J.
    • Proposed as answer by DeviantLogic Tuesday, April 8, 2014 3:25 AM
    Monday, April 7, 2014 10:58 PM
  • SET @sql = N'SELECT  DISTINCT filedate  FROM  @DBname  

    This fails, because @DBName must be a table variable, but you have

     SET @ParameterDefinition = N'@DBname  nvarchar(100),

    That is @DBName is a string. And, no, just because you have put a table name there does not make it a table variable.

    As for the actual solution depends on what you want to achieve, which is somewhat difficult to understand from your sketched-out code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 8, 2014 7:17 AM
  • I am trying to execute below Sp_executesql with multiple parameters,but keep getting a syntax error

    Must declare the table variable "@DBname".

    SELECT   @FileDateList = filedate  files from TableA

    SELECt   @DataTimeField   = DataTimeField from TableB

    DECLARE @DBname  Nvarchar(100)

    SET @DBname = @DataBaseName + '.' + @TableName

    SET  @FileDateList =   '''' + @FileDateList + ''''

    SET @sql = N'SELECT  DISTINCT filedate  FROM  @DBname  

           

    WHERE   @DataSetTimeField  IN (@FileDateList)'

       SET @ParameterDefinition = N'

                                    @DBname  nvarchar(100),

                                    @DataSetTimeField nvarchar(50),

                                   @FileDateList as Nvarchar(1000) ';

             EXECUTE sp_executesql @sql, @ParameterDefinition,

                                                      @DBname=  @DBname  , @DataSetTimeField=@DataSetTimeField, @FileDateList=@FileDateList

    I am not sure what i am doing wrong and how can i fix this error?

    For dynamic SQL, look at the parameters you're using to check whether the syntax is correct within that parameter string:

    Declare @sql nvarchar(100)
    
    SELECT @sql = 'Bla bla bla ' + @whatever + ' bla bla'
    
    
    --return the parameter you built into a dataset and check syntax
    
    SELECT @sql

    Secondly, why are you using sp_executesql for two parameters? I thought sp_executesql only runs 1 parameter and uses the string within the parameter and runs it as a essentially a new query window. For example if you were to return data which is determined by an inputted parameter (say if you're using a vb.net program for this stored proc) and you have a string field you want to add to your query which will need single quotes for the string, use the ASCII code for the single quote in order to avoid a syntax error=

    Alter Procedure [dbo].[whatever_procedure]
    
    @ID NvarChar(20)
    @comment NvarChar(300)
    
    AS
    BEGIN
    
    Declare @SQL nvarchar(200) =
    'INSERT INTO [TABLE1] SELECT '+ CHAR(39) + @ID + CHAR(39) + ' as [ID],' + CHAR(39) + @comment + CHAR(39) + ' as [comment]'
    
    
    --execute the query:
    
    EXEC sp_executesql @SQL

    You will have to use CHAR(39) as the single quote within dynamic SQL (to my knowledge) because the dynamic SQL itself is within single quotes. If you don't use them your query will insert a string without quotes for example you wouldn't use this code:

    Declare @string nvarchar(20) = this is my string

    INSTEAD you will use single quotes:

    Declare @string nvarchar(20) = 'this is my string'

    You would notice this if you looked within your @sql parameter after it's filled.


    • Edited by SnorreDenStora Tuesday, April 8, 2014 1:22 PM saw a random close bracket where it shouldn't of been!
    • Marked as answer by Elvis LongModerator Wednesday, April 16, 2014 1:42 AM
    Tuesday, April 8, 2014 1:20 PM