June 15, 2003 at 2:02 am
I'm trying to execute an insert statement from a stored procedure, that looks (simplified) like this:
declare @TempTableName varchar(150)
set @TempTableName = 'city'
declare @city_name varchar(150)
set @city_name = 'Denver'
declare @city_code char(3)
set @city_code = 'DEN'
declare @active int
set @active = 1
DECLARE @InsertCommand varchar(300)
SET @InsertCommand = 'INSERT INTO ' + @TempTableName + '(name, desc, active) VALUES(@city_name, @city_desc, @active)'
execute(@InsertCommand)
The transaction failes, the error is "Must declare variable '@city_name'". This variable is already declared, what's wrong with my syntax? Thanks in advance.
Edited by - levv on 06/15/2003 02:02:49 AM
Edited by - levv on 06/15/2003 02:03:22 AM
Edited by - levv on 06/15/2003 02:04:44 AM
Edited by - levv on 06/15/2003 02:05:23 AM
June 15, 2003 at 4:26 am
There's a couple of errors. It appears your temporary table CITY has a column called "desc". DESC is a reserved word, so best to use "description" or "[desc]".
Secondly, you're attempting to insert a variable called @city_desc which hasn't been declared. You actually declared @city_code. Was this a mix-up?
Lastly, and most importantly, when you use EXECUTE you're more or less firing off a brand new batch and any previously declared variables are out of scope and not known to that batch. You really need to supply constants to EXECUTE rather than variable names: Eg.
DECLARE @InsertCommand varchar(300)
SET @InsertCommand = 'INSERT INTO ' + @TempTableName + '(name, code, active) VALUES(''' + @city_name+ ''', ''' + @city_code + ''', ' + convert(varchar, @active)+ ')'
print @InsertCommand
execute(@InsertCommand)
This looks rather convoluted but it does work. There are alternatives to this syntax where you can supply variables names (eg. sp_executesql), but this is just one way of doing it.
Cheers,
- Mark
June 16, 2003 at 4:24 am
If you are building up a sql string to execute repeated use of 'print' is your best way of debugging (but print commands should be removed before using the proc properly).
To elaborate on what McCork is saying because you've put some variables in ' ' they are treated literally and can't refer to your earlier declarations. The variables have to come out of the quotes to be interpreted at runtime ie
set @city_name='Denver'
set @test-2=''' + @city_name+ ''', '
means @test-2 is equal to 'Denver',
set @city_name='Denver'
set @test-2='@city_name, '
means @test-2 is equal to @city_name,
but @city_name will not be further interpreted.
June 16, 2003 at 9:12 am
Guys, thanks very much. Got it all working just fine. Didn't realize that executing an "Execute" statement that uses variables inside of it will put all previuosly declared variables out of scope, that was the keyword. Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply