What to use for Size argument in Database.AddOutParameter when returning a string?
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.Sizeaffect 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.Sizeaffect any of the non-max values? Yes it truncates the return valuesIs 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
Jim
Updated on July 18, 2022Comments
-
Jim almost 2 years
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