我想将存储过程中的选择查询作为argumnet传递

I made this procedure..

我做了这个程序..

ALTER PROCEDURE [dbo].[MyProcedure]
            @pSelect nvarchar
AS
BEGIN
    SET NOCOUNT ON;

    select @pSelect from tabel1
END

I want to pass a select query like from c# code to this stored procedure

我想将一个选择查询从c＃代码传递给此存储过程

MyProcedure("column1,column2");

How could I do this because stored procedure treat my parameter as a string and it behaves like

我怎么能这样做，因为存储过程将我的参数视为一个字符串，它的行为就像

select N'column1,column2' from tabel1

pls help me

请帮助我

or provide a better option for this

或者为此提供更好的选择

3 个解决方案

#1

You'll have to use dynamic sql inside the stored procedure.

您必须在存储过程中使用动态sql。

ALTER PROCEDURE [dbo].[MyProcedure]
    @pSelect nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(max)

    SET @SQL = 'select ' + @pSelect + ' from tabel1';

    EXEC (@SQL)
END

Here's a script to test the above stored procedure:

这是一个测试上述存储过程的脚本：

CREATE TABLE tabel1 (id int, data varchar(50))
INSERT INTO tabel1 VALUES(1,'aaa'),(2,'bbb'),(3,'ccc')

EXEC [dbo].[MyProcedure] 'id'
EXEC [dbo].[MyProcedure] 'data'
EXEC [dbo].[MyProcedure] 'id,data'

#2

You can use dynamic SQL for the purpose, but it is not recommended. (More info here - The Curse and Blessings of Dynamic SQL)

您可以使用动态SQL，但不建议这样做。（更多信息在这里 - 动态SQL的诅咒和祝福）

create procedure MyProcedure
@pSelect nvarchar
AS 
begin 
    declare @sql nvarchar(4000);
    set @sql='select ['+ @pSelect +'] from Table_1';
    exec sp_executesql @sql
end 
go

exec MyProcedure 'column1,column2'
go

#3

If your @pSelect is having entire query then:

如果你的@pSelect有整个查询，那么：

ALTER PROCEDURE [dbo].[MyProcedure]
            @pSelect nvarchar
AS
BEGIN
    SET NOCOUNT ON;    
    -- If you are@pSelect is having entire query
    exec (@pSelect)

    -- If you are passing only field list then following
    DECLARE @SQL nvarchar(max)

    SET @SQL = 'select ' + @pSelect + ' from tabel1';
END

#1