Modifying a procedure to accept multiple conditions

[dharmer1975]

I've got a procedure that essentially creates and runs a select statement on a view that is specified via a single parameter. I'd now like to add multiple 'where conditions' to the generated statement in the procedure via using additional parameters. For example, here's my original procedure:
----------------------------------------
CREATE PROCEDURE FERPmonthly @selectedview Varchar (20)
as

declare @x as varchar(500)
set @x = 'select distinct((select cast(sum(Number_of_Responses)as float) from ' + @selectedview + ') / (select cast(count(distinct(imc_trackingnumber))as float) from ' + @selectedview + ')) as "# of Responses to Resolution"
from '+ @selectedview
execute( @x )
-----------------------------------------

This essentially generates the following statement when executed as follows 'execute harmerd.FERPmonthly "harmerd.mar01"':

select distinct((select cast(sum(Number_of_Responses)as float) from harmerd.mar01) / (select cast(count(distinct(imc_trackingnumber))as float) from harmerd.mar01)) as '# of Responses to Resolution'
from harmerd.mar01

--------------------------------------

Now I'd like the procedure to be able to generate a statement like this (same statement but with a where condition):

select distinct((select cast(sum(Number_of_Responses)as float) from harmerd.mar01) / (select cast(count(distinct(imc_trackingnumber))as float) from harmerd.mar01)) as '# of Responses to Resolution'
from harmerd.mar01
where exists ( select *
from harmerd.mar01
where pool like 'CL%')

-------------------------------------

Can anyone help me modify this procedure to accept one or more where conditions?

I'd like to have a procedure that can easily build a dynamic query based on the parameters I provide. These parameters are being scraped from and html page and posted to a java servlet that in turn executes a callable statement and returns the results of the query. Any assistance would be greatly appreciated as I've been spinning my wheels for a couple weeks now. Thanks,

Dan

 

[Adita]

set @x = 'select distinct((select cast(sum(Number_of_Responses)as float) from ' + @selectedview + ') / (select cast(count(distinct(imc_trackingnumber))as float) from ' + @selectedview + ')) as "# of Responses to Resolution"
from '+ @selectedview+
'where exists ( select *
from ' +@selectedview +
'where pool like '''CL%''')'

 

[carlcom]

Hi Dan,

If I understand that you just want to add the where clause, try this:

CREATE PROCEDURE FERPmonthly @selectedview Varchar (20)
as

declare @x as varchar(500)
set @x = 'select distinct((select cast(sum(Number_of_Responses)as float) from ' + @selectedview + ') / (select cast(count(distinct(imc_trackingnumber))as float) from ' + @selectedview + ')) as "# of Responses to Resolution"
from '+ @selectedview
+' where exists ( select *
from ' + @selectedview + '
where pool like "CL%&quot'
execute( @x )

If this doesn't work try replacing the "CL%" w/ ''CL%''

Carl

 

[dharmer1975]

Thank you for the responses thus far. To clarify, what I'd like to be able to do is pass multiple 'where clauses' to the procedure. For example, I may want the where clause to be:

"where exists (select * from ' + @selectedview + ' where pool like "CL%&quot'"

but I might also want there to be more than one value in the where clause:

"where exists (select * from ' + @selectedview + ' where pool like "CL%" or "IOM%" or "VCS%&quot'"

Or perhaps the above statement as well as
"and where xxx_id = ('@parameter_x', '@parameter_y', '@parameter_z')

Does that make any sense? I want to be able to pass not only the view name to query, but multiple 'where clause' statements all at once. One time the procedure might receive 1 'where clause' parameter, the next time 3. Please let me know if you have any ideas--thanks.

Dan

 

[tlbroadbent]

Dan,

Have you resolved this, yet? If not, could you post how you intend to call the stored procedure. Will it be something like the following?

EXEC FERPmonthly "harmerd.mar01", "col1=cond1 and col2 In ('val1','val2','val3',...)"

That is, the 2nd parameter contains the where criteria. Terry

http://members.home.net/tlbroadbent/index.htm