Stored Procedure Order By Variable

[OberCanober]

I want to query my table with a stored procedure with a way for the user of my program to change how the data is ordered.

So I wrote my Stored procedure out like this...

CREATE     PROCEDURE spVerifyComplete

(
@Order1 as column,
@Order2  as column
)

As
 SELECT
*
 FROM RSR
 WHERE (RSRCompleted = "NO"
ORDER BY @Order1, @Order2

GO

But it don't work.

Can someone tell me what I did wrong?

Thanks,

-Ober

[abhijit]

Its not going to work this way.

You need to assign the whole query to a variable and use the exec statement to run it correctly.

Cheers,
Abhijit

[mendhak]

WHERE (RSRCompleted = "NO"
---------^

could it be that?

[vb_dba]

As abhijit said:

CREATE     PROCEDURE spVerifyComplete

(
@Order1 as column,
@Order2  as column
)

As

Declare @Sql varchar(8000)
Set @Sql = 'Select * '
Set @Sql = @Sql + 'From RSR '
Set @Sql = @Sql + 'Where Completed = "NO" '
Set @Sql = @Sql + 'Order By ' + @Order1 + ', ' + @Order2

Exec (@Sql)

[OberCanober]

Yup,

I wrote that after working all day/night on Christmas day.

By morning of the day after Christmas, I was tired and when your tired and not to mention hungry its hard to find the simple errors.

I cannot connect to the SQL server from home so I'll have to go to work and fix it. ::sigh::

Thanks for your help.

-Ober

[Mongo]

Column datatype?!?! Also, list the specific table columns in an SQL statement.

[Virgil J. Schmidt]

Originally posted by OberCanober
Yup,

-Ober

Can this work with LIKE in a where clause. I attempted to use this with LIKE and % and I get a syntax error.