Extrmemly Complicated Stored Procedure Problem

[ice7899]

InIn order to convert a coldfusion query to a stored procedure I've implemented the following code given to me in an earlier post.


SELECT Column1
From Table
WHERE Column2 = 0

<cfif someVar = 3>
AND Column3=0
</cfif>


becomes..........


SELECT Column1
From Table
WHERE Column2 = 0
And (
(@SomeVar = 3 And Column3 = 0)
Or IsNull(@SomeVar, 0) <> 3
)


However, my query has now got more complicated

It runs as follows

SELECT Column1
From Table
WHERE Column2 = 0

<cfif someVar = 3>
AND Column3=30
<cfelseif anotherVar=4>
AND Column4=40
<cfelse>
<cfif yetanothervar=5>
AND column5=50
</cfif>
</cfif>

How could I implement this as a stored procedure ?

Thanks for your help in advance...I know it's a tough one !

 

[jbenson001]

can you show the table(s) structure you are dealing with and some sample data and desired output?

 

[johpje]

Hi,

just write the if statements complete in the stored procedure:

SELECT Column1
From Table
WHERE Column2 = 0

<cfif someVar = 3>
AND Column3=0
</cfif>

becomes:

if (@someVar = 3)
BEGIN
  SELECT Column1
  FROM Table
  WHERE Column2 = 0
  AND Column3=0
END
ELSE
BEGIN
  SELECT Column1
  FROM Table
  WHERE Column2 = 0
END

or you could also do this:
(this will be a little slower!)

DECLARE @sql AS varchar(1000)

@sql='  SELECT Column1 FROM Table WHERE Column2 = 0'
if (@someVar = 3)
  @sql=@sql + 'AND Column3=0'

EXEC @sql

cheers,
Johpje

 

[ice7899]

Thanks

 

[ice7899]

if (@someVar = 3)
BEGIN
SELECT Column1
FROM Table
WHERE Column2 = 0
AND Column3=0
END
ELSE
BEGIN
SELECT Column1
FROM Table
WHERE Column2 = 0
END


Does this method have an impact on the execution plan?