September 23, 2008 at 4:54 am
Hi all,
I need to have parameter values in ORDER BY CLAUSE.
I have the following statement which doesnt seem to work:
DECLARE @Name nvarchar(60)
,@Order nvarchar(4)
SET @Name = 'Name'
SET @order = 'asc'
SELECT * FROM tbl_employee
ORDER BY @Name + '' + @order
but this doenst seem to work, it doesnt order by anything. I need to be able to order by @Name and have another parameter to set it to either ASC or DESC...
can u have dyanmic order by's?
any idea guys?
September 23, 2008 at 5:13 am
Hi,
Try this...
DROP TABLE #Test
CREATE TABLE #Test (fldName varchar(8))
INSERT INTO #Test
SELECT 'Suresh'
UNION ALL
SELECT 'Arun'
UNION ALL
SELECT 'Kamal'
UNION ALL
SELECT 'Bala'
DECLARE @Name varchar(8), @Order varchar(8),@Sql varchar(50)
SET @Name = 'fldName'
SET @Order = 'ASC'
SET @Sql = ''
SET @Sql = 'SELECT * FROM #Test ORDER BY ' + @Name + ' ' + @Order
EXEC (@Sql)
---
September 23, 2008 at 6:07 am
hi thanks this does work however how would i do wit without use T-SQL syntax ie without the exec @sql, jus run as a select with an order by without the '' etc...is there a way to do it this way?
September 23, 2008 at 6:56 am
I am afraid that there is no way out then dynamic SQL. Its not that bad why dont you want to use it?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply