SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL issues with passing variables in stored procedure


Dynamic SQL issues with passing variables in stored procedure

Author
Message
henkvisser
henkvisser
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 38
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?
Ronald H
Ronald H
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 639
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 Hensbergen

Help 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
henkvisser
henkvisser
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 38
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!


Roshan Jospeh
Roshan Jospeh
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4965 Visits: 2104
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
henkvisser
henkvisser
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 38
Thanks but I just tried the same solution by Ronald but get the OPTIMIZER LOCK HINTS error.
Roshan Jospeh
Roshan Jospeh
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4965 Visits: 2104
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
Ronald H
Ronald H
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 639
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 Hensbergen

Help 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
Roshan Jospeh
Roshan Jospeh
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4965 Visits: 2104
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
Ronald H
Ronald H
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2154 Visits: 639
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 Hensbergen

Help 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
henkvisser
henkvisser
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 38
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search