CODEWITHSUNDEEP
sqlsql-servert-sql
Kashif
Kashif

Reputation: 3327

stored procedure with custom parameters

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

Upvotes: 0

Views: 729

Answers (3)

alzaimar
alzaimar

Reputation: 4622

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

Upvotes: 0

KS Tech
KS Tech

Reputation: 194

You can call you so in following manner:

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

Upvotes: 0

www
www

Reputation: 4391

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'

Upvotes: 2

Related Questions