Geek Answers Handbook

Default Parameter Values ​​in SQL Server 2008 Stored Procedure

I feel like an idiot, but I can't make this parameter the default value ... Here's how I declare 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 procedure, except that I cannot get notReadythe default for @cacheStatus. Google says I'm using the correct syntax.

this is how i call 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 work now, but I don’t understand why, when I right-click and say to execute the stored procedure, then select the zero flags, why it will not be used by default. I assume the null check sends "NULL" to proc, not DBNull?

if @cacheStatus is null
begin
    set @cacheStatus ='notReady'
end
+5
3

, null ? , .

@JNK , :

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

:

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

@cacheStatus, .

+3

, , , "DEFAULT", NULL.

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
+2

More articles:


All Articles