[dba-SQLServer] Re: How To: Create a Stored Procedure using aparameter for IN

OK, this HAS to work now.  You can JOIN the resulting function call with
any other table where values passed to the function can be found:

use pubs
GO

if object_id('dbo.fn_ParseString2VarCharTable') is not null
   drop function dbo.fn_ParseString2VarCharTable
GO
 
create function dbo.fn_ParseString2VarCharTable (
 @string   varchar(8000)                      ) returns @tbl table (
   RecordID  int identity(1,1) not null primary key clustered,
   [String]  varchar(8000)   not null           ) as
   begin
      declare @spos int, @pos int
      set @string = replace(replace(@string, ', ', ','), ' ,', ',')
      set @spos = 1
      set @pos = 100
      while (@pos) > 0 begin
         set @pos = charindex(',', @string, @spos)
         insert @tbl ([String])
            select
               ltrim (
                  rtrim (
                     substring (
                        @string, @spos,
                           case
                              when (@pos - @spos) <= 0 then
datalength(@string) + 1
                              else @pos
                           end - @spos
                     )
                  )
               )
         set @spos = @pos + 1
      end
      return
   end
GO

declare @str varchar(8000)
set @str = ''
select @str = @str + au_lname + ',' from pubs.dbo.authors
set @str = reverse(substring(reverse(@str), 2, 8000))
select f.RecordID, a.* from pubs.dbo.authors a 
   inner join dbo.fn_ParseString2VarCharTable (@str) f
      on a.au_lname = f.[String]
select * from dbo.fn_ParseString2VarCharTable ('this , is,a , test,and,
as ,you, can , see,it ,works!')


Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert
L. Stewart
Sent: Monday, February 16, 2004 10:32 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Re: How To: Create a Stored Procedure using
aparameter for IN 

Final SQL Stored Procedure:

CREATE PROCEDURE usp_Build_In
      @Select varchar(500),
      @From varchar(150),
      @Where varchar(500),
      @In varchar(500),
      @OrderBy Varchar(250)
AS
BEGIN
      DECLARE @SQL varchar(1900)
      SET @sql = @Select + @From + @Where + @In + @Orderby
      EXEC(@sql)
END

To Use it:

SET QUOTED_IDENTIFIER OFF
GO
exec usp_Build_In 'SELECT ClientID,ResidenceType,LastName ',
                   'FROM tbl_Client ',
                   'WHERE LastName IN(',
                   "'Stewart','Jones')",
                   'ORDER BY LastName'

Notice the Double-quotes on the @In parameter.

Robert, I am still working on the idea of using the subquery.
The code you sent to me only returns one row, the last one in
the list. But I will have more time to work with it tomorrow.

RLS 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com