passing table name in dynamic sub SQL

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)

passing table name in dynamic sub SQL

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2009-10-16 : 07:51:26

Hi,

SELECT @MySQL1=(SELECT SUM(sum1) as var1 FROM ( SELECT Top (3) cast(F7 as int) as Sum1 FROM mytable where sconnect=@sConnect and Sdevice=@GetsDevice and sDeviceId=@GetsID and ddate=@@R1date and F7<>'' order by ntime desc) as Ps)

i want to pass mytable name i have declared

Declare @mytablevar VARCHAR(255)
set @mytablevar = 'mytable'

when i change my query to

SELECT @MySQL1=(SELECT SUM(sum1) as var1 FROM ( SELECT Top (3) cast(F7 as int) as Sum1 FROM ' + @mytablevar + ' where sconnect=@sConnect and Sdevice=@GetsDevice and sDeviceId=@GetsID and ddate=@@R1date and F7<>'' order by ntime desc) as Ps)

i m getting error it says incorrect syntax near at @mytablevar pls help me out

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-16 : 07:59:29

SELECT @MySQL1='(SELECT SUM(sum1) as var1 FROM ( SELECT Top (3) cast(F7 as int) as Sum1 FROM ' + @mytablevar + ' where
sconnect='''+@sConnect+''' and Sdevice='''+ @GetsDevice+ ''' and sDeviceId='''+@GetsID+''' and
ddate='''+@@R1date+''' and F7<>'''' order by ntime desc) as Ps)'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 08:09:16

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources