Default parameter values in SQL Server 2008 stored procedure

Question

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

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.

here is how i call in 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

GO

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?

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

Any chance you can provide a sample of how the SP is called? — Justin
– Justin, Commented Aug 23, 2011 at 14:39
Your syntax looks good. Check the value you're passing to @cachStatus. (i.e., make sure you're not passing one). — Chains
– Chains, Commented Aug 23, 2011 at 14:40
Im calling it from entity frameworks (from a WCF service) and in management studio same behavior both places not defaulting the value — cobolstinks
– cobolstinks, Commented Aug 23, 2011 at 14:53
forget the IF, just use: SET @cacheStatus = ISNULL(@cacheStatus,'notReady'). you can use a SELECT @cacheStatus = ISNULL(@cacheStatus,'notReady'), @Xyz = ISNULL(@Xyz,'abc'), ... to do multiple — KM.
– KM., Commented Aug 23, 2011 at 15:14

John Mills · Accepted Answer · 2015-07-10 03:12:05Z

18

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.

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

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

edited Jul 10, 2015 at 3:12

John Mills

10.4k12 gold badges79 silver badges124 bronze badges

answered Aug 23, 2011 at 14:37

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JNK Over a year ago

+1 - workaround would be to add IF @Cachestatus IS NULL SET @cachestatus = 'NotReady'

HLGEM Over a year ago

@JNk add that as a separate answer as it really is probably what should be accepted if this is indeed his problem.

JNK Over a year ago

@HL - I wouldn't have gotten to it without you pointing out passing NULL so no biggie. Add a line to your answer for it.

John Mills · Accepted Answer · 2015-07-10 03:13:18Z

3

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.

edited Jul 10, 2015 at 3:13

John Mills

10.4k12 gold badges79 silver badges124 bronze badges

answered Aug 23, 2011 at 15:10

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Comments

David Coster · Accepted Answer · 2015-01-13 08:43:16Z

2

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

So when calling the SP the code would be:

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

answered Jan 13, 2015 at 8:43

David Coster

1,8252 gold badges15 silver badges16 bronze badges

Collectives™ on Stack Overflow

Default parameter values in SQL Server 2008 stored procedure

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related