[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