Stored Procedure "IS NULL" in a param 1

[tesoro]

I am trying to create a stored procedure with a conditional that uses "IS NULL". I know how to do this by creating a QRY String within the procedure and then use Execute once the string is built, but was hoping for an easier, more eloquent, solution. Basically, I have 2 cases. In case 1, I need to return only the records where field2 IS NULL, and in case 2, I need to return all the records.

Something like this:

Case 1:

CREATE PROCEDURE dbo.my_Procedure(
@my_Param1 varchar(25),
...
)
AS ...
SELECT ...
FROM ...
WHERE (myTable.field1 LIKE @my_Param1) AND (myTable.field2 IS NULL)
...


Case 2:

CREATE PROCEDURE dbo.my_Procedure(
@my_Param1 varchar(25),
...
)
AS ...
SELECT ...
FROM ...
WHERE (myTable.field1 LIKE @my_Param1)
...

Is there a way to do this by passing a parameter ?

Thanks

 

[PHV]

You may use a boolean parameter and use this where clause:
WHERE myTable.field1 LIKE @my_Param1 AND (myTable.field2 IS NULL OR @my_Param2=True)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244