Add to Select in SP

[Andrzejek]

In my Stored Procedure I have a request from the DB based on the parameter passed.

I know I can do something like this:

[pre]
If MyParameter = 0 Then
Select * from SomeTable
And FieldX = 0
Else
Select * from SomeTable
Where SomeField = MyParameter
[/pre]
But, my ‘base’ Select is more complex and I would hate to have 2 ‘versions’ of it in my code. I would like to have something like this instead:

[pre]
Select ALotOfStuff
From SeveralTables
Where SomeField = SomeOtherField
And ...

If MyParameter = 0 Then[green]
--Add something to my Select[/green]
And FieldX = 0
Else[green]
--Add something else to my Select[/green]
And SomeField = MyParameter
[/pre]
I hope it is clear what I would like to have.

Any suggestions?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[fredericofonseca]

Select ALotOfStuff
From SeveralTables
Where SomeField = SomeOtherField
and (
(myparameter = 0 and fieldX = 0)
or (myparameter <> 0 and somefield = 0)
)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886