pass column name as parameter in sp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

pass column name as parameter in sp

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-15 : 05:22:02

I have three different procedures as below.


create procedure firstprocedurea
as
begin
select date, sum(case when secondcolumn = '45' and thirdcolumn = 'abc'
Then cast(price1 as int)) as price1 from table1
where between '20120801' and '20120831'
group by date
end


create procedure firstprocedure
select date, sum(case when secondcolumn = '45' and thirdcolumn = 'abc'
Then cast(price2 as int)) as price2 from table1
where between '20120801' and '20120831'

group by date


create procedure firstprocedure
as 
begin
select date, sum(case when secondcolumn = '45' and thirdcolumn = 'abc'
Then cast(price3 as int)) as price3 from table1
where date between 20120801' and '20120831'
group by date
end[/code]

I think I can combine this into one procedure if I'm able to pass the column 'price1' or 'price2' or 'price3' as a parameter. 

Is it possible in sql server?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-15 : 11:30:46

Maybe something like:


create procedure firstprocedure
@colname as varchar(40)
as
begin
   select date
         ,sum(cast(case when secondcolumn='45' and thirdcolumn='abc'
                        then case @colname
                                when 'price1' then price1
                                when 'price2' then price2
                                when 'price3' then price3
                                else 0
                             end
                        else 0
                   end as int)
             ) as price1
     from table1
    where date between '20120801' and '20120831'
    group by date
end

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-15 : 14:03:48

I tried your code but I get error incorrect syntax near @colname

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 16:05:45

I didn't see any syntax error in bitsmed's code; so I copied the code and installed it - still didn't get any error. Then created a table like this and ran the stored proc - still didn't get any error. So I am unable to figure out why you are getting an error.

Double click on the error and see where it takes you to. Is it on the parameter definition, or is it where @colname is used?

CREATE TABLE table1 (
	secondcolumn VARCHAR(32),
	thirdcolumn VARCHAR(32),
	price1 INT, price2 INT, price3 INT, [date]DATETIME);
INSERT INTO table1 VALUES (
	'45','abc',1,2,3,'20120801');

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-15 : 17:10:52

How are the fields price1, price2 and price3 defined?

Edit: Come to think of it, you might pass worng parameter type. Are you pasing 'price1' or price1?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-15 : 22:01:48

quote:
Originally posted by learning_grsql

I tried your code but I get error incorrect syntax near @colname

you were trying to create three procedures together?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-16 : 16:25:33

quote:
Originally posted by visakh16

quote:
Originally posted by learning_grsql

I tried your code but I get error incorrect syntax near @colname

you were trying to create three procedures together?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes.

@bitsmed and @sunita.
Thanks, you are correct. After I added apostrophe in price1, price2 and price3, it worked.
before i was passing price1 instead of 'price1'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-16 : 17:28:29

quote:
Originally posted by learning_grsql

quote:
Originally posted by visakh16

quote:
Originally posted by learning_grsql

I tried your code but I get error incorrect syntax near @colname

you were trying to create three procedures together?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes.

@bitsmed and @sunita.
Thanks, you are correct. After I added apostrophe in price1, price2 and price3, it worked.
before i was passing price1 instead of 'price1'

ok...glad that you got it sorted out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources