Passing fieldnames as parameter in stored procedure

Hi,

I have a sp like this
 
ALTER PROCEDURE [dbo].[st_test] @SelectString as varchar(8000)

as
select
@SelectString

from t1
ORDER BY userid

Open in new window


When I exec this sp
exec [st_test] "some string"

It does not return the value I need, instead, it returns number of rows in the table with "some string" as values.  Any ideas?  thanks
mcrmgAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
you need to use dynamic SQL to be able to do that. have a look at http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure
0
 
NorieConnect With a Mentor VBA ExpertCommented:
Try something like this.


SET @sql = 'SELECT ' + @SelectString + ' FROM t1 ORDER BY userid'

sp_executesql @sql
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
First of all: Such a generic procedure is not useful under most circumstances..

The only way to do it, is by using dynamic SQL, which has it's problems (scope, permissions, execution plans):

ALTER PROCEDURE [dbo].[st_test]
    @Columns AS NVARCHAR(MAX)
AS
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT @SelectString
        FROM t1;'
    SET @SQL = REPLACE(@SQL, '@Columns', @Columns)
    EXECUTE (@SQL);

Open in new window


Read also Erland's excellent article: The Curse and Blessings of Dynamic SQL
0
 
mcrmgAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.