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!
TheUndeciderAsked:
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.

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

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
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.