CODEWITHSUNDEEP
sqlt-sqlsql-server-2005dynamicssms
Richard
Richard

Reputation: 117

Dynamic SQL - Concatenated FROM table

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

Upvotes: 1

Views: 143

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

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

Upvotes: 1

Richard
Richard

Reputation: 117

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.

Upvotes: 3

Related Questions