Take a look at sp_executesql stored procedure. You can create a sql statement inside a stored procedure and pass it to sp_executesql for execution.

It runs slower than a regular stored procedure, but it's an option for dynamically created sql statements.

>All,
>
>I'm having a problem with what should be a trivial undertaking.
>
>I have a SP where I have 7 sort orders. 6 needs to be ASC (default) and 1 needs to be DESC. I cannot figure out how to implement this.
>
>SELECT *
>FROM (table)
>WHERE (where)
>ORDER BY
>CASE @iorder
>WHEN 0 THEN (sort1)
>WHEN 1 THEN (sort2)
>WHEN 2 THEN (sort3)
>WHEN 3 THEN (sort4)
>WHEN 4 THEN (sort5)
>WHEN 5 THEN (sort6)
>WHEN 6 THEN (sort7)
>ELSE (default)
>END
>ASC
>
>I thought I could do a
>
>CASE @iorder
>WHEN 3 THEN DESC
>ELSE ASC
>END
>no go (according to the syntax checker). Any ideas to assign ASC or DESC based on the sort order?
>
>TIA,
>Doug