Stored procedure parameter default value

ssmsstored-procedures

I'm trying to create a stored procedure with default parameters, in my query I would do it like this:

DECLARE @mydate DATETIME
DECLARE @MT DATETIME
DECLARE @MY DATETIME
SELECT @mydate = GETDATE()
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)

SELECT * FROM ....

In my stored proc I've tried it as follows but when I execute the stored proc it prompts me for parameter values:

USE ..
GO
..
ALTER PROCEDURE ...
(@mydate DATETIME
,@MT DATETIME
,@MY DATETIME)

AS
BEGIN ...
SELECT @mydate = GETDATE()
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)
---
SELECT * FROM ....

How do I set the parameter values in the stored proc like I would in the query?

Best Answer

Try this..

I put only one parameter because the others depend on it.

First approach

USE ..
GO
..
ALTER PROCEDURE yourSP
(@mydateParam DATETIME)

AS
BEGIN ...
SELECT @mydate = @mydateParam
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)
  ---
SELECT * FROM ....

To call the SP:

DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam

Second approach

ALTER Procedure yourSP
@mydateParam datetime = null
AS
BEGIN ...
IF @mydateParam is null
SET @mydateParam = getdate()
SELECT @mydate = @mydateParam
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY =  DATEPART(YEAR,@mydate)

   ---
SELECT * FROM ....

To call the SP:

DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam

execute yourSP

Related Solutions

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Sql-server – SQL Server: Query fast, but slow from procedure

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

Best Answer

Related Solutions

Sql-server – Select columns from result set of stored procedure

Sql-server – SQL Server: Query fast, but slow from procedure

Related Topic