Command Object PArameters

[=?Utf-8?B?VGVycnkgSG9sbGFuZA==?=]

I have written a stored procedure in SQL 2005 as below.

When I call this procedure from QA with the SQL QA Test code the value of
@strNewRef is returned correctly with my limited amount of testing thus far.

When I try to use this procedure from my vb code im getting some unexpected
results.

Using the "VB Code Not Working" code I get incorrect results. The value
returned into m_strNewRef is only ever 1 char long

If I change my vb procedure to the "Working VB Code" I get correct values
returned
By messing about with "Working VB Code" I can see that by not setting the
Size property of the parameter, I get incorrect results ie only 1 character
being returned.

Could soeone please explain this behaviour

Terry Holland
SQL QA Test
=============
declare @strNewRef nvarchar(10)
exec sprGetNextItemR ef -999,@strNewRef output
select @strNewRef

VB Code Not Working
==============
Private Sub ExecuteUpdate(B yVal tr As SqlTransaction)

Using cm As SqlCommand = tr.Connection.C reateCommand()
cm.Transaction = tr
cm.CommandType = CommandType.Sto redProcedure
cm.CommandText = "sprGetNextItem Ref"
cm.Parameters.A ddWithValue("@i ntQuoteID", m_intQuoteID)
cm.Parameters.A ddWithValue("@s trItemRef", m_strNewRef)
cm.Parameters(" @strItemRef").D irection = ParameterDirect ion.Output

cm.ExecuteNonQu ery()

m_strNewRef = cm.Parameters(" @strItemRef").V alue

End Using
End Sub

If I change my vb procedure to the following I get correct values returned

Working VB Code
===============
Private Sub ExecuteUpdate(B yVal tr As SqlTransaction)
' If Not OnExecuteUpdate (tr) Then Return
Using cm As SqlCommand = tr.Connection.C reateCommand()
cm.Transaction = tr
cm.CommandType = CommandType.Sto redProcedure
cm.CommandText = "sprGetNextItem Ref"
cm.Parameters.A ddWithValue("@i ntQuoteID", m_intQuoteID)
Dim p As New SqlClient.SqlPa rameter
With p
.ParameterName = "@strItemRe f"
.SqlDbType = SqlDbType.NVarC har
.Size = 10
.Direction = ParameterDirect ion.Output
.Value = m_strNewRef
End With
cm.Parameters.A dd(p)
cm.ExecuteNonQu ery()

m_strNewRef = cm.Parameters(" @strItemRef").V alue

End Using
End Sub

Procedure
===========
USE [Contest03UK]
GO
/****** Object: StoredProcedure [dbo].[sprGetNextItemR ef] Script Date:
05/02/2007 11:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
-- =============== =============== ===============
-- Author: <Author,,Name >
-- Create date: <Create Date,,>
-- Description: <Description, ,>
-- =============== =============== ===============
ALTER PROCEDURE [dbo].[sprGetNextItemR ef]
-- Add the parameters for the stored procedure here
@intQuoteID int,
@strItemRef nvarchar(10) = '' output
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @strLastRef nvarchar(10)
declare @intPointer int
declare @strLeft nvarchar(10)
declare @strRight nvarchar(10)

--Get last Item Ref for Items related to Quote
select top (1)
@strLastRef = item_txt_Refere nce
from
Item
where
item_quot_int_I D = @intQuoteID
order by
item_int_ID desc
--Set @strLastRef to 0 if the item ref
--of last item is null or empty
if @strLastRef = null
begin
set @strLastRef = 0
end
if @strLastRef = ''
begin
set @strLastRef = 0
end

/*
If last item ref is numeric the next ref should also
be numeric and it should be incremented
ie
Last Next
100 >101
10.1 >10.1

If last item ref is non-numeric the next ref should
increase the last character by 1 ascii value
ie
Last Next
1a >1b
Item 1 >Item 2
*/
if isnumeric(@strL astRef) = 1
begin
set @intPointer = charindex(N'.', @strLastRef)
if @intPointer 0
--non-integer
begin
set @strLeft = left(@strLastRe f,@intPointer-1)
set @strRight = substring(@strL astRef,@intPoin ter + 1, len(@strLastRef ))
set @strRight = convert(nvarcha r(10), convert(int, @strRight)+1)
set @strItemRef = @strLeft + '.' + @strRight
end
else
begin
--integer
set @strItemRef = convert(nvarcha r(10), convert(int, @strLastRef)+1)
end

end
else
begin
--not numeric
set @strLeft = left(@strLastRe f,len(@strLastR ef)-1)
set @strRight = right(@strLastR ef,1)
set @strRight = char(ascii(@str Right)+1)
set @strItemRef = @strLeft + @strRight
end