data type error in dynamic query

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)

data type error in dynamic query

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-12 : 10:34:59

hi iam running a query which is dynamic and ia have one error about converting int to varchar at @y[last line of my query]...................for my variable

Declare @z varchar(500),
@y int,
@x varchar(40)

Set @y=123
Set @x='tab'+convert(varchar(8),@y)

SET @z=
'INSERT INTO ##temptable
SELECT field1,field2
FROM tab1 As A INNER JOIN ' +@x+' B ON A.ID = B.ID where A.Pid=' + @y

Exec (z)

Note: when I decalre @y varchar(4). its is not giving me any error but I have to use int.
Can any one help me, why i have error in the above query
Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-12 : 10:48:14

You declare @y an integer (numeric value) then assign it an integer 123 but then "add" it to a string variable. You can't do that. @y needs to be a varchar as well and given a string value like '123' then you can concatenate it to your sql string.

are you sure you have to use dynamic sql in this situation?

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-12 : 11:08:38

thanks
yes
@y is comming from my above query and which is integer, my table name is dynamic which is tablename+@y, thats why I used convert function.
so i need dynamic sql caz my table name is dynamic, which depends on@y

thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-12 : 11:29:20

This:
FROM tab1 As A INNER JOIN ' +@x+' B ON A.ID = B.ID where A.Pid=' + @y
Needs to be this:
FROM tab1 As A INNER JOIN ' +@x+' B ON A.ID = B.ID where A.Pid=' +convert(varchar(8),@y)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources