Parms

I was wondering how to default an input parm to nulls.
I created a proc which looked liked the following:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID = null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
This compiles, but it does not run correctly. If I change the
declaration of @.ClubID and removed the '=null' - then it compiles, but
it requires the @.ClubID to be populated (will not accept nulls).
How do I define an input parm which is not required to be populated
(accepts nulls and is defaulted to null)?Hi Jim.
The parameter is defined correctly - you should check for null by using IS
NULL rather then = null in the body of the proc:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID is null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
Regards,
Greg Linwood
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9458D9954D842JimHeaveyhotmailcom@.207.46.248.16...
> I was wondering how to default an input parm to nulls.
> I created a proc which looked liked the following:
> ALTER proc dbo.Proc_QuerySkaters
> @.ClubID uniqueidentifier =null,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> if @.ClubID = null
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> set @.cnt = @.@.ROWCOUNT
> end
> else
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> where @.clubID = ClubID
> set @.cnt = @.@.ROWCOUNT
> end
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
> - Error code: ' + cast( @.@.ERROR as char(4) )
> return -1
> end
> set @.msg=''
> return 0
> This compiles, but it does not run correctly. If I change the
> declaration of @.ClubID and removed the '=null' - then it compiles, but
> it requires the @.ClubID to be populated (will not accept nulls).
> How do I define an input parm which is not required to be populated
> (accepts nulls and is defaulted to null)?