CODEWITHSUNDEEP
sqlsql-serversql-server-2008
athiq-ahmed
athiq-ahmed

Reputation: 43

How to pass the dynamic column names in a stored procedure

--Drop the table if exists
 drop proc if exists test_sp

 use testdb
 go

 create procedure test_sp
     (@metric varchar(50)  = NULL,
      @from_date date = NULL,
      @to_date date = NULL)
 as 
 begin
    set nocount on;

    --declaring the column name type
    declare @column_name decimal(25,2)

    --specifying the column name based on the metric provided
    if @metric = 'Sales'
    begin
        @column_name = 'sales_value'
    end
    else
    begin
        @column_name = 'revenue_value'
    end

    --sum the value based on the metric
    select sum(@column_name) 
    from <dataset>  
    where metric = @metric 
end

-- execute the procedure
exec test_sp @metric = 'sales'

Upvotes: 0

Views: 1013

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

As an alternative to dynamic sql you can use a case expression. This will make the entirety of your procedure this simple.

create procedure test_sp
(
    @metric varchar(50)  = NULL
    ,@from_date date =NULL
    ,@to_date date =Null
)
AS
BEGIN
    SET NOCOUNT ON;

    select sum(case when @metric = 'Sales' then sales_value else revenue_value end) from <dataset>  where metric = @metric 
END

Upvotes: 2

S3S
S3S

Reputation: 25112

You need dynamic SQL here. You can't use a variable for an object (table name, column name, etc) without it.

...

   declare @sql varchar(max)

   --sum the value based on the metric

   set @sql = 'select sum(' + @column_name + ') from <dataset>  where metric = ' + @metric 
   print(@sql) --this is what will be executed in when you uncomment the command below
   --exec (@sql)
end

--execute the procedure
 exec test_sp @metric ='sales'

But, you could eliminate it all together... and shorten your steps

 use testdb
 go
 create procedure test_sp
     (
     @metric varchar(50)  = NULL
     ,@from_date date =NULL
     ,@to_date date =Null
     )
 AS
 BEGIN
    SET NOCOUNT ON;

--specifying the column name based on the metric provided
 if @metric = 'Sales'
 begin
    select sum('sales_value') 
    from yourTable 
    where metric = @metric --is this really needed?
 end
else 
 begin
    select sum('revenue_value') 
    from yourTable 
    where metric = @metric  --is this really needed?
 end

Also, not sure what @from_date and @to_date are for since you don't use them in your procedure. I imagine they are going in the WHERE clause eventually.

Upvotes: 0

Related Questions