Join 2 strings to make a table i can use

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)

Join 2 strings to make a table i can use

Author

Topic

snipered
Starting Member

9 Posts

Posted - 2009-09-03 : 11:48:45

Hi, been struggling on this for a day now. Really would like some help.

Straight to the point.

How can i get this to work?

SELECT COUNT(DISTINCT(Field1)) FROM ('Table_' + @variablename)

As the table is randomly generated it does not work. I have tried CONCAT, CAST etc.

I just want to use the existing table rather than make a new one.

Does anybody have any idea?

Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-03 : 12:18:18

You have to use dynamic SQL for this:

EXEC('SELECT COUNT(DISTINCT Field1) FROM TABLE_' + @variablename)

snipered
Starting Member

9 Posts

Posted - 2009-09-03 : 12:55:48

Thanks. I will try that out tomorrow when i'm back at work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-04 : 02:07:24

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

snipered
Starting Member

9 Posts

Posted - 2009-09-04 : 04:42:15

Hi, it still doesn't seem to be working, i keep getting the error incorrect syntax near the keyword 'EXEC'

and ')'

Anyone know where i am going wrong please? If i remove the bolded section, everything works fine.

UPDATE table2
		SET table2.field2 = 
				(SELECT COUNT(*) FROM table3
					WHERE table3.field2 = @var2 ),
		table2.field3 = 
				(EXEC('SELECT COUNT(DISTINCT field2 ) FROM Table_' + @variablename ))
		WHERE table2.ID = @i

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-04 : 04:55:20

The whole statement has to be inside EXEC('...')

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-04 : 04:57:18

declare @val int
declare @t table(id int)

insert into @t
exec('select count(empid) from emptable')

select @val = id from @t

select @val

UPDATE table2
SET table2.field2 =
(SELECT COUNT(*) FROM table3
WHERE table3.field2 = @var2 ),
table2.field3 = @val
WHERE table2.ID = @i

u can't use the exec function there

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources