我想将存储过程中的选择查询作为argumnet传递 [英] i want to pass a select query in a stored procedure as an argumnet

问题描述

我做了这个程序..

  ALTER   PROCEDURE  [dbo]。[MyProcedure] 
  @ pSelect   nvarchar  
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 
 选择  @ pSelect  来自 tabel1 
  END  
 

我想传递一个选择查询比如从c＃代码到这个存储过程

 MyProcedure（ < span class =code-string> column1，column2）; 
 

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

 选择 N '  column1，column2' 来自 tabel1 
 

请帮助我



或为此提供更好的选择

解决方案

尝试：

  EXEC （'  SELECT' +  @ pSelect  + '  FROM Table1'）

[edit]

但是，要非常小心 - 你需要仔细审核@pSelect，因为这是SQL注入攻击的一个很好的来源！

[/ edit]

用途Exec（执行）语句。

  ALTER   PROCEDURE  [dbo]。[MyProcedure] 
  @ pSelect   nvarchar  
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
  Exec （' 选择' +  @ pSelect  + ' 来自tabel1;'）; 
  END  

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

解决方案

Try:

EXEC ('SELECT ' + @pSelect + ' FROM Table1')

[edit]
But, be very careful - you need to vet @pSelect pretty carefully, as this is a good source of SQL injection attacks!
[/edit]

Uses the Exec (Execute) statement.

ALTER PROCEDURE [dbo].[MyProcedure]
            @pSelect nvarchar
AS
BEGIN
    SET NOCOUNT ON;
    Exec('Select ' + @pSelect + ' from tabel1;');
END

这篇关于我想将存储过程中的选择查询作为argumnet传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！