[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