Reputation: 21

Function Split in SQL Server causing error

I want to query a comma-separated list of values. But I get an error:

SELECT
    nCmpID, cCompanyName  
FROM 
    (SELECT * 
     FROM tbl_CompanyMaster  
     WHERE nCmpID IN (SELECT * 
                      FROM dbo.fnsplit((SELECT can_AccessCompanyID 
                                        FROM tbl_UserMenuRelations 
                                        WHERE nUserID = 0 
                                          AND Is_Active = 1 
                                          AND Is_Available = 1), ',') a) 
       AND Is_Active = 1) t

My function FNSplit:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnSplit]
    (@sInputList VARCHAR(8000), -- List of delimited items
     @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) 
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
    DECLARE @sItem VARCHAR(8000)

    WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
    BEGIN
        SELECT
            @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
            @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter), LEN(@sInputList))))

        IF LEN(@sItem) > 0
           INSERT INTO @List 
               SELECT @sItem
        END

        IF LEN(@sInputList) > 0
           INSERT INTO @List 
               SELECT @sInputList -- Put the last item in

        RETURN
END

I get these errors:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

Upvotes: 0

Answers (4)

SqlZim

Reputation: 38043

Do not reinvent the wheel if you do not have to.

Example CSV string splitter table-valued function by Jeff Moden:

create function [dbo].[delimitedsplit8K] (
      @pstring varchar(8000)
    , @pdelimiter char(1)
  )
returns table with schemabinding as
 return
  with e1(N) as (
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all select 1
  )
  , e2(N) as (select 1 from e1 a, e1 b)
  , e4(N) as (select 1 from e2 a, e2 b)
  , ctetally(N) as (
    select top (isnull(datalength(@pstring),0)) 
      row_number() over (order by (select null)) from e4
  )
  , ctestart(N1) as (
    select 1 union all
    select t.N+1 from ctetally t where substring(@pstring,t.N,1) = @pdelimiter
  )
  , ctelen(N1,L1) as (
    select s.N1,
      isnull(nullif(charindex(@pdelimiter,@pstring,s.N1),0)-s.N1,8000)
    from ctestart s
  )
 select itemnumber = row_number() over(order by l.N1)
      , item       = substring(@pstring, l.N1, l.L1)
   from ctelen l
;
go

And use like so:

select cm.nCmpID, cm.cCompanyName 
from tbl_CompanyMaster cm
where cm.Is_Active = 1 
  and cm.nCmpId in (
    select s.Item
    from tbl_UserMenuRelations umr
      cross apply dbo.delimitedsplit8K(umr.can_AccessCompanyId,',') s
    where umr.nUserId = 0 
      and umr.Is_Active = 1 
      and umr.Is_Available = 1
    )

splitting strings reference:

Upvotes: 0

pankaj kumar singh

Reputation: 17

your syntax is wrong where you are using user defined function dbo.fnsplit.

You should use cursor for rows by rows execution.

DECLARE @name VARCHAR(100)

DECLARE record CURSOR FOR SELECT name FROM emp OPEN record

FETCH NEXT FROM record INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN select * from dbo.fnsplit(@name,',') FETCH NEXT FROM record INTO @name END CLOSE record
DEALLOCATE record

Upvotes: 0

Rohit Kumar

Reputation: 806

Answer

Will require making use if dynamic Query

Declare @Query varchar(8000)

Declare @CommaSeparatedList varchar(8000)

-- Use a string variable to store the result of the split function.

Set @CommaSeparatedList =(SELECT * 
                  FROM dbo.fnsplit((SELECT can_AccessCompanyID 
                                    FROM tbl_UserMenuRelations 
                                    WHERE nUserID = 0 
                                      AND Is_Active = 1 
                                      AND Is_Available = 1), ','))

SET @Query='SELECT
nCmpID, cCompanyName  
FROM 
(SELECT * 
 FROM tbl_CompanyMaster  
 WHERE nCmpID IN ('+@CommaSeparatedList+') a) 
   AND Is_Active = 1) t' 

 Exec(@Query)

Upvotes: 0

Sam

Reputation: 910

try this

SELECT
    nCmpID, cCompanyName  
FROM 
    (SELECT * 
     FROM tbl_CompanyMaster  
     WHERE nCmpID IN (SELECT * 
                      FROM dbo.fnsplit((SELECT can_AccessCompanyID 
                                        FROM tbl_UserMenuRelations 
                                        WHERE nUserID = 0 
                                          AND Is_Active = 1 
                                          AND Is_Available = 1), ',')) 
       AND Is_Active = 1) t

there's a useless a) in your code

Upvotes: 1

Function Split in SQL Server causing error

Answers (4)

Related Questions