Dynamic Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Stored Proc

Author

Topic

azjeep
Starting Member

2 Posts

Posted - 2007-03-08 : 12:45:01

Hi:

I'm getting the following error; can anyone shed some light on it for me?

Error:
Server: Msg 245, Level 16, State 1, Procedure sp_UniqueInsertValidation2FieldsII, Line 13
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'SELECT * FROM tblDepartmentstoStores WHERE ((storeID = '' to a column of data type int.

Test Data:

tablename: tblDepartmentstoStores
field1Name: storeID
field1Value: 8
field2Name: deptID
field2Value: 3

The stored proc:

CREATE PROCEDURE sp_UniqueInsertValidation2FieldsII

(@tablename varchar(255),
@field1Name varchar(255),
@field1Value int,
@field2Name varchar(255),
@field2Value int)

AS

DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = "SELECT * FROM " + @tablename + " WHERE ((" + @field1Name + " = '" + @field1Value + "') AND (" + @field2Name + " = '" + @field2Value + "'))"

EXEC(@SQLStatement)
GO

Thanks for your time and help,

Matt

azjeep
Starting Member

2 Posts

Posted - 2007-03-08 : 12:56:45

Answered my own question:

http://www.sql-server-helper.com/error-messages/msg-245.aspx

Here's the corrected code:

CREATE PROCEDURE sp_UniqueInsertValidation2FieldsII

(@tablename varchar(255),
@field1Name varchar(255),
@field1Value int,
@field2Name varchar(255),
@field2Value int)

AS

DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = "SELECT * FROM " + @tablename + " WHERE ((" + @field1Name + " = '" + CAST(@field1Value AS VARCHAR(10)) + "') AND (" + @field2Name + " = '" + CAST(@field2Value AS VARCHAR(10)) + "'))"

EXEC(@SQLStatement)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-08 : 12:56:48

You need to add the CONVERT function to your int columns so that they can be concatenated to your string.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources