CODEWITHSUNDEEP
sql-serverstored-procedures
Ionut P.
Ionut P.

Reputation: 63

Create stored procedure with dynamic column name

I tried to create a procedure in sql server that accepts a parameter as column name. (@luna) This is the code:

CREATE PROCEDURE clientBrandM (@luna NVARCHAR(10), @zona NVARCHAR(50)) AS

SELECT client, brand, SUM(anterior) anterior, SUM(curent) curent
FROM (
    SELECT CASE WHEN t1.zona IS NULL THEN t2.zona ELSE t1.zona END AS zona,
    CASE WHEN t1.client IS NULL THEN t2.client ELSE t1.client END AS client,
    CASE WHEN t1.brand IS NULL THEN t2.brand ELSE t1.brand END AS brand,
    CASE WHEN t1.@luna IS NULL THEN 0 ELSE t1.@luna END AS anterior,
    CASE WHEN t2.@luna IS NULL THEN 0 ELSE t2.@luna END AS curent

    FROM cbm2016 t1

    FULL OUTER JOIN cbm2017 t2 ON t1.zona = t2.zona
    AND t1.client = t2.client
    AND t1.brand = t2.brand ) t3

WHERE zona = @zona
GROUP BY client, brand



EXEC clientBrandM @luna = 'luna5', @zona = 'Ardeal'

After the create process, i get this error:

Msg 102, Level 15, State 1, Procedure clientBrandM, Line 8 Incorrect syntax near '@luna'.

How can i make that @luna (states as month) to change dinamicaly ?

@Marian Nasry, here is the code:

EXECUTE sp_executesql   
          N'SELECT client, brand, SUM(anterior) anterior, SUM(curent) curent
FROM (
    SELECT CASE WHEN t1.zona IS NULL THEN t2.zona ELSE t1.zona END AS zona,
    CASE WHEN t1.client IS NULL THEN t2.client ELSE t1.client END AS client,
    CASE WHEN t1.brand IS NULL THEN t2.brand ELSE t1.brand END AS brand,
    CASE WHEN t1.@luna IS NULL THEN 0 ELSE t1.@luna END AS anterior,
    CASE WHEN t2.@luna IS NULL THEN 0 ELSE t2.@luna END AS curent

    FROM cbm2016 t1

    FULL OUTER JOIN cbm2017 t2 ON t1.zona = t2.zona
    AND t1.client = t2.client
    AND t1.brand = t2.brand ) t3

WHERE zona = @zona
GROUP BY client, brand',  
          N'@luna nvarchar(10)', 
          N'@zona nvarchar(50)',
          @luna = 'luna5',  
          @zona = 'Ardeal';

Upvotes: 0

Views: 109

Answers (3)

Marian Nasry
Marian Nasry

Reputation: 819

use sp-executesql , this is sp-executesql documentation

EXECUTE sp_executesql   
          N'SELECT client, brand, SUM(anterior) anterior, SUM(curent) curent
FROM (
    SELECT CASE WHEN t1.zona IS NULL THEN t2.zona ELSE t1.zona END AS zona,
    CASE WHEN t1.client IS NULL THEN t2.client ELSE t1.client END AS client,
    CASE WHEN t1.brand IS NULL THEN t2.brand ELSE t1.brand END AS brand,
    CASE WHEN t1.'+@luna+' IS NULL THEN 0 ELSE t1.'+@luna+' END AS anterior,
    CASE WHEN t2.'+@luna+' IS NULL THEN 0 ELSE t2.'+@luna+' END AS curent

    FROM cbm2016 t1

    FULL OUTER JOIN cbm2017 t2 ON t1.zona = t2.zona
    AND t1.client = t2.client
    AND t1.brand = t2.brand ) t3

WHERE zona = '+@zona+'
GROUP BY client, brand'

Upvotes: 1

Dheerendra
Dheerendra

Reputation: 306

You can use the below code

CREATE PROCEDURE clientBrandM (@luna NVARCHAR(10), @zona NVARCHAR(50)) 
AS
BEGIN
DECLARE @Sql NVARCHAR(MAX);

SET @Sql = 'SELECT client, brand, SUM(anterior) anterior, SUM(curent) curent
FROM (
    SELECT CASE WHEN t1.zona IS NULL THEN t2.zona ELSE t1.zona END AS zona,
    CASE WHEN t1.client IS NULL THEN t2.client ELSE t1.client END AS client,
    CASE WHEN t1.brand IS NULL THEN t2.brand ELSE t1.brand END AS brand,
    CASE WHEN t1.'+@luna+' IS NULL THEN 0 ELSE t1.'+@luna+' END AS anterior,
    CASE WHEN t2.'+@luna+' IS NULL THEN 0 ELSE t2.'+@luna+' END AS curent
    FROM cbm2016 t1
    FULL OUTER JOIN cbm2017 t2 ON t1.zona = t2.zona
    AND t1.client = t2.client
    AND t1.brand = t2.brand ) t3
WHERE zona = '+''''+@zona+''''+'
GROUP BY client, brand'

PRINT @Sql
EXEC SP_EXECUTESQL @Sql

END

Upvotes: 1

Satinder singh
Satinder singh

Reputation: 10198

For dynamic query in stored procedure use sp-executesql

SQL Query:

ALTER PROCEDURE clientBrandM (@luna NVARCHAR(10), @zona NVARCHAR(50))
 AS BEGIN

    Declare @SQLQuery AS NVarchar(4000)

    SET @SQLQuery =N'SELECT client, brand, SUM(anterior) anterior, SUM(curent) curent
        FROM (
            SELECT CASE WHEN t1.zona IS NULL THEN t2.zona ELSE t1.zona END AS zona,
            CASE WHEN t1.client IS NULL THEN t2.client ELSE t1.client END AS client,
            CASE WHEN t1.brand IS NULL THEN t2.brand ELSE t1.brand END AS brand,
            CASE WHEN t1.'+@luna+' IS NULL THEN 0 ELSE t1.'+@luna+' END AS anterior,
            CASE WHEN t2.'+@luna+' IS NULL THEN 0 ELSE t2.'+@luna+' END AS curent
            FROM cbm2016 t1
            FULL OUTER JOIN cbm2017 t2 ON t1.zona = t2.zona
            AND t1.client = t2.client
            AND t1.brand = t2.brand ) t3
        WHERE zona = '+@zona+'
        GROUP BY client, brand'

      EXECUTE sp_executesql @SQLQuery

    END

Upvotes: 1

Related Questions