Select, store procedure question?

[Iwan Petrow]

Hi,

I have a store procedure like this:

CREATE PROCEDURE AAA
@p1 int
@p2 datetime
@id int OUTPUT

AS
SET @id=(SELECT id FROM table1
WHERE p1=@p1 AND p2=@p2)


But p1, p2, @p1 and @p2 could be NULL and this is a problem because
it can happen something like this: p1=NULL (I use DbNull.Value to set
null parameters).

What could I do to deal with this problem? How to change the store
procedure? Or may be any ideas about c# code?

Thanks.

 

[Dave]

There are several ways you can check for a Null value in your procedure using SQL, but this is probably what your after:

IF @p1 IS NULL OR @p2 IS NULL -- Make sure p1 and p2 are not NULL values
RAISERROR ('Non-null values are required for p1 and p2', 16, 1); -- Throws an exception on the client
ELSE
SELECT TOP 1 @id = id FROM table1 WHERE p1=@p1 AND p2=@p2

If you don't want to throw an exception on the client then use this code instead:

IF @p1 IS NOT NULL AND @p2 IS NOT NULL -- Make sure p1 and p2 are not NULL values
SELECT TOP 1 @id = id FROM table1 WHERE p1=@p1 AND p2=@p2

 

[Iwan Petrow]

Hi,

But p1 could be null ( I designed my table in this way). So I would
like to execute all this
queries in one query(if it is possible):

SET @id=(SELECT id FROM table1
WHERE p1=@p1 AND p2=@p2)
--if @p1 is NULL
SET @id=(SELECT id FROM table1
WHERE p1 IS NULL AND p2=@p2)
-- if @p2 is null
.......

--if both are null
SET @id=(SELECT id FROM table1
WHERE p1 IS NULL AND p2 IS NULL)

 

[Bill Struve]

IF @p1 IS NOT NULL AND @p IS NOT NULL
SET @id=(SELECT id FROM table1WHERE p1=@p1 AND p2=@p2)
IF @p1 IS NULL AND @p IS NOT NULL
SET @id=(SELECT id FROM table1WHERE p1 IS NULL AND p2=@p2)
IF @p1 IS NOT NULL AND @p IS NULL
SET @id=(SELECT id FROM table1WHERE p1=@p1 AND p2 IS NULL)
IF @p1 IS NOT NULL AND @p IS NOT NULL
SET @id=(SELECT id FROM table1WHERE p1 IS NULL AND p2 IS NULL)


--


---------------------------------------------------------------------
"Are you still wasting your time with spam?...
There is a solution!"

Protected by GIANT Company's Spam Inspector
The most powerful anti-spam software available.
http://mail.spaminspector.com

 

[Iwan Petrow]

Thanks.

But when I have p1,..p7, this is a problem.