Dynamic SQL into SP

[admin]

If we have a query like these, we can just use by gate SP logic.

SELECT * from MyTable
where col1 = @val1
<cfif some condition>
AND col2 = @val2
<cfelseif condition2>
AND col3 = @val3
<cfelse>
AND col4 like @ val4
</cfif>


In CF part:

<cfset orderby =3>
<cfif some condition>
<cfset orderby =1>
<cfelseif condition2>
<cfset orderby =2>
<cfelse>
<cfset orderby =3>
</cfif>
pass this orderby as a parameter to the sp.
SELECT *
FROM MyTable
WHERE col1 = @val1
AND (
(@ orderby = 1 AND col2 = @val2)
OR
(@ orderby = 2 AND col3 = @val3)
OR
(@ orderby = 3 AND col4 like @val4)
)