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 sprGetNextItemRef -999,@strNewRef output
select @strNewRef
VB Code Not Working
==============
Private Sub ExecuteUpdate(ByVal tr As SqlTransaction)
Using cm As SqlCommand = tr.Connection.CreateCommand()
cm.Transaction = tr
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "sprGetNextItemRef"
cm.Parameters.AddWithValue("@intQuoteID", m_intQuoteID)
cm.Parameters.AddWithValue("@strItemRef", m_strNewRef)
cm.Parameters("@strItemRef").Direction = ParameterDirection.Output
cm.ExecuteNonQuery()
m_strNewRef = cm.Parameters("@strItemRef").Value
End Using
End Sub
If I change my vb procedure to the following I get correct values returned
Working VB Code
===============
Private Sub ExecuteUpdate(ByVal tr As SqlTransaction)
' If Not OnExecuteUpdate(tr) Then Return
Using cm As SqlCommand = tr.Connection.CreateCommand()
cm.Transaction = tr
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "sprGetNextItemRef"
cm.Parameters.AddWithValue("@intQuoteID", m_intQuoteID)
Dim p As New SqlClient.SqlParameter
With p
.ParameterName = "@strItemRef"
.SqlDbType = SqlDbType.NVarChar
.Size = 10
.Direction = ParameterDirection.Output
.Value = m_strNewRef
End With
cm.Parameters.Add(p)
cm.ExecuteNonQuery()
m_strNewRef = cm.Parameters("@strItemRef").Value
End Using
End Sub
Procedure
===========
USE [Contest03UK]
GO
/****** Object: StoredProcedure [dbo].[sprGetNextItemRef] Script Date:
05/02/2007 11:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sprGetNextItemRef]
-- 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_Reference
from
Item
where
item_quot_int_ID = @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(@strLastRef) = 1
begin
set @intPointer = charindex(N'.', @strLastRef)
if @intPointer 0
--non-integer
begin
set @strLeft = left(@strLastRef,@intPointer-1)
set @strRight = substring(@strLastRef,@intPointer + 1, len(@strLastRef))
set @strRight = convert(nvarchar(10), convert(int, @strRight)+1)
set @strItemRef = @strLeft + '.' + @strRight
end
else
begin
--integer
set @strItemRef = convert(nvarchar(10), convert(int, @strLastRef)+1)
end
end
else
begin
--not numeric
set @strLeft = left(@strLastRef,len(@strLastRef)-1)
set @strRight = right(@strLastRef,1)
set @strRight = char(ascii(@strRight)+1)
set @strItemRef = @strLeft + @strRight
end
