CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

how to include the parameter name as the name of a column in sql

I have a stored procedure that looks something like this : 

CREATE PROCEDURE [dbo].[spRS_Get] 
@Year Varchar(20),
@Month Varchar(20)


AS
BEGIN

SET NOCOUNT ON;

DECLARE @Paramdate datetime; 
DECLARE @ParamYear varchar(4);
DECLARE @ParamMonth varchar(2); 
DECLARE @Concatt varchar(10);
SET     @ParamYear = @Year;
SET     @ParamMonth = @Month;
SET     @Concatt = @ParamMonth + '-' + '01' + '-' + @ParamYear;
SET     @Paramdate = CONVERT(datetime, @Concatt);

SELECT Item
      ,SUM(CASE WHEN [MONTH]  = month(@ParamDate) THEN Sales END) AS month(@ParamDate)
      ,SUM(CASE WHEN  [MONTH] = month(@ParamDate) - 1 THEN Sales END) AS month(@ParamDate) - 1,
      ,SUM(CASE WHEN  [MONTH] = month(@ParamDate) THEN Sales END) - SUM(CASE WHEN [MONTH] = month(@ParamDate) - 1 THEN Sales END) AS  month(@ParamDate) - month(@ParamDate) - 1
FROM ABC
GROUP BY Item

In the above query , the part after the AS causes the error. I want to use the parameter name as the name of the column , but it gives me an error. Is there a way I can use the parameter name as the name of the month ?

Upvotes: 0

Views: 210

Answers (1)

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

You can only do that by building the select statement as a string and then executing it using the sp_executesql command.

So you'd get something like this:

declare @month0 varchar(2) = cast(month(@paramdate) as varchar(2));
declare @month1 varchar(2) = cast((month(@ParamDate) - 1) as varchar(2));
declare @month2 varchar(2) =
    cast((month(@ParamDate) - month(@ParamDate) - 1) as varchar(2));

declare s nvarchar(1024) =
  'SELECT Item
       , SUM(CASE WHEN [MONTH]  = month(@d) THEN Sales END)
         AS ''' + @month0 +
   ''' , SUM(CASE WHEN  [MONTH] = month(@d) - 1 THEN Sales END)
         AS ''' + @month1 +
   ''' , SUM(CASE WHEN  [MONTH] = month(@d) THEN Sales END) -
         SUM(CASE WHEN [MONTH] = month(@d) - 1 THEN Sales END)
         AS ''' + @month2 +
   ''' FROM ABC GROUP BY Item';

EXEC sp_executesql @s, N'@d DATETIME', @ParamDate;

Upvotes: 1

Related Questions