Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic SQL issues with passing variables in stored procedure Expand / Collapse
Author
Message
Posted Friday, January 16, 2009 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 10, 2009 6:15 AM
Points: 11, 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?
Post #637856
Posted Friday, January 16, 2009 4:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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
Post #637864
Posted Friday, January 16, 2009 4:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 10, 2009 6:15 AM
Points: 11, 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!

Post #637869
Posted Friday, January 16, 2009 4:44 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
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
Custom cleanup script for backups
Post #637871
Posted Friday, January 16, 2009 4:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 10, 2009 6:15 AM
Points: 11, Visits: 38
Thanks but I just tried the same solution by Ronald but get the OPTIMIZER LOCK HINTS error.
Post #637872
Posted Friday, January 16, 2009 4:58 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
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
Custom cleanup script for backups
Post #637881
Posted Friday, January 16, 2009 5:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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
Post #637888
Posted Friday, January 16, 2009 5:05 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:08 AM
Points: 617, Visits: 1,273
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
Custom cleanup script for backups
Post #637890
Posted Friday, January 16, 2009 5:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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
Post #637893
Posted Friday, January 16, 2009 7:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 10, 2009 6:15 AM
Points: 11, 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)
Post #638089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse