Plz correct this - 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 2005 Forums

Transact-SQL (2005)

Plz correct this

Author

Topic

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-05 : 16:41:07

declare @var varchar(50)
set @var= 'COLUMNNAME'
select ID, a.@var , b.@var
from rooper a
join jim_rooper b on b.id = a.id
join b_rooper bb on bb.id = a.id
where a.@var != b.@var

All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-05 : 16:55:21

The only way I can think of doing this is with Dynamic Sql.
But then, it will make your query cumbersome.


declare @var varchar(50),@sqlqry varchar(4000)
set @var= 'COLUMNNAME'
SET @sqlqry = 
'select ID, a.'+@var+' , b.'+@var
+'from rooper a
join jim_rooper b on b.id = a.id
join b_rooper bb on bb.id = a.id 
where a.'+@var+' != b.'+@var
EXEC (@sqlqry)

Thanks
Karunakaran

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-05 : 17:42:04

Karunakaran,

I get the message -

Server: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.

karuna
Aged Yak Warrior

582 Posts

Posted - 2008-05-05 : 17:51:22

Try this. If you still got error, uncomment the Print statement line and post the result.


declare @var varchar(50),@sqlqry varchar(4000)
set @var= 'COLUMNNAME'
SET @sqlqry = 
'select ID, a.'+@var+' , b.'+@var+' from rooper a join 
jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id 
where a.'+@var+' != b.'+@var 
--print @sqlqry
EXEC (@sqlqry)

Thanks
Karunakaran

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2008-05-05 : 18:13:56

Karunakaran,

I didnt explain myself well in here. I thought that you were asking me what was wrong with my query instead and incidentally, even the dynamic sql that you wrote in here was also throwing hte same error message.

However, Im thankful for resolving my sql issue as whole.

regards,

Anil Kumar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources