[dba-SQLServer]Creating Sproc over 8000 chars long

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