Default parameter values in SQL Server 2008 stored procedure

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.

  • How can I remove accents on a string?
  • Link all IDs from associative entity that have common ID
  • What's the most efficient way to match values between 2 tables based on most recent prior date?
  • Conditional Conditons in SQL Server
  • how to encode int with base32 in sql server 2008
  • SQL replace a null xml value
  • 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
    

  • EF Code First Mysql to SQL Server
  • How to get Previous business day in a week with that of current Business Day using sql server
  • Reporting Services Chart - Hard Coded Series
  • Using SQL Server as Image store
  • Unable to perform delete on View SQL Server 2005
  • SQL Server 2008: Multiple joined columns to rows?
  • 3 Solutions collect form web for “Default parameter values in SQL Server 2008 stored procedure”

    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' 
    

    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.

    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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.