SQL Server 2008存储过程中的默认参数值

I feel like an idiot, but I can't make this SP default a value.... Here's how I'm declaring my parameters.

我觉得自己像个白痴,但我不能让这个SP默认值......这就是我如何宣布我的参数。

ALTER PROCEDURE [dbo].[PCS_DocumentCacheInsert]
(
    @sessionId varchar(200),
    @mrn varchar(50) ,
    @fromDate datetime,
    @toDate datetime,
    @aggregate varchar(50),
    @author varchar(50),
    @datePerformed dateTime,
    @docId varchar(15),
    @encounterId varchar(15),
    @facility varchar(5),
    @level char(1),
    @orderedByAuthor varchar(50),
    @resultAuthor varchar(50),
    @resultCode varchar(5),
    @resultId varchar(30),
    @resultName varchar(30),
    @status varchar(5),
    @subType varchar(10),
    @type varchar(10),
    @security varchar(3),
    @serviceGroup varchar(15),
    @witmurNum varchar(10),
    @deptId varchar(10),
    @deptText varchar(40),
    @cacheCreateTS dateTime ,
    @cacheStatus varchar(8) ='notReady',
    @cacheUpdateTS datetime
)

Everything works fine with this proc except I can't get notReady to default for @cacheStatus. Google says I'm using the correct syntax.

这个过程一切正常,除了我不能为@cacheStatus得到notReady默认值。谷歌说我正在使用正确的语法。

here is how i call in MS

这是我打电话给MS的方式

EXEC    @return_value = [dbo].[PCS_DocumentCacheInsert]
    @sessionId = N'asdfssa',
    @mrn = N'asdf',
    @fromDate = NULL,
    @toDate = NULL,
    @aggregate = NULL,
    @author = N'author',
    @datePerformed = NULL,
    @docId = N'id',
    @encounterId = NULL,
    @facility = NULL,
    @level = NULL,
    @orderedByAuthor = NULL,
    @resultAuthor = NULL,
    @resultCode = NULL,
    @resultId = NULL,
    @resultName = NULL,
    @status = NULL,
    @subType = NULL,
    @type = NULL,
    @security = NULL,
    @serviceGroup = NULL,
    @witmurNum = NULL,
    @deptId = NULL,
    @deptText = NULL,
    @cacheCreateTS = NULL,
    @cacheStatus = NULL,
    @cacheUpdateTS = NULL

SELECT 'Return Value' = @return_value

SELECT'返回值'= @return_value

so i added this and its working now, but I don't understand why when I right click and say execute stored procedure then select the null check boxes why it wouldn't default. I guess checking null sends 'NULL' to the proc and not DBNull?

所以我现在添加了这个和它的工作,但我不明白为什么当我右键单击并说执行存储过程然后选择空复选框为什么它不会默认。我想检查null会向proc发送'NULL'而不是DBNull吗?

if @cacheStatus is null
begin
    set @cacheStatus ='notReady'
end

3 个解决方案

#1

Are you sure that you aren't sending null in as the value for that parameter? The default is only used if you do not send that parameter in at all.

你确定你没有发送null作为该参数的值吗?仅当您根本不发送该参数时才使用默认值。

@JNK suggests a workaround like this if you have this issue:

如果您遇到此问题,@ JNK建议使用这样的解决方法:

IF @Cachestatus IS NULL SET @cachestatus = 'NotReady'

#2

Call it like this:

这样叫:

EXEC    @return_value = [dbo].[PCS_DocumentCacheInsert]
    @sessionId = N'asdfssa',
    @mrn = N'asdf',
    @fromDate = NULL,
    @toDate = NULL,
    @aggregate = NULL,
    @author = N'author',
    @datePerformed = NULL,
    @docId = N'id',
    @encounterId = NULL,
    @facility = NULL,
    @level = NULL,
    @orderedByAuthor = NULL,
    @resultAuthor = NULL,
    @resultCode = NULL,
    @resultId = NULL,
    @resultName = NULL,
    @status = NULL,
    @subType = NULL,
    @type = NULL,
    @security = NULL,
    @serviceGroup = NULL,
    @witmurNum = NULL,
    @deptId = NULL,
    @deptText = NULL,
    @cacheCreateTS = NULL,
    --@cacheStatus = NULL,
    @cacheUpdateTS = NULL

You can't pass @cacheStatus if you want the default to get used.

如果要使用默认值,则无法传递@cacheStatus。

#3

Another option that hasn't been mentioned yet is to use the keyword "DEFAULT" rather than passing a NULL value.

尚未提及的另一个选项是使用关键字“DEFAULT”而不是传递NULL值。

So when calling the SP the code would be:

因此,在调用SP时,代码将是:

EXEC    @return_value = [dbo].[PCS_DocumentCacheInsert]
    @sessionId = N'asdfssa',
    @mrn = N'asdf',
    @fromDate = NULL,
    ...  Just got rid of some lines to focus on the param in question - see DEFAULT below
    @cacheCreateTS = NULL,
    @cacheStatus = DEFAULT,
    @cacheUpdateTS = NULL

#1