Can I pass '=' or '>' as parameters?

Hi
I'm moving SQL in my app to Stored Procedures.

My SQL string is currently created within my app, and I have two check boxes and this C# code...

if(chkPartTime.Checked&&!chkFullTime.Checked) { sql+=" AND P0_Hours = 0"; }

if(!chkPartTime.Checked&&chkFullTime.Checked) { sql+=" AND P0_Hours > 0"; }

where sql is my query string

If both boxes are checked I don't care what value P0_Hours is, but at least one of them will be checked.

What I want to know is how I transfer such logic into a Stored Procedure, or whether I will need to creat 2 similar sp's and choose which to call in my app.

It will combined with a query (currently 60 lines) comprising of 3 UNIONs

TIA

Sure why not...

you could literally pass a char(1) of '>' or '=' and use it to build the sql string

However wouldn't it be better to pass 'bit' fields like this:

SP paremeters:

@.chkPartTime bit

@.chkFullTime bit

then in the code:

in the Where clause:

Code Snippet

and ((p0_hours = 0 and @.chkPartTime = 1 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1 and @.chkPartTime = 0))

or better yet... just have one parameter:

@.chkFullTime bit and then 0 = Parttime 1 = FullTime

so then the code would look like:

Code Snippet

and ((p0_hours = 0 and @.chkFullTime = 0) or (p0Hours > 0 and @.chkFullTime = 1))

-Robert

|||

use the flag parameter (new param) & pass the appropriate flag value to the sp.

for example on your code,

Code Snippet

int iFlag = 0;

if(chkPartTime.Checked && !chkFullTime.Checked) { iFlag = 1; }

if(!chkPartTime.Checked && chkFullTime.Checked) { iFlag = 2; }

//Pass this flag variable as parameter while calling the SP

on your SP,

Check the flag using IF condition are on where condition to get the required result.

Code Snippet

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

@.Flag = 0

Or

(@.Flag = 1 And P0_Hours =0)

Or

(@.Flag = 2 And P0_Hours >0)

--or

If @.Flag = 0

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

If @.Flag = 1

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours =0

If @.Flag = 2

Select * From

(

Select .... From Table1

Union ALL

Select .... From Table2

Union ALL

Select .... From Table3

) as Data

Where

P0_Hours > 0

|||

I don't know, I usually would just suggest a dynamic SQL stored procedure in these cases, particularly if there is any real heft to your data. The other solutions suggested are a good possibilities too, but the complexities of coding tend to turn things like this into a nightmare both to maintain, and to get a decent query plan anyhow.

Louis

|||

Thank you all

It was difficult to choose which to mark as the answer!

I had not heard of dynamic sp's (I come from a DB2 background, now using SQL Server) and I have learnt from all 3 answers, still not made my mind up which way to go, but thanks.