Invalid length parameter passed to SUBSTRING function

Question

Hey all,

Having a bit of a run in with SQL and Transactions... hoping some of you can shine a light on the problem. (and maybe even fix my screwed html! Does this site support formatting for different languages? Perl, SQL, Java?) This server runs on SQL Server 2005 and was very recently upgraded from SQL Server 2000. I'll keep an eye on this post throughout the day. Cheers

ALTER PROCEDURE [dbo].[sp_AddRequest] 
    -- Add the parameters for the stored procedure here
    @Message TEXT,
    @RequestId VARCHAR(20),
    @StatusCode CHAR(1),
    @StatusText VARCHAR(255),
    @AddedDate DATETIME,
    @MessageTimestamp DATETIME
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Direction is incoming (i.e. Client -> WEBAPP)
    DECLARE @Direction VARCHAR(50)
    SET @Direction = 'Client -> WEBAPP'

    -- Type is derived from:
    -- a) MessageType Element value OR
    -- b) Data Element first child OR
    -- c) Root Element name
    DECLARE @Type VARCHAR(50)
    SELECT @Type = dbo.fnGetValue('MessageType', @Message)
    IF @Type IS NULL SELECT @Type = dbo.fnGetFirstChild('Data', @Message)
    IF @Type IS NULL SELECT @Type = dbo.fnGetFirstChild(NULL, @Message) 

    -- MessageStatus is retrieved from the lookup table
    DECLARE @Status VARCHAR(50)
    SELECT @Status = Description FROM MessageStatus WHERE MessageStatusCode = @StatusCode

    -- Examine the Message root element
    IF dbo.fnGetFirstChild(NULL, @Message) = 'RequestMessage'
    BEGIN
     -- Insert values into the Transaction table
     INSERT INTO tblTransaction (RequestID, Direction, [Type], Status, StatusText, Sent, Received, Body)
     VALUES (@RequestId, @Direction, @Type, @Status, @StatusText, @MessageTimestamp, @AddedDate, @Message)
     RETURN @@IDENTITY
    END
    ELSE
    BEGIN
     -- Transaction is linked using the RequestId
     DECLARE @TransactionID INT
     SELECT @TransactionID = dbo.fnFindTransaction(@RequestId)

     -- Insert values into the RelatedMessage table
     INSERT INTO tblRelatedMessage (TransactionID, RequestID, Direction, [Type], Status, StatusText, Sent, Received, Body)
     VALUES (@TransactionID, @RequestId, @Direction, @Type, @Status, @StatusText, @MessageTimestamp, @AddedDate, @Message)
     RETURN @@IDENTITY
    END
END

Regards,

Answer 1

You had a missing end quote here (after WEBAPP):

 SET @Direction = 'Client -> WEBAPP'

(which I fixed in the listing) but I couldn't find any reference to a SUBSTRING ........ what is the problem, really?

Marc

Answer 2

Your substring must be in one the functions eg fnGetValue or fnGetFirstChild

Assuming it is, you can generate the same error by passing a negative number to the length parameter. Passing NULL or 'bob' or 2 billion or float: it either works or gives a different error.

SELECT SUBSTRING ('ffggg', 1, -1)

Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the substring function.

Another point: don't use @@IDENTITY. Ever. Use SCOPE_IDENTITY().