CODEWITHSUNDEEP
sql-serverstored-procedures
amymon87
amymon87

Reputation: 41

CREATE PROCEDURE SQL Server

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?

Upvotes: 0

Views: 157

Answers (1)

lc.
lc.

Reputation: 116468

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

Upvotes: 1

Related Questions