Solved

Passing fieldnames as parameter in stored procedure

Posted on 2016-10-04
4
73 Views
Last Modified: 2016-10-04
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
0
Comment
Question by:mcrmg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 34

Assisted Solution

by:Norie
Norie earned 100 total points
ID: 41828608
Try something like this.


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

sp_executesql @sql
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41828612
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
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 150 total points
ID: 41828613
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
 

Author Closing Comment

by:mcrmg
ID: 41828621
thank you
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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