[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

SQL Stored Procedure Set a parameter as the Order By field name

Hello, quick question,

I'm using SQL Server 2005.
Let's say I have a table (Customers) with the following field:

CustomerID
FirstName
LastName
Address1
City
State
Zip

and I have a stored procedure that has 2 parameters.

@SearchVal
@MyField

and I have the following query syntax in my SP:

SELECT
CustomerID,
FirstName,
LastName,
Address1,
City,
State,
Zip
FROM      
Customer
WHERE
City = @SearchVal
Order by City

Is there a way I can use @MyField in both the WHERE and the Order By clauses like:

WHERE
@MyField  = @SearchVal
Order by @MyField


This way, I would have the option to search all of my fields without having to state it in the SP.  I'd just pass the search value and the name of the field I want to search and sort by.  

Do I have to declare @MyField as SYSNAME in order to make this work?

Thanks!
0
TheUndecider
Asked:
TheUndecider
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
You will have to do it with dynamic sql something like this.  But beware of SQL Injection if the values are coming in from open user entered data and not controlled by you somehow.
DECLARE @SQL NVARCHAR(2000)

@SQL = N'SELECT
CustomerID,
FirstName,
LastName,
Address1,
City,
State,
Zip
FROM      
Customer
WHERE ' +
@MyField + ' = @SearchVal
Order by ' + @MyField +';'

exec sp_executesql @SQL, N'@SearchVal varchar(100)', @SearchVal

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Here it is

Raj
CREATE PROCEDURE usp_Search_Order
(
	@MyField	NVARCHAR(50)
)
AS
BEGIN


	DECLARE @SQL VARCHAR(8000)

	SET @SQL = 'SELECT 
				CustomerID, 
				FirstName, 
				LastName, 
				Address1, 
				City, 
				State, 
				Zip 
				FROM       
				Customer 
				WHERE ' + 
				@MyField + ' = @SearchVal 
	Order by ' + @MyField 

EXEC (@SQL) 


END

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Oops! Missed Search field. Here is the corrected SP

Raj

CREATE PROCEDURE usp_Search_Order
(
	@MyField	NVARCHAR(50),
	@SearchVal	NVARCHAR(50)
)
AS
BEGIN


	DECLARE @SQL VARCHAR(8000)

	SET @SQL = 'SELECT 
				CustomerID, 
				FirstName, 
				LastName, 
				Address1, 
				City, 
				State, 
				Zip 
				FROM       
				Customer 
				WHERE ' + 
				@MyField + ' = ''' + @SearchVal + '''	Order by ' + @MyField 

EXEC (@SQL) 


END

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now