Dynamic SQL issues with passing variables in stored procedure

  • I'm trying to use the stored procedure below in SQL Server 2000, but I get the following error;

    Server: Msg 170, Level 15, State 1, Line 4

    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'much'.

    Failed to get the call stack!

    I know there difficulties passing tablenames but this is clearly not a tablename.

    Var values used:

    @BatchID = 461

    @ErrorReason = 'DataElementID too much characters (>3)'

    @WhereStatement = 'LEN(DataElementID) > 3'

    CREATE PROCEDURE dbo.spWriteErrorRows

    @BatchID INT,

    @ErrorReason VARCHAR(200),

    @WhereStatement VARCHAR(200)

    AS

    EXEC('

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ' + @ErrorReason + '

    FROM dbo.ImportAccess

    WHERE' + @WhereStatement)

    GO

    Obviously it can't handle the @ErrorReason variable. Is there any way to fix this?

  • henkvisser (1/16/2009)


    I'm trying to use the stored procedure below in SQL Server 2000, but I get the following error;

    Server: Msg 170, Level 15, State 1, Line 4

    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'much'.

    Failed to get the call stack!

    I know there difficulties passing tablenames but this is clearly not a tablename.

    Var values used:

    @BatchID = 461

    @ErrorReason = 'DataElementID too much characters (>3)'

    @WhereStatement = 'LEN(DataElementID) > 3'

    CREATE PROCEDURE dbo.spWriteErrorRows

    @BatchID INT,

    @ErrorReason VARCHAR(200),

    @WhereStatement VARCHAR(200)

    AS

    EXEC('

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ' + @ErrorReason + '

    FROM dbo.ImportAccess

    WHERE' + @WhereStatement)

    GO

    Obviously it can't handle the @ErrorReason variable. Is there any way to fix this?

    This way the code expects @ErrorReason to be a column name, to prevent this, replace ' + @ErrorReason + ' with ''' + @ErrorReason + '''

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • r.hensbergen (1/16/2009)

    This way the code expects @ErrorReason to be a column name, to prevent this, replace ' + @ErrorReason + ' with ''' + @ErrorReason + '''

    Thanks Ronald, I replaced the variable the way you mentioned by adding two single quotes on both sides like this;

    CREATE PROCEDURE dbo.spWriteErrorRows

    @BatchID INT,

    @ErrorReason VARCHAR(200),

    @WhereStatement VARCHAR(200)

    AS

    EXEC('

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ' + @BatchID + ', ''' + @ErrorReason + '''

    FROM dbo.ImportAccess

    WHERE' + @WhereStatement)

    GO

    But now I get the OPTIMIZER LOCK HINTS error:

    Server: Msg 155, Level 15, State 1, Line 4

    [Microsoft][ODBC SQL Server Driver][SQL Server]'DataElementID' is not a recognized OPTIMIZER LOCK HINTS option.

    Failed to get the call stack!

  • Try additional quotes around the variables - two single quote for escape

    EXEC('

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''

    FROM dbo.ImportAccess

    WHERE' + @WhereStatement)

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks but I just tried the same solution by Ronald but get the OPTIMIZER LOCK HINTS error.

  • put the whole query to a variable and print it. I think you need to give a space after the where clause

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I agree that putting the SQL statement in a variable could have solved the problem, but I think I got it anyway. If you add an extra ') to the code, I think it should run then.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • set @sql='INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''

    FROM dbo.ImportAccess

    WHERE ' + @WhereStatement

    print @sql

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • So I think the @WhereStatement wasn't actually part of the INSERT. If you get used to put the SQL in a variable, understanding of using the quotes is much easier. Below my solution based on the above post. set @sql='INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT

    DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), ''' + @BatchID + ''', ''' + @ErrorReason + '''

    FROM dbo.ImportAccess

    WHERE ''' + @WhereStatement + ''')

    print @sql

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • Guys, I tried both;

    First results of print @SQL;

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), '463', 'DataElementID too much characters (>3)'

    FROM dbo.ImportAccess

    WHERE LEN(DataElementID) > 3

    Looks promising, but the procedure generates this error;

    Server: Msg 170, Level 15, State 1, Line 3

    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'LEN(DataElementID) > 3'.

    Failed to get the call stack!

    @RETURN_VALUE = N/A

    The second code by Ronald gives the following for @SQL;

    INSERT INTO dbo.ImportError

    (DataElementID, CenterCode, ValueType, ValueDate, [Value], ImportDate, BatchID, ErrorReason)

    SELECT DataElementID, CenterCode, ValueType, ValueDate, [Value], GetDate(), '463', 'DataElementID too much characters (>3)'

    FROM dbo.ImportAccess

    WHERE 'LEN(DataElementID) > 3'

    Last line won't work, but after removing 4 quotes from the @WhereStatement variable it produces the same error as the first piece of codes does. (incorrect syntax)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply