[dba-SQLServer]Creating Sproc over 8000 chars long

David Emerson davide at dalyn.co.nz
Mon Sep 22 19:16:48 CDT 2003


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) & ")"

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"

Is my only option to write the customer ID to a table and join it to the 
rest of the sproc?

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030923/577945d0/attachment.html>


More information about the dba-SQLServer mailing list