i want to pass a select query in a stored procedure as an argumnet

Question

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

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

Answer 1

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

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'