please help me to cearte this stored procedure

Hi ,

I want to make a report of records of a table, there is abut 15 fields that this report based on them , so we need a select query like this

Select f1,f2from Table1where f3=@.f3and f4=@.f4 and ….and f17=@.f17

-- f1 = field1 and ….

The problem is sometimes @.fs are empty, for example if @.f4was empty so "and f4=@.f4" should be excluded from the select query .(and it means there is no limitation for f4 field)

I know, probably I couldn't explain my purpose very well, but I hope somebody kindly try to understand it .

how can I perform that in a stored procedure?

Please help me

Thank you

try it like this:

SELECT f1,f2FROM Table1WHERE (f3=@.f3OR @.f3ISNULL)AND (f4=@.f4OR @.f4ISNULL)AND ….AND (f17=@.f17OR @.f17ISNULL)

|||

Is in your example @.f4 empty or null? I think null, so I created the following query for you:

Select

f1from table1where f1= @.f1and(f4= @.f4or @.f4isnull)

This query selects all the records where f1 matches @.f1 and f4 matches @.f4 or @.f4 is null (and will be ignored then).

I hope this helps

Richard

|||

mbanavige & richardsoeteman.net thank you very, very much!.

|||

Hi

Assume there are 3 tables like these:

Table0

Primary key

Name

1

Name1

2

Name2

3

Name3

Table 1:

Foreign key

Column1

1

Data1

2

Data1

Table2:

Foreign key

Column2

2

Data2

3

Data2

And there are 2 parameters that they may be null: @.Data1 and @.Data2

I need a select query that

-selects "Name2" from Table0 if:

@.Data1="Data1"

@.Data2="Data2"

-selects "Name1, Name2" from Table0 if:

@.Data1="Data1"

@.Data2=null

-selects "Name2, Name3" from Table0 if:

@.Data1=null

@.Data2="Data2"

And selects "Name1, Name2, Name3" from Table0 if both of@.Data1 &@.Data2 wasnull.

I know I did not explain very well again but it's the final step of my project and I really need help. So please help me again

Thanks,

|||

Same concept:

Read this post:http://forums.asp.net/thread/1440706.aspx

|||

thank you Mike,

What about parameters that areint ordecimal and we want to ignore them , they can not benull ,they can be 0.

thank you Mike,

|||

You can use the same concept

Select

f1from table1where(f1= @.f1or @.f1=0)

|||Thank you Richard,