using variables in stored procedure
-
I'm trying to run a conditional query for a report. there are 3 variables, rate(int.), state(char(2)), and membership status(int.). For all three variables, if input value is 0, then it will include all. Here is my procedure:
CREATE PROCEDURE safety_rate
@rate int,
@state char (2),
@member int
AS
declare @rates varchar (50)
declare @states varchar (50)
declare @members varchar (50)
if @rate = 0
set @rates = 'rate > 0'
else
set @rates = 'rate = @rate '
if @state ='0'
set @states = ' and state > 0'
else
set @states = ' and state = ''' + @state + ''' '
if @member = 0
set @members = ' and member < 2'
else
set @members = ' and member = @member '
print @rates + @states + @members
begin
Select pothole_ky, rate, addr, roadway, intersection, location, comment,
email, state, member, emailAddr, entry_time
From pothole
--Where @rates + @states + @members
end
GO
It won't allow me to all where clause, and it can't accept @state and @member variables. Can you help me?
-
Use Dynamic SQL.
Mathew J Kulangara
sqladventures.blogspot.com -
can you be more specific, a sample syntax would be appreciated very much
-
OR
Select pothole_ky
, rate
, addr
, roadway
, intersection
, location
, comment
, email
, state
, member
, emailAddr
, entry_time
From
pothole
Where
rate = (case when @rate = 0 then rate else @rate)
and state = (case when @state = 0 then state else @state)
and member = (case when @member = 0 then member else @member)
Watch out for nulls though
Cheers,
* Noel -
In the past when I have had to work with this I have itilised the COALESCE feature. For example:
CREATE PROCEDURE sample
@p_some_num INTEGER = NULL
AS
SELECT field1, field2, field3, some_num
FROM table
WHERE sum_num = COALESCE (@p_some_num, some_num)
This keeps the procedure clean, versatile, and avoids the use of dynamic SQL.
-
Thank you all. I used Noel's sample and it worked out fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply