CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user942514
user942514

Reputation:

SQL Server: Function is returning single char in stored procedure

I'm using SQL Server 2008

Here is function (splitting string and returning int values)

USE [SANNET]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata int 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(CAST(SUBSTRING(@string, @start, @end - @start)AS  int))
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END
GO

Everything is ok when using this way

select * from fnSplitString('65,1,2,27,28,33,34',',')
/**
Returning
splitdata
-----------
65
1
2
27
28
33
34

(7 row(s) affected)


*/

But when i use in stored procedure is returning a single char (not even a row)

-- Procedure which calling the function


CREATE /*ALTER*/ PROCEDURE [dbo].[CallerProcedure] 
    @values varchar
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
     SET NOCOUNT ON;
    select * from fnSplitString(@values,',');

END
GO

Usage 

DECLARE @result int

EXEC    @result = [dbo].[CallerProcedure]
        @values = '65,1,2,27,28,33,34'

SELECT @result

GO
/*
Returns
splitdata
-----------
6


-----------
0

(1 row(s) affected)

*/

As you see even value "64" is not returned. Just returnet 6.

Where am I doing wrong?

Thanks

Upvotes: 2

Views: 2270

Answers (2)

GarethD
GarethD

Reputation: 69789

You have not defined a length for your VARCHAR input variable @values, so it is defaulting to VARCHAR(1):

CREATE /*ALTER*/ PROCEDURE [dbo].[CallerProcedure] 
    @values varchar

So although you think you are passing '65,1,2,27,28,33,34', this is truncated to just '6'

Changing the declaration of @Values to VARCHAR(MAX) will solve your problem.

CREATE /*ALTER*/ PROCEDURE [dbo].[CallerProcedure] 
    @values VARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
     SET NOCOUNT ON;
    select * from fnSplitString(@values,',');

END
GO

Example on SQL Fiddle

You should ALWAYS define a length when converting to, or declaring a VARCHAR. For further reading:

Bad habits to kick : declaring VARCHAR without (length)

Upvotes: 4

user275683
user275683

Reputation:

Modify your procedure as follows. First insert results into a variable table and than return it. Also don't forget to set size for VARCHAR fields. 

CREATE PROCEDURE [dbo].[CallerProcedure] @values VARCHAR(MAX)
AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @MyTable AS TABLE
        (
            value VARCHAR(20)
        )

        INSERT INTO @MyTable
        SELECT *
            FROM fnSplitString(@values, ',');

        SELECT *
        FROM @MyTable
    END
GO

Upvotes: 1

Related Questions