Calling stored procedure via query and getting return value in C# without output parameters

Question

Hello

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

I tried to execute query

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

and then to grab @R but without success.

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

I would appreciate any info. 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.

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...

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

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

and dont 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.

Implementation ofc becomes trivial:

    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, storeProcedure, parameters ?? String.Empty);  

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

Answer 1

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):

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

Answer 2

try this running this query in C#:

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