通过查询调用存储过程并在没有输出参数的情况下获取C＃中的返回值

Is there anyway I can execute stored procedure via EXEC (so not specifying CommandType.StoredProcedure in C#) and get result?

无论如何我可以通过EXEC执行存储过程(所以不在C#中指定CommandType.StoredProcedure)并获得结果?

I tried to execute query

我试图执行查询

DECLARE @R int 
EXEC @R = proc p1, p2

and then to grab @R but without success.

然后抓住@R但没有成功。

What I don't want is to do it the usual way by adding parameters and using ParameterDirection.ReturnValue to obtain result.

我不想要的是通过添加参数和使用ParameterDirection.ReturnValue来获取结果的常规方法。

I would appreciate any info. Ty.

我会很感激任何信息。 TY。

EDIT

Some people asked why.

有人问为什么。

I need to create little console program that runs any stored procedure and gets any kind of result it returns. The app accepts .config file with description of database conn and name of sp.

我需要创建一个运行任何存储过程的小控制台程序,并获得它返回的任何结果。该应用程序接受.config文件,其中包含数据库conn的描述和sp的名称。

Now, if I do it the usual way, I need config entery for each parameter or if call is specified in usual query format then I need to parse it myself. The user needs to know parameter details etc...

现在,如果我按常规方式执行,我需要为每个参数配置entery,或者如果以通常的查询格式指定调用,那么我需要自己解析它。用户需要知道参数细节等...

Thats why I choosed exec approach. User can specify in config file stored procedure and params under one key.

这就是我选择exec方法的原因。用户可以在配置文件中指定存储过程和params在一个键下。

   <add key="Procedure" value="dbo.TEST 10,20"/>

and don't think much about it. People that work on such programs don't understand programming things, they know they need to put some text as arguments (i.e. implicit conversion is mistery)

并且不要太在意它。在这些程序上工作的人不理解编程事物,他们知道需要将一些文本作为参数(即隐式转换是错误的)

The program generally wont accept any user parameters except inital config so sql injection and such don't exist, and even if thats concern injection check can be moved to SP level.

程序一般不会接受任何用户参数,除了初始配置,所以sql注入和这样的不存在,即使这涉及注入检查可以移动到SP级别。

Implementation ofc becomes trivial:

c的实现变得微不足道:

    const string runFormat = "exec ('declare @r varchar (1024);EXEC @r={0}{1};select @r')";

    public bool Run(string parameters, out string result) {
        SqlConnection con = new SqlConnection( connectionString );

        SqlCommand cmnd = new SqlCommand(String.Empty, con);
        cmnd.CommandText = String.Format(runFormat, storedProcedure, parameters ?? String.Empty);  

        try {
            con.Open();
            result = (string)cmnd.ExecuteScalar();
        }
        catch (Exception e) {
            result = e.Message;
            return false;
        }
        return true;
    }

2 个解决方案

#1

try this running this query in C#:

试试这个在C#中运行这个查询:

exec ('declare @r int;EXEC @r=testing 1,2;select @r')

exec('declare @r int; EXEC @ r = testing 1,2; select @r')

#2

You can't get hold of @R since it's declared in a batch which has no knowledge of an outside world. BLToolkit to rescue (roughly):

你无法掌握@R,因为它是在一个不知道外部世界的批处理中声明的。 BLToolkit拯救(大致):

[SqlQuery("declare @r int; exec @r = proc @p1, @p2; return @r")]
public abstract object ExecProc(int @p1, int @p2);

#1