[dba-SQLServer]Creating Sproc over 8000 chars long

Well, since I'm not writing any articles, here's the function that
Arthur may have:

create function dbo.fn_ParseStringToIntValues (
   @str varchar(8000) ) returns @tbl table (IntValue int not null)
as begin
   declare @pos int
   
   while charindex(',', @str, 1) > 0 begin
      set @pos = charindex(',', @str, 1)
      insert @tbl values (cast(substring(@str, 1, @pos-1) as int))
      set @str = substring(@str, @pos+1, datalength(@str)- at pos)
   end
   insert @tbl values (cast(@str as int))
   return
end
go
select * from dbo.fn_ParseStringToIntValues
('123,432,456,4356,43234,1,2,3') order by 1

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Wednesday, September 24, 2003 5:04 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Creating Sproc over 8000 chars long


Thanks Arthur,

I found that I had used a temp table in another (programmed later) part
of 
the FE.  I seemed to have come to the same conclusion but hadn't gone
back 
and updated old code.

David

At 24/09/2003, you wrote:
>I just wrote a UDF that takes a comma delimited string (which would 
>normally be passed as the argument to IN(), but instead it breaks the 
>string apart into its components and creates a temporary table, which 
>can then be joined to the table(s) of interest.
>
>Unfortunately I can't show it to you yet because it's part of one of my

>forthcoming SQL Tips columns :-)
>
>The point of the exercise was, lots of arguments to IN() results in 
>very slow execution. Joining a temp table is WAY faster.
>
>Arthur
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David 
>Emerson
>Sent: Monday, September 22, 2003 6:17 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]Creating Sproc over 8000 chars long
>
>
>Thanks Stuart.  Since the database is likely to start running into 
>several thousand customers it looks like I will need to run the table 
>way.
>
>David
>
>At 23/09/2003, you wrote:
> >On 23 Sep 2003 at 12:16, David Emerson wrote:
> >
> > > I have a sproc that accepts a string as a parameter which is used 
> > > in
>
> > > a where clause to filter out customers.
> > >
> > > The string is created by looping through a list box and adding the

> > > customers that have been selected:
> > >
> > > strList = strList & " or ((CustomerID) = " & .ItemData(varItem) & 
> > > ")"
> > >
> >
> >
> >You will build a much smaller string if you use
> >
> >'initialise the string
> >strList  = "CustomerID IN ("
> >......
> >'build the list
> >strList = strList & ItemData(varItem) & ","
> >........
> >
> >'strip trailing comma and close the expression
> >strList = left$(strList,Len(strList)-1) & ")"
> >
> > > This goes into a statement which starts like this:
> > >
> > > DECLARE @qs varchar (8000)
> > >
> > > SELECT @qs = 'INSERT INTO ttmpExportMerge (CustomerName, 
> > > TradingName,
> > ... etc
> > >
> > > However the problem is that there could be over 1600 customers and

> > > this means that strList is over 8000 character which exceeds the 
> > > @qs
>
> > > length (without even allowing for the rest of the sproc).
> > >
> > > Is there a data type that can handle these numbers?  I tried 
> > > 'text' but
> > the
> > > sproc errors with "text is invalid for local variables"
> > >
> >Nope, your stuck with data types which can have a maximum of 8000 
> >characters.
> >
> >If the IN construct still gives you more than 8000 characters you're 
> >SOOL with that approach.
> >
> > > Is my only option to write the customer ID to a table and join it 
> > > to
>
> > > the rest of the sproc?
> > >
> >
> >That's one option.
> >Another may be to have a "Selected" field in the customer table which

> >you set somehow rather than building the where string. If this is 
> >likely to be multi user, I'd probably go with your option of building

> >a
>
> >temp table of selected IDs and joining it.
> >
> >
> >
> >--
> >Stuart McLachlan
> >Lexacorp Ltd
> >Application Development,  IT Consultancy http://www.lexacorp.com.pg
> >
> >_______________________________________________
> >dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com

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