我只想将系统日期(GETDATE
(作为SQL存储过程中的一个参数传递。但是我在执行过程中遇到了一个错误。
SQL查询:
ALTER PROCEDURE ST_PRO_GETUSER
@D DATETIME = GETDATE --// passing GETDATE as a parameter.
AS BEGIN
select case when branch in ('A25','B10','C10')
then 'BR B1' Else 'BR B2'
end As [COLLECTION],FIXDATE
from MAIN_COUNTER where TDATE=@D --//Just want to pass date only
group by COLLECTION,FIXDATE
END
EXEC KK_SP_GETUSER_DIV
错误:
从字符串转换日期和/或时间时转换失败。
我该怎么办?
要作为参数传递,只需声明一个变量并将其传递:
DECLARE @DATE DATETIME = GETDATE();
EXEC ST_PRO_GETUSER @DATE;
如果您只想要日期,请将参数的数据类型更改为date
,然后执行:
DECLARE @DATE DATE = GETDATE();
EXEC ST_PRO_GETUSER @DATE;
但问题的一部分似乎实际上是问如何指定默认参数值。你不能使用函数作为默认值,所以可以使用:
CREATE PROCEDURE ST_PRO_GETUSER
(
@Date DATETIME = null
-- Change to DATE datatype if you don't want a time component.
-- @Date DATE = null
)
AS
BEGIN
SET NOCOUNT ON;
-- Default the @Date here is its null.
-- Note this doesn't handle the case when the caller wants to pass in null.
SET @Date = COALESCE(@Date,GETDATE());
-- SP Body
RETURN 0;
END
解决我的自我
ALTER PROCEDURE ST_PRO_GETUSER
@Date datetime = null
as
IF @Date is null
SET @Date = getdate() --// passing GETDATE as a parameter.
BEGIN
select case when branch in ('A25','B10','C10')
then 'BR B1' Else 'BR B2'
end As [COLLECTION],FIXDATE
from MAIN_COUNTER where TDATE=@D --//Just want to pass date only
group by COLLECTION,FIXDATE
END
EXEC ST_PRO_GETUSER
GETDATE()
是正确的语法,而不是GETDATE
。