Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » T-SQL » Dynamic SQL issues with passing variables in...
Add to briefcase

Dynamic SQL issues with passing variables in stored procedure Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
henkvisser
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
Ronald H
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: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602
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
henkvisser
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
joeroshan
Posted Friday, January 16, 2009 4:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 573, Visits: 1,169
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
henkvisser
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
joeroshan
Posted Friday, January 16, 2009 4:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 573, Visits: 1,169
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
Ronald H
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: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602
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
joeroshan
Posted Friday, January 16, 2009 5:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 573, Visits: 1,169
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
Ronald H
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: Friday, November 30, 2012 1:52 AM
Points: 1,116, Visits: 602
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
henkvisser
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.