August 30, 2006 at 12:23 pm
Hello
I'm trying to do a select and I'm having a problem with it (code below)
declare @teste_varchar2 as varchar(20)
declare @teste_varchar as varchar(500)
set @teste_varchar2 = "valor_fact"
exec ('select ' +@teste_varchar2+ ' from ##CONTENC where contracto = ' + @cont_descCursor)
the error message that I have is
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'e'.
What is odd with the above code is that if I use a similar code but not dynamic sql it works.
select valor_fact from ##CONTENC where contracto = @cont_descCursor
August 30, 2006 at 3:28 pm
I can see the only problem is
set @teste_varchar2 = "valor_fact" use single quotes instead of double quotes
August 30, 2006 at 5:02 pm
you can set a variable using either the ' or "
August 30, 2006 at 6:50 pm
Please look into this
create procedure proc_test as
begin
declare @name varchar(100)
set @name="Gopi"
select @name
end
If you execute this SQL Server 2000 through an error like this
Server: Msg 207, Level 16, State 3, Procedure proc_test, Line 4
Invalid column name 'Gopi'.
Correct me , if I am wrong
August 31, 2006 at 2:07 am
In response to the original problem reported....
From my experience, you can't use variable or temporary tables in a dynamic sql. That could probably be the reason you get the errror that you have mentioned.
Try it and see the results.
August 31, 2006 at 3:36 am
actually I used both and had no problem with that, I solved it with double quotes
exec ('select ' +@teste_varchar2+ ' from ##CONTENC
where contracto = ''' + @cont_descCursor + '''')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply