thanks that worked a treat!

I am using MSSQL so there were no worries there...

;=AC)

Pritesh

-----Original Message-----
From: Arbon Reimer [mailto:arbon_reimer@h...]
Sent: 14 August 2001 14:43
To: sql language
Subject: [sql_language] Re: how can I dynamically specify the orderby
values in a stored proc edure ?


You will need to build a string and then execute it as a SQL statement.
Example:

CREATE PROCEDURE procTestMe (@selectvar varchar(10), @orderbyvar
varchar(10))
AS
--first declare a variable to hold your sql statement
DECLARE @sqlstring nvarchar(255)
--build your SQL statement using variables
SET @sqlstring = 'SELECT myCol1, myCol2 FROM myTable WHERE myCol3 
''' +
@selectvar + ''' order by ' + @orderbyvar
--use the system's sp_executesql stored procedure to execute your sql
statement
EXEC sp_executesql @sqlstring
--voila!

Of course, this assumes you are using Microsoft SQL Server.  I'd have to
look through other database documentation to see if there is a stored
procedure or another way to execute a dynamically built SQL statement... 
I'm sure any enterprise-worthy database has some capability like this.
HTH
Regards,
Arbon Reimer



----- Original Message -----
From: "Pritesh Mehta" <pritesh.mehta@s...>
To: "sql language" <sql_language@p...>
Sent: Tuesday, August 14, 2001 4:43 AM
Subject: [sql_language] how can I dynamically specify the orderby values in
a stored procedure ?


> Hi all,
>
> I'm trying to pass in the order by values in a string variable to a 
> stored procedure. I can't, however, get it to work...
> If there is a solution or a better way of doing this would someone please
> let me know?
>
> TIA
>
> P