Solved

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

Posted on 2009-07-09
3
152 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select dynamic rows as columns? 3 54
temp table - optimal records for performance.. (tsql) 6 23
SQL HELP 2 75
Delete from table 6 36
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

11 Experts available now in Live!

Get 1:1 Help Now