dynamic query in a stored procedure

[Riverman1111]

Hi,

I have a need to create a stored procedure that has 3 input parameters which are to be used as select criteria against a table. Each of the 3 parameters can either be specific values, or 'all'.

if parm a = 1, b = 2, and c='all' the query would be:

select * from table
where (a like 1 and b like 2 and c like '%%')

Is there a way to create dynamic sql in a stored procedure?

Thanks!

 

[jaxtell]

You could make it dynamic but you probably don't need to. I think you could just have three variables, var_a, var_b, and var_c. Declare a cursor

select * from table where a like var_a and b like var_b and c like var_c

then for each variable, if the input value = 'all', then change it to '%'

I think thats it.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[smfayayz]

Hi

I am new to this forum and have question, can someone help me with right sql syntax.

I am joining two table where one table has numeric field 5 digit long and other is numeric 3 digit long.

Like: Library.table1.cyymm = Library.table2.cyy(1:3)

what I mean is what is correct syntax for sql to parsing numeric field.

Thanks
Syed.

 

[vacunita]

First: Please don't hijack other people's threads with unrelated questions.

Start your own thread and post your question. This assures it gets the most views and can be answered promptly.


Second I'm not sure I understand the question.

what exactly do you mean by parsing?






----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[smfayayz]

I appologies for that.

Syed.