Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    Unanswered: Inconsistent return from SQL

    I have a stored procedure that is passed two values. Both are strings representations of GUID values, the first is a single value, the second is a comma delimited string of values.

    In the stored procedure I call a split function to separate the comma delimited values into a table and this is used in my WHERE clause to filter my select results.

    This is an example:

    Code:
    SELECT 
        item.uiGUID as ItemGUID,
        stores.strStoreName as Store,
        location.strLocationName as Location
        FROM tblItems as item
        INNER JOIN tblStoreLocations as location
            ON item.uiLocationGUID = location.uiGUID 
        INNER JOIN tblStores as stores
            ON location.uiStoreGUID = stores.uiGUID
        WHERE CAST(item.uiGUID as varchar(36)) IN (SELECT Value FROM dbo.Split(',',@ItemGUIDList))
    When I run this query in the management studio, passing a list of 5 values in the second parameter, my results include one item for each of the 5 values. However, when I pass the parameters from my ASP project, (I've verified the values I think are being passed are indeed being passed), I only get one item.

    I believe the error is in my split function. Both split functions return the same results in the SQL management studio, but only one returns the correct results in the the ASP project.

    When I use this version of the function it returns the correct table values to the calling application, but it chokes when the item list does not have a trailing comma. I figure that to be a bug in the SQL function.

    Code:
    CREATE FUNCTION [dbo].[Split] 
       (  @Delimiter varchar(5), 
          @List      varchar(8000)
       ) 
       RETURNS @TableOfValues table 
          (  RowID   smallint IDENTITY(1,1), 
             [Value] varchar(50) 
          ) 
    AS 
       BEGIN
        
          DECLARE @LenString int 
    	  DECLARE @SplitString varchar(50)
    	  WHILE(len(@List) > 0)
    	  BEGIN
    	  SET @SplitString = substring(@List,0,charindex(@Delimiter,@List)) 
    	  SET @List = substring(@List,charindex(@Delimiter,@List)+1,len(@List))
    	  INSERT INTO @TableOfValues VALUES (@SplitString) 
    	  END
          RETURN 
          
       END
    This function returns the correct values only in the management studio, and when called from code, returns only a single row, despite multiple rows being selected in the SQL environment.

    Code:
    CREATE FUNCTION [dbo].[Split] 
       (  @Delimiter varchar(5), 
          @List      varchar(8000)
       ) 
       RETURNS @TableOfValues table 
          (  RowID   smallint IDENTITY(1,1), 
             [Value] varchar(50) 
          ) 
    AS 
       BEGIN
          DECLARE @LenString int 
          WHILE len( @List ) > 0 
             BEGIN 
                SELECT @LenString = 
                   (CASE charindex( @Delimiter, @List ) 
                       WHEN 0 THEN len( @List ) 
                       ELSE ( charindex( @Delimiter, @List ) -1 )
                    END
                   ) 
                INSERT INTO @TableOfValues 
                   SELECT substring( @List, 1, @LenString )
                SELECT @List = 
                   (CASE ( len( @List ) - @LenString ) 
                       WHEN 0 THEN '' 
                       ELSE right( @List, len( @List ) - @LenString - 1 ) 
                    END
                   ) 
             END
          RETURN 
       END
    I am about to go crazy trying to figure this out ... I hope some SQL genius can point out the error of my ways.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your example delimeter has a length of two, your function code assumes a length of 1 instead of measuring the length of the Delimiter. Bad things will happen!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2009
    Posts
    33
    That is indeed an issue that I had not noticed, however, I am 100% positive the length of the delimiter being passed is only 1 char.

    The way I tested the function was to step through the code in the ASP project and when it got to the function to read from the database, I copied the value of the parameter to the clipboard. I then ran the stored procedure using the values copied right from the running application. The procedure in the database produced correct results. Then when I continued the debug process and allowed the application to call the stored procedure, it produced only one record.

    This is quite frustrating.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Convert the value on your clipboard to hex using CAST ('sample value' AS VARBINARY(1000)) and check the hex values.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I suspect something more mundane is at work here. Have you looked at the command in Profiler, to see that the whole command is being sent to the server? Perhaps the second parameter is not defined as SqlVarchar with a length of 8000 characters in the SqlParameter object?

  6. #6
    Join Date
    Feb 2009
    Posts
    33
    Ah .. something is amis ... apparently, despite the fact that the ASP project is passing a varchar(8000), the stored procedure is interpreting it as a char(8000) even though it is defined as a varchar.

    I managed to setup debugging of the stored procedure and found that the function adds a really long string of white characters to the end of the string. Thus, when [right( @List, len( @List ) - @LenString - 1 )] is fired, the resulting string is all whitespace.

    I don't understand why [len(@List)] returns the correct string length, but the length of the string after the whitespace is added is something much longer.

    Anyway, I've simply added the following to ensure there is no errant whitespace.
    Code:
    SET @List = RTRIM(@List)
    This resolves my problem, but I still don't understand the underlying issue of why it adds whitespace at the end of the string.

    Anyway, thanks for the help ...

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Char is a fixed length datatype. If you don't supply enough characters to fill it out, the system fills it out with spaces for you. Assigning a char to a varchar variable does not strip off the spaces:
    Code:
    declare @var1 char(20)
    declare @var2 varchar(50)
    
    set @var1 = 'a'
    
    select @var1 + 'a'
    
    select @var2 = @var1 + 'a'
    
    select @var2
    (at least with the default settings)
    Last edited by MCrowley; 10-23-12 at 10:32. Reason: Add disclaimer

  8. #8
    Join Date
    Feb 2009
    Posts
    33
    Well whadda ya know .. after a check of the application code, I found that it is being cast into SqlDbType.Char, 8000 ... changing it to the "correct" type of VarChar did the trick.

    The funny thing is, I knew that char was fixed length, but it just didn't jump out at me.

    I'll leave the trim in place anyway because I can't be sure that other strings being passed won't have the same issue ... at least until I have time to go through the thousands of lines of code and verify that every cast to Char should be Char and not VarChar ...

    I've learned a very valuable lesson that I shall not forget.

    Thanks again for the help ... this myth is busted!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •