What to use for Size argument in Database.AddOutParameter when returning a string?

Question

I'm getting a string as an output parameter, and need to know what to set for the Size argument in the call to AddOutParameter.

I know I could just use some huge number, like int.MaxValue, but want to know best practices.

In SQL Server, the column is actually a uniqueidentifier type. The T-SQL statement being executed inserts a record, and then sets some output variables to the ID and GUID of the newly inserted records. This is the actual code I'm using, but with variable names changed.

database.AddOutParameter(cmd, "@someInt", DbType.Int32, 0);
database.AddOutParameter(cmd, "@someString", DbType.String, 0);

database.ExecuteNonQuery(cmd);

someInt = (int)database.GetParameterValue(cmd, "@someInt");
someString = database.GetParameterValue(cmd, "@someString").ToString();

When executed, I get the following error...

System.InvalidOperationException: String[2]: the Size property has an invalid size of 0.

So it's obvious to me that you can't just use a size of 0 with a string output parameter. You can do that with an Int32 output parameter, but I guess a string needs a valid size. So what is the best practice for setting the size? Can it just be a huge size without affecting performance at all? Can I just set it to int.MaxValue or something? Is there any constant that can be used here; (didn't see any String.MaxValue - you can probably tell I'm new to C#, with a Java background).

Should I find out what the max size of a uniqueidentifier column is and set the size to that? What about if I'm doing the same thing for a VARCHAR or NVARCHAR column?

I wish the framework would just do it for me; I don't want to specify a size for every string that I get as output. Anyone have any suggestions here for best practice?

I've read the posts below, as well as MSDN documentation, but there's not really a best practices answer to this that I've found yet.

AddOutParameter - non-magic number way of finding length of DBType.Int32

Read VARBINARY(MAX) from SQL Server to C#

Answer 1

As we found out you were using the wrong type for a UniqueIdentifer. You should use DbType.Guid instead of a string, but you raised other questions in the comments that I couldn't answer in a comment and I wasn't sure of so I needed to test.

They are

• What should you set the size for different string output parameters be?
• Does it matter if it's a Nvarchar or varchar?
• What happens if you make it too big or too small?

I started by using SqlCommandBuilder.DeriveParameters to find out what ADO.NET and SQL Server think it should be and then executed the Stored Procedure to see what our return values were.

Sql Type     | DbType                | Size | Returned string.Length()
----------------------------------------------------------------
Varchar(10)  | AnsiString            | 10   | 9
Char(10)     | AnsiStringFixedLength | 10   | 10
Nvarchar(10  | String                | 10   | 9
Varchar(max) | AnsiString            | -1   | 20,480 
NVarchar(max)| String                | -1   | 20,480

As expected the derived sizes matched the length field on the all the character types except for the max and the return values where the expected length. However looking at the max types and DbTypes we had some new questions to go with our first three.

• What's up with that AnsiString type and if we set it to DbType.String instead does it affect the output if we keep the same size? Answer: No it doesn't, probably because .NET strings are unicode

• Does increasing the Paramater.Size affect any of the non-max values? Answer: Yes but only char(10). It increases the output size by adding empty spaces.

• Does decreasing the Paramater.Size affect any of the non-max values? Yes it truncates the return values

• Is a size of -1 magic? Answer: Yes if you set the size to -1 it will return the values as though you had set them correctly

Test Code .NET 4.0 SQL Server 2008

SQL Code

CREATE PROCEDURE SomeOutput( 
@tenVC varchar(10)  output,
@tenC char(10) output,
@tenNVC nvarchar(10) output,
@maxVC varchar(max) output,
@maxNVC nvarchar(max) output,
@Indentifier uniqueidentifier output)
AS 


SELECT @tenC = '123456789',
       @tenVC = '123456789',
       @tenNVC = '123456789',
       @Indentifier = NEWID(),
       @maxVC = '',
       @maxNVC = ''



SELECT 
       @maxVC = @maxVC + '1234567890',
       @maxNVC = @maxNVC + '1234567890'
FROM
        master..spt_values 
WHERE
      type= 'P'    

C# Code

static void Main(string[] args)
{

    using (SqlConnection cnn = new SqlConnection("Server=.;Database=Test;Trusted_Connection=True;"))
    {
        SqlCommand cmd = new SqlCommand("SomeOutput", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        cnn.Open();
        SqlCommandBuilder.DeriveParameters(cmd);

        Printparams(cmd.Parameters, "Derived");

        foreach (SqlParameter param in cmd.Parameters)
        {
            //By default output parameters are InputOutput
            //This will cause problems if the value is both null
            if (param.Direction == ParameterDirection.InputOutput )
                param.Direction = ParameterDirection.Output;

        }


        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters ,"Executed");


        cmd.Parameters["@tenVC"].DbType = DbType.String;
        cmd.Parameters["@tenNVC"].DbType = DbType.AnsiString;

        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters, "DbType change");



        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.DbType != DbType.Int32
                && param.DbType != DbType.Guid
                && param.Size != -1)
            {
                param.Size = param.Size * 2;

            }
        }

        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters, "Mangeled sizes up");


        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.DbType != DbType.Int32 
                && param.DbType != DbType.Guid
                && param.Size != -1)
            {
                param.Size = param.Size / 4;

            }
        }

        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters, "Mangeled sizes down");

        cmd.Parameters["@maxVC"].Size = Int32.MaxValue;
        cmd.Parameters["@maxNVC"].Size = Int32.MaxValue;

        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters, "Fixed max sizes");

        foreach (SqlParameter param in cmd.Parameters)
        {
            if (param.DbType != DbType.Int32
                && param.DbType != DbType.Guid)
            {
                param.Size = -1;

            }
        }

        cmd.ExecuteNonQuery();

        Printparams(cmd.Parameters, "is negative one magic");

        }
}

Outputs

Derived
@RETURN_VALUE : Int32 : 0 : ReturnValue : 0 :
@tenVC : AnsiString : 10 : InputOutput : 0 :
@tenC : AnsiStringFixedLength : 10 : InputOutput : 0 :
@tenNVC : String : 10 : InputOutput : 0 :
@maxVC : AnsiString : -1 : InputOutput : 0 :
@maxNVC : String : -1 : InputOutput : 0 :
@Indentifier : Guid : 0 : InputOutput : 0 :

Executed
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : AnsiString : 10 : Output : 9 : 123456789
@tenC : AnsiStringFixedLength : 10 : Output : 10 : 123456789
@tenNVC : String : 10 : Output : 9 : 123456789
@maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : eccc3632-4d38-44e8-9edf-031

DbType change
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : String : 10 : Output : 9 : 123456789
@tenC : AnsiStringFixedLength : 10 : Output : 10 : 123456789
@tenNVC : AnsiString : 10 : Output : 9 : 123456789
@maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : 94cb0039-8587-4357-88fb-25c

Mangeled sizes up
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : String : 20 : Output : 9 : 123456789
@tenC : AnsiStringFixedLength : 20 : Output : 20 : 123456789
@tenNVC : AnsiString : 20 : Output : 9 : 123456789
@maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : 4de88f14-9963-4a78-b09b-bb6

Mangeled sizes down
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : String : 5 : Output : 5 : 12345
@tenC : AnsiStringFixedLength : 5 : Output : 5 : 12345
@tenNVC : AnsiString : 5 : Output : 5 : 12345
@maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : 5e973e72-14e5-4b75-9cff-e88

Fixed max sizes
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : String : 5 : Output : 5 : 12345
@tenC : AnsiStringFixedLength : 5 : Output : 5 : 12345
@tenNVC : AnsiString : 5 : Output : 5 : 12345
@maxVC : AnsiString : 2147483647 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : 2147483647 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : 6cab2b41-d4ba-42d2-a93a-e59

is negative one magic
@RETURN_VALUE : Int32 : 0 : ReturnValue : 1 : 0
@tenVC : String : -1 : Output : 9 : 123456789
@tenC : AnsiString : -1 : Output : 10 : 123456789
@tenNVC : AnsiString : -1 : Output : 9 : 123456789
@maxVC : AnsiString : -1 : Output : 20480 : 123456789012345678901234567
@maxNVC : String : -1 : Output : 20480 : 123456789012345678901234567
@Indentifier : Guid : 0 : Output : 36 : 0d69ed57-fab7-49c8-b03a-d75