Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Unanswered: Pass semi colon delimited parameter to stored procedure

    Hell All,
    I am facing a bit of challenge:
    I have a table with the following structure:
    Code:
     [tabid] [int] IDENTITY(1,1) NOT NULL,
    	[tabname] [nvarchar](50) NULL,
    	[description] [nvarchar](50) NULL,
    	[url] [nvarchar](50) NULL,
    	[parent] [int] NULL,
    	[roles] [nvarchar](50) NULL,
    	[tabposition] [int] NULL,
    	[tabstatus] [int] NULL,
    Some sample data is the following:
    1 Home Dashboard en.aspx/portalmodules/dashboard NULL 1;6; 1 1
    2 Maintenance Maintenance en.aspx/maintenance/maintenance NULL 1; 7 1
    126 Site Site enbank.aspx/site NULL 11; 2 1

    I wrote a stored procedure that takes a string of values, seperated by semicolon as parameter. The procedure is below;
    Code:
    ALTER PROCEDURE [dbo].[selectUserTabsByRoles] 
    	@var varchar(max)
    AS
    BEGIN
    	SELECT distinct * from tbl_tabs
    	where ( PATINDEX('%'+left(@var,1)+'%', roles) > 0
    	or PATINDEX('%'+right(@var,1)+'%', roles) > 0 ) AND parent is null and tabstatus =1
    	ORDER BY tabposition
    	
    END
    My problem is, when I pass a parameter like 1; it fetches all rows with roles having 1. But I realised that the last row in the sample data does not have 1 as roles, but rather 11.
    I urgently need help on this.
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you need fn_split.

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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Where's this colon-separated list of values coming from? If the format consistent (i.e. same number of parts in the same order, etc).
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    PATINDEX('%;' + @var + ';%' , ';' + roles) > 0
    Last edited by tonkuma; 09-16-13 at 10:34. Reason: Replace roles and @var.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Should that be
    Code:
    PATINDEX('%;' + @var + ';%' , ';' + roles + ';') > 0
    ??
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The last character of roles were semicolon, like
    1;6;
    1;
    11;

Posting Permissions

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