Dynamic SQL - Concatenated FROM table

Question

Long time searcher first time poster.

I am trying to create a dynamic SQL script which concatenates the dynamic part into a string to form the From clause so I can declare the table name only once (in future I will add to this so that I can loop through a whole bunch of tables). However when I run the script I get the error: Incorrect syntax near '+'

DECLARE @SCode varchar(4)
DECLARE @Subcode varchar(4)

SET @SCode = 'Client1'
SET @Subcode = 'A'          
SELECT MAX(dateofchange) AS Maxdate, @SCode + 'Type'.ID 
FROM @SCode + 'Type'

Any help would greatly be appreciated. I'm sure the answer will be simple however I am relatievely new to the SQL game.

thanks, SSMS - 2005

It is the + in the From as I stripped the code all the way back to just a simple select top 1 * from and it errors. But thanks for the suggestion. — Richard
– Richard, Commented Apr 11, 2013 at 7:45

Richard · Accepted Answer · 2013-04-11 17:49:07Z

3

Using the answer from Alexander Fedorenko in SQl Server 2005 it gives an error "Cannot assign a default value to a local variable." and then for every variable "Must declare the scalar variable "@dml""

The reason for this is that with 2005 you have to declare the variable and set in like

DECLARE @SCode AS VARCHAR(MAX)
SET @scode ='ID12'

I simplified the example a however the below code is a working example for SSMS 2005

DECLARE @dml  as NVARCHAR(MAX)
DECLARE @SCode AS VARCHAR(MAX)
SET @scode ='ID12'
Set @dml='SELECT ID 
FROM '+ QUOTENAME(+@SCode +'Type')
EXEC sp_executesql @dml

Side Note: Why you can't comment on an answer with a code block is beyond me.

answered Apr 11, 2013 at 17:49

Richard

1171 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oleksandr Fedorenko Over a year ago

Very valuable info.Thanks @Richard and +1 in your answer;)

Richard Over a year ago

Thanks and now I have enough rep to be able to plus 1 your answer too.

Oleksandr Fedorenko · Accepted Answer · 2013-04-12 11:01:16Z

1

Try this dynamic SQL script(option for a SQLServer2008+)

DECLARE @SCode varchar(10) = 'Client1',
        @Subcode varchar(10) = 'A',
        @dml nvarchar(400)
SELECT @dml = 'SELECT MAX(dateofchange) AS Maxdate, ID FROM ' + QUOTENAME( + @SCode + 'Type')
EXEC sp_executesql @dml

edited Apr 12, 2013 at 11:01

answered Apr 10, 2013 at 20:25

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

1 Comment

Richard Over a year ago

Please see my additional answer below for the working code and an explanation as to why these changes need to be made.

Collectives™ on Stack Overflow

Dynamic SQL - Concatenated FROM table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related