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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-19 : 10:59:59
|
| writes "Hello,I have a problem with some dynamic SQL in a SP. I can seem to find the problem.The statement is this:CREATE PROCEDURE dbo.sp_get_address_nl ( @pv_nZipNumeric int, @pv_cZipCharacter varchar(2), @pv_nHouseNumber int, @pv_nEven int, @pv_cDatabaseName varchar(100) = 'address', @pv_cTableName varchar(255) = 'tblZipNL' ) ASBEGIN DECLARE @pv_cSQLStatement varchar(2000) SET @pv_cSQLStatement = 'SELECT * ' + 'FROM ' + @pv_cDatabaseName + '.dbo.' + @pv_cTableName SET @pv_cSQLStatement = @pv_cSQLStatement + ' WHERE fnZipNumeric = ' + convert(varchar, @pv_nZipNumeric) SET @pv_cSQLStatement = @pv_cSQLStatement + ' AND fcZipCharacter = ' + @pv_cZipCharacter SET @pv_cSQLStatement = @pv_cSQLStatement + ' AND fnEven = ' + convert(varchar, @pv_nEven) SET @pv_cSQLStatement = @pv_cSQLStatement + ' AND fnStartNumber <= ' + convert(varchar, @pv_nHouseNumber) SET @pv_cSQLStatement = @pv_cSQLStatement + ' AND fnEndNumber >= ' + convert(varchar, @pv_nHouseNumber) EXEC (@pv_cSQLStatement)ENDGOThe call is:exec sp_get_address_nl 4817, 'WC', 18, 1The error message is:Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'WC'.Did i forget something?" |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-04-19 : 11:11:37
|
| Yes you forgot that when building dynamic SQL varchar variables have to be enclosed in quotes when you use them.What you have generated has something like.... AND fcZipCharacter = WC ANd fnEven ...which should of course be.... AND fcZipCharacter = 'WC' ANd fnEven ...To rectify this, put quotes around your string something like this SET @pv_cSQLStatement = @pv_cSQLStatement + ' AND fcZipCharacter = ''' + @pv_cZipCharacter + ''''Raymond |
 |
|
|
|
|
|
|
|
Dynamic SQL