存储过程参数默认值 [英] Stored procedure parameter default value
本文介绍了存储过程参数默认值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用默认参数创建一个存储过程,在我的查询中我会这样做:
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?
推荐答案
试试这个..
我只放了一个参数,因为其他参数都依赖它.
I put only one parameter because the others depend on it.
第一种方法
USE ..
GO
..
ALTER PROCEDURE yourSP
(@mydateParam DATETIME)
AS
BEGIN ...
SELECT @mydate = @mydateParam
SELECT @MT = DATEPART(MONTH,@mydate)
SELECT @MY = DATEPART(YEAR,@mydate)
---
SELECT * FROM ....
呼叫 SP:
DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam
第二种方法
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 ....
呼叫 SP:
DECLARE @mydateParam DATETIME
SELECT @mydateParam = GETDATE()
execute yourSP @mydateParam
或
execute yourSP
这篇关于存储过程参数默认值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文