Solved

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

Posted on 2009-07-09
3
153 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:ziorrinfotech
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 125 total points
ID: 24815926
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24815966
As aneeshattingal has pointed out, you would have to execute the query with dynamic SQL.  

http:#a24815926 is the correct solution
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24815980
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What is the best way to calculate hours worked 5 61
PERFORMANCE OF SQL QUERY 13 66
Truncate vs Delete 63 102
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 40
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now