CREATE PROCEDURE SQL Server

Question

I am trying to input different variables into one procedure by just specifying the input parameters - sorry if this is very easy - I have been using SAS for years and trying to figure out SQL!

@VAR is my input parameter. It can either be a number or a text in the data that I am looking at. I want to be able to have say 10 columns that I run this code for and output the results separately by just changing the inputs - is this possible or make sense?

declare @VAR as int???
set @VAR = 'exp_year', 'BusYrsExp'

CREATE PROCEDURE getExpSum @VAR int as
select @VAR, sum(NetCost) as clmCost, sum(claimCount) as clmnum, sum(earneddays) as exposure
into ow_exp_year
from CF_BI
group by @VAR
order by @VAR

OK - if I need to use dynamic sql - can this procedure be used for just one variable? How much more difficult would dynamic sql make this?

need dynamic sql for that...

Mitch Wheat
– Mitch Wheat

2014-01-10 01:27:14 +00:00
Commented Jan 10, 2014 at 1:27 — Mitch Wheat
– Mitch Wheat, Commented Jan 10, 2014 at 1:27

lc. · Accepted Answer · 2014-01-10 01:37:53Z

1

If you know the possible values for @VAR ahead of time you can write a CASE statement. For example if you have an int value representing some enumeration:

CREATE PROCEDURE getExpSum
    @groupColumn int --enumeration: 1=foo, 2=bar, 3=baz
AS
    SELECT grp, sum(NetCost) as clmCost, 
        sum(claimCount) as clmnum, sum(earneddays) as exposure
    INTO ow_exp_year
    FROM
    (
        SELECT CASE @groupColumn
            WHEN 1 THEN foo
            WHEN 2 THEN bar
            WHEN 3 THEN baz
            END AS grp,
            NetCost, claimCount, earneddays
        FROM CF_BI
    ) x
    GROUP BY grp
    ORDER BY grp

answered Jan 10, 2014 at 1:37

lc.

117k21 gold badges164 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CREATE PROCEDURE SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related