Dynamical SQL - select the table name from comma seperated variable in sql

Question

 DECLARE @tablename as varchar(MAX);
  SET  @tablename = '[dbo].[A_E_att_rate]
                    ,[Acute_total_cost]
                    ,[Average_los]
                    ,[D_days]
                    ,[days_1000]
                    ,[rate]';

    WHILE (SELECT * from [dbo].[fnSplitListToTable](@tablename)) > 0
    BEGIN
    EXEC(' SELECT t.[Commissionercode],
                t.[RiskBand Qadm],
                x.Col,
                x.Value
                FROM' + @tablename +' t
            CROSS APPLY 
            (
                VALUES
                    (''August'', t.[Sum of Aug12-Jul13]),
                    (''July'', t.[Sum of Jul12-Jun13]),
                    (''June'', t.[Sum of Jun12-May13]),
                    (''May'', t.[Sum of May12-Apr13]),
                    (''April'', t.[Sum of Apr12-Mar13])
            ) x (Col, Value)')

END

Here in the above query i am trying to fetch the table names from the variable @tablename. Which is a comma separated value. I am interested in using this each table name with in the loop. How do i do this ?

Your loop is wrong. I'd split the string variable into a table variable and loop over it like: http://stackoverflow.com/a/1578248/65223 — KM., Nov 07 '13 at 15:19
Thanks everybody. I got the solution from first comment by @KM . I used cursor for this — user2919277, Nov 07 '13 at 16:38

score 0 · Answer 1 · answered Nov 07 '13 at 20:47

Here's what I would do. Feedback is appreciated!

DECLARE @tablename as varchar(MAX), @d AS nvarchar(100), @SQL NVARCHAR(MAX);
  SET  @tablename = N'[dbo].[A_E_att_rate],[Acute_total_cost],[Average_los],[D_days],[days_1000],[rate]';
  SET @d = N',';
    SET @SQL = N'';
;WITH
t AS
(
    SELECT
       CAST(@tablename AS NVARCHAR(MAX)) AS tn,
       CAST(@tablename AS NVARCHAR(MAX)) AS r,
       CAST(0 AS INT) AS i
    UNION ALL
    SELECT
       CASE WHEN CHARINDEX(@d, r) <> 0 THEN
       CAST(
          STUFF(r, CHARINDEX(@d, r), LEN(r), N'')
          AS NVARCHAR(MAX))
          ELSE
          r
          END
          ,
       CASE WHEN CHARINDEX(@d, r) <> 0 THEN
       CAST(
          STUFF(r, 1, CHARINDEX(@d, r), N'')
          AS NVARCHAR(MAX))
          ELSE
          NULL
          END
          ,
       i + 1
    FROM
       t
    WHERE
       r IS NOT NULL
)
SELECT @SQL = N'SELECT t.[Commissionercode], t.[RiskBand Qadm], x.Col, x.Value FROM ' + tn + N' AS t CROSS APPLY 
            (
                VALUES
                    (''August'', t.[Sum of Aug12-Jul13]),
                    (''July'', t.[Sum of Jul12-Jun13]),
                    (''June'', t.[Sum of Jun12-May13]),
                    (''May'', t.[Sum of May12-Apr13]),
                    (''April'', t.[Sum of Apr12-Mar13])
            ) x (Col, Value)' FROM t WHERE i > 0

EXEC sp_executesql @SQL

Dynamical SQL - select the table name from comma seperated variable in sql

1 Answers1