Get the output for a stored procedure in the format of a string in C#

Question

I have a stored procedure that returns a string:

DECLARE @SqlStatement VARCHAR(MAX)

SELECT @SqlStatement = 
    COALESCE(@SqlStatement, '') + 'DROP TABLE [TMP].' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TMP'

PRINT @SqlStatement

I try to return the string like so:

var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBBASE"].ConnectionString);

using (var cmd = new SqlCommand("proSQL", con)
{
    CommandType = CommandType.StoredProcedure
})
{
    cmd.Parameters.Add("SqlStatement", SqlDbType.VarChar);
    var returnParameter = cmd.Parameters.Add("@SqlStatement", SqlDbType.VarChar);
    returnParameter.Direction = ParameterDirection.Output;

    con.Open();
    cmd.ExecuteNonQuery();
    var result = returnParameter.Value;
}

When I run the code I get the error:

Procedure proSQL has no parameters and arguments were supplied

I am not sure what I am doing wrong. Can some please tell me how to get the output for a stored procedure in the format of a string?

I thought about return also, but no. Use returnParameter.Direction = ParameterDirection.Output instead — Jauch, Dec 09 '14 at 17:43
@Jauch You did mention output parameters in STORED PROCEDURE...Please paste whole SP — Dgan, Dec 09 '14 at 17:44
The stored procedure doesn't return anything. Anyway, whats the point of doing string concatenation in TSQL? — Jodrell, Dec 09 '14 at 17:47
To return something you need to use RETURN, but it will return only an integer. To output something else you need to mark it as OUTPUT. see my answer, @nate :) — Jauch, Dec 09 '14 at 17:50

Jauch · Answer 1 · 2014-12-09T18:16:56.833

0

Change in the SP:

DECLARE @SqlStatement VARCHAR(MAX) OUTPUT

Change in the C# code:

var returnParameter = new SQLParameter("@SqlStatement", SqlDbType.VarChar, -1);
returnParameter.Direction = ParameterDirection.Output; 
cmd.Parameters.Add(returnParameter);

edited Dec 09 '14 at 18:16

answered Dec 09 '14 at 17:38

Jauch

1,500
9
19

I recived the error: String[1]: the Size property has an invalid size of 0. – nate Dec 09 '14 at 17:52
@nate, you have to provide the size of the parameter of varchar type. Try with -1. exemple: http://msdn.microsoft.com/en-us/library/a1904w6t%28VS.80%29.aspx – Jauch Dec 09 '14 at 17:59
It errors on: cmd.ExecuteNonQuery(); – nate Dec 09 '14 at 18:03
try with the -1 in the definitopn of the parameter :) – Jauch Dec 09 '14 at 18:04
cmd.Parameters.Add("SqlStatement", SqlDbType.VarChar, -1); var returnParameter = cmd.Parameters.Add("@SqlStatement", SqlDbType.VarChar, -1); – nate Dec 09 '14 at 18:12
Returns the error: Procedure proSQL has no parameters and arguments were supplied. – nate Dec 09 '14 at 18:12
@nate, I think I figured out the problem. You have to set the direction before adding to the collection, it seems. I edidted the answer – Jauch Dec 09 '14 at 18:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66504/discussion-between-nate-and-jauch). – nate Dec 09 '14 at 18:29

score 0 · Answer 2 · answered Dec 09 '14 at 18:12

your SQL should look like this,

CREATE PROCEDURE [GetTableNames]
     @schema NVARCHAR(128)
AS
    SELECT
            [TABLE_NAME]
    FROM
            [INFORMATION_SCHEMA].[TABLES]
    WHERE
            [TABLE_SCHEMA] = @schema
RETURN 0;

Then you could get the string you want like this,

var tableNames = new List<string>();
var con = new SqlConnection(
              ConfigurationManager.ConnectionStrings["DBBASE"].ConnectionString);

using (var cmd = new SqlCommand("GetTableNames", con)
        {
            CommandType = CommandType.StoredProcedure
        })
{                 
    cmd.Parameters.Add("@schema", "TMP");    
    con.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while(reader.MoveNext())
        {
            tableNames.Add(reader.GetString(0))
        }
    }
}

var result = string.Join(
    Environment.NewLine,
    tableNames
        .Select(tn => string.Format("DROP TABLE [TMP].[{0}];", tn))
        .ToArray());

Get the output for a stored procedure in the format of a string in C#

2 Answers2