Revisions to Parameterize an SQL IN clause

deleted 24 characters in body

Source Link

edited Dec 21, 2014 at 14:37

94.1k
21
144
179

We have function that creates a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table][Fn_sqllist_to_table](@list as varcharAS VARCHAR(8000),
                                           @delim asAS varcharVARCHAR(10))
RETURNS @listTable tableTABLE(
  Position intINT,
  Value varchar   VARCHAR(8000)
  )
AS
  BEGIN
    declare  DECLARE @myPos intINT

  set    SET @myPos = 1

  while charindex   WHILE Charindex(@delim, @list) > 0
  begin      BEGIN
    insert into       INSERT INTO @listTable
                        (Position,Value)
    values        VALUES     (@myPos, leftLEFT(@list, charindexCharindex(@delim, @list) - 1))

    set        SET @myPos = @myPos + 1 

    if charindex       IF Charindex(@delim, @list) = lenLen(@list)
      insert into       INSERT INTO @listTable
                          (Position, Value)
      values        VALUES     (@myPos, '') 

    set        SET @list = rightRIGHT(@list, lenLen(@list) - charindexCharindex(@delim, @list))
  end      END

  if len   IF Len(@list) > 0
    insert into   INSERT INTO @listTable
                    (Position, Value)
    values    VALUES     (@myPos, @list)

Return      RETURN
  END

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

We have function that creates a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

We have function that creates a table variable that you can join to:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1 

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'') 

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

added 1 characters in body

Source Link

edited Dec 4, 2008 at 1:15

David Robbins

10.1k
7
55
83

We have function that createcreates a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

We have function that create a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

We have function that creates a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

Source Link

answered Dec 3, 2008 at 17:11

David Robbins

10.1k
7
55
83

We have function that create a table variable that you can join to:

ALTER     FUNCTION [dbo].[fn_sqllist_to_table](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
  Position int,
  Value varchar(8000)
  )
AS
BEGIN
    declare @myPos int
  set @myPos = 1

  while charindex(@delim, @list) > 0
  begin
    insert into @listTable(Position,Value)
    values(@myPos, left(@list, charindex(@delim, @list) - 1))

    set @myPos = @myPos + 1
    if charindex(@delim, @list) = len(@list)
      insert into @listTable(Position, Value)
      values(@myPos, '')
    set @list = right(@list, len(@list) - charindex(@delim, @list))
  end

  if len(@list) > 0
    insert into @listTable(Position, Value)
    values(@myPos, @list)

Return

So:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

Collectives™ on Stack Overflow

Return to Answer

Post Timeline