Quick question to check NULL values in input parameters in a stored procedure

Hi:

I have a stored procedure that calls 3 stored procedures. If some of my input parameters are NULL, I would like to skip the call to another stored procedure. Can you someone please help me with this? I would like to find out what is NULL, before I execute the other stored procedures. Thanks so much.

MA

check with is not null

example

If @.Var1 is not null
begin
exec proc1 @.Var1
end

If @.Var2 is not null
begin
exec proc2 @.Var2
end

If @.Var3 is not null
begin
exec proc3 @.Var3
end

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Is there a way loop thru the parameters in one go, because in some instances I am dealing with a set of 50 or more parameters. Thanks.|||

something like this perhaps

declare @.v int
declare @.v2 int
declare @.v3 int

select @.v =1,@.v2 =3

if exists (select * from (select @.v as a union all
select @.v2 union all
select @.v3) z where a is null)
begin
print 'at least one parameter has a null value'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Going back to your initial response, which I think I will respond as an Answer to my question, because it is my best bet at this moment. I do have a quick question in reference to your first response, here it is:

If I have more than parameters, that I need to check for NULL, and if its NULL then dont execute the SP, and vice versa, how would i do that? Can i do something like this, my goal is to check/validate that if all values passed in are NULL, then dont call the sp:

IF @.CitizenshipStatusCode is null and
@.GovtIDTypeCode is null and
@.AlienID is null and
@.EmploymentStatusCode is null and
@.EmployerName is null and
@.EmployerAddress1 is null and
@.EmployerAddress2 is null and
@.EmployerAddress3 is null and
@.EmployerCity is null and
@.EmployerStateCode is null and
@.EmployerZipCode is null and
@.EmployerCountryCode is null and
@.Position is null and
@.WorkForeignPhoneExchange is null and
@.WorkAreaCode is null and
@.WorkPhoneNumber is null and
@.WorkExtension is null and
@.WorkEmail is null and
@.EmploymentYears is null and
@.EmploymentMonths is null and
@.MonthlySalaryAmount is null and
@.MonthlyRentAmount is null and
@.OtherMonthlyIncome is null and
@.ResidenceTypeCode is null and
@.CreatedPersonID is null and
@.UpdatedOn is null and
@.CreatedPersonID is null and
@.UpdatedOn is null
BEGIN
Set @.IsNull = 1
END
ELSE
Set @.IsNull = 0

|||

you could use coalesce since coalesce returns the first non null value

examples

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

select @.v ='1',@.v2 =3
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end
go

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

--will be null
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||thanks I think this is what I can use. Also, why do you have the word null at the end, inside the parantheses. Is that necessary? Whats the purpose of that?|||It is not necessary to have NULL at the end. If all of the inputs to COALESCE is NULL then it will return NULL anyway.