[dba-SQLServer]Creating Sproc over 8000 chars long

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