stored procedure with custom parameters

Question

I'm trying to create a stored procedure to summarize counts in a view I created, but I need to use a custom parameter because I want to filter on start and end dates (two of my parameters) and those are supposed to rely on user input.

For example, I want to have something like this except I want the @Name to be a value that is set by another person, not set in the procedure itself. What would be the best way to go about this?

DECLARE @Name VARCHAR(20)
SET @Name='User1'
SELECT
    SUM(
        CASE
            WHEN Name=@Name THEN Total*-1
            ELSE Total
        END
    ) [Total Adj]
    ,Date
FROM Table
GROUP BY Date

Your question is not clear. Are you saying that the start and end dates are declared by one user and the name by another? — Dan Bracuk
– Dan Bracuk, Commented Mar 10, 2013 at 18:29
Oh disregard the start/end date part. I'm looking at solely names in this case. — Yannik
– Yannik, Commented Mar 10, 2013 at 18:36

www · Accepted Answer · 2013-03-10 18:36:35Z

2

Just create a SP:

CREATE PROC some_proc @Name VARCHAR(20)
SELECT
    SUM(
        CASE
            WHEN Name=@Name THEN Total*-1
            ELSE Total
        END
    ) [Total Adj]
    ,Date
FROM Table
GROUP BY Date

And use it:

some_proc 'User1'

answered Mar 10, 2013 at 18:36

www

4,4011 gold badge26 silver badges24 bronze badges

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

4 Comments

Yannik Over a year ago

What if I have multiple parameters (say I throw in a date parameter too)? Then would I call it as some_proc 'User1' 'Date1'?

www Over a year ago

Sure: CREATE PROC some_proc 'at'Name VARCHAR(20) , 'at'Date1 DATE. to call it just add date after a comma. GL!

Yannik Over a year ago

Where would you type some_proc 'User1' when you're implementing the procedure? I didn't know you could just type it into the query window in MS SQL Server for example and it'd run. Cool!

Yannik Over a year ago

Alright--but where would you type some_proc 'User1' when you're implementing the procedure?

KS Tech · Accepted Answer · 2013-03-10 18:45:13Z

0

You can call you so in following manner:

Exec spname 'name','param1','param2'

answered Mar 10, 2013 at 18:45

KS Tech

1944 bronze badges

Comments

alzaimar · Accepted Answer · 2013-03-10 19:35:08Z

0

I prefer a table function. Basically the same as a procedure, but can be used similar to a view. Also, as a function is returning something, I prefer to use a function to return data. A procedure does something. But, this is only my personal opinion.

create function dbo.some_func(@Name Varchar(30))
returns table
as
begin
  return(
    SELECT
      SUM(CASE WHEN Name=@Name THEN Total*-1 ELSE Total END) [Total Adj]
         ,Date
    FROM Table
    GROUP BY Date
)
end

answered Mar 10, 2013 at 19:35

alzaimar

4,6321 gold badge22 silver badges31 bronze badges

2 Comments

Dan Bracuk Over a year ago

Using a function instead of a stored proc introduces limitations. For example, you can call one stored procedure from another, but you can't call one from a function.

alzaimar Over a year ago

@DanBracuk: Thank you very much for this comment. I never ran into this problem yet but it is interesting to know. However, in this case (inline table functions) these limitations do not apply. It is a nice 'parametrized view'.

Collectives™ on Stack Overflow

stored procedure with custom parameters

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related