SELECT procedure - ALL for integers

  • Thread starter Thread starter Arek
  • Start date Start date
A

Arek

Hello,

How can I create stored procedure with clause "where integer=ALL" that
would return all the values.

select * from tblA where integer = ALL

I cannot use like in this procedure and because this is integer I
cannot use ALL.

Thank you
Arek
 
Hi Arek,

I am not sure what you are after?
Do you want to select all distinct integers?
Or you want to select rows regardless of integer value?
 
I want to return all rows.

I use that in my stored procedure and when I have empty parameter I
would like to return all rows.

Arek
 
You can just put an if statement in your stored procedure, that will use the
parameter with a where clause if it's passed in, or just not use a where
clause otherwise.
 
This should do the trick!

spXXX (@Integer int)
AS
IF (@Integer is NULL)
BEGIN
SELECT * FROM tblQQQ
END
ELSE
BEGIN
SELECT * FROM tblQQQ WHERE intCol = @Integer
END
 
Do Not Prefix stored procedure names with sp or xp

It forces a double lookup, first in the Master database, then in the default
database.
 
I find this slightly easier to read:

spXXX (@Integer int)
AS
SELECT * FROM tblQQQ WHERE @Integer IS NULL OR intCol = @Integer

Scott C.
 
Back
Top