how to pass parameter for order by class from asp.net website to store procedure

I am trying to create a stored proc which will take a optional parameter
and if it passed from my website then its value should add in the order by clause.

I tried to write the proc like below but it does not work. I got an compile error.

any one could help me out here
create PROCEDURE [dbo].[sp_GetRecord]
	@ClipSerialNumberID int	,
	@SortColumnName varchar(100) = null
	
AS
BEGIN
	if(@SortColumnName IS null)
	BEGIN 
		SELECT [OID],[Name],[ClipSerialNumberID]
		FROM [Record]
		where ClipSerialNumberID  = @ClipSerialNumberID  
	END
	ELSE
	BEGIN
		SELECT [OID],[Name],[ClipSerialNumberID]
		FROM [Record]
		where ClipSerialNumberID  = @ClipSerialNumberID 
		ORDER BY @SortColumnName
	END	
	
END

Open in new window

ziorrinfotechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
create PROCEDURE [dbo].[sp_GetRecord]
      @ClipSerialNumberID int      ,
      @SortColumnName varchar(100) = null
     
AS
BEGIN
      if(@SortColumnName IS null)
      BEGIN
            SELECT [OID],[Name],[ClipSerialNumberID]
            FROM [Record]
            where ClipSerialNumberID  = @ClipSerialNumberID  
      END
      ELSE
      BEGIN
            DECLARE @nSQl NVARCHAR(1000)
            SELECT @nSQl = '
            SELECT [OID],[Name],[ClipSerialNumberID]
            FROM [Record]
            where ClipSerialNumberID  = @ClipSerialNumberID
            ORDER BY ' + @SortColumnName
        EXEC sp_ExecuteSQL @nSQl, N'@ClipSerialNumberID int ' ,@ClipSerialNumberID  
      END      
     
END

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
As aneeshattingal has pointed out, you would have to execute the query with dynamic SQL.  

http:#a24815926 is the correct solution
0
Aneesh RetnakaranDatabase AdministratorCommented:
or else you have to use a Large case statement

ORDER BY case (@SortColumnName ) WHEN 'ID' THEN ID
                                                               WHEN 'Name' THEN NAME,
                                                               ELSE ClipSerialNumberID END
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.