Work around for maximum size of Stored Procedure Parameter (8000 chars)

I'm not sure, but for what i understand 8000 is the maximum size for a parameter.

My problem is, how do i pass a string that has more than 8000 chars?


Here's my stored procedure:
=======================================

CREATE PROCEDURE Get_QMake
     @vChoose varchar(8000)
 AS

BEGIN
     SET NOCOUNT ON
     
     DECLARE @strSQL varchar(8000)

     --set query string
     SET @strSQL = "SELECT make FROM used_cars  "
     SET @strSQL = @strSQL  + @vChoose
     SET @strSQL = @strSQL + "  GROUP BY make ORDER BY make "

     EXEC(@strSQL)

     SET NOCOUNT OFF
END
GO

I know that by breaking the strSQL i'm loosing some performance but in this case, it still much faster than running this process in a asp page.


and here's part of the string i need to pass:
=======================================
string = "WHERE ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00323')) OR ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00688')) OR ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00636')) OR ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00696')) OR ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00701')) OR ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '00716')) OR ........... and it goes on ............. ((NAL_REGION = 'PENN') AND (NAL_DLR_NO = '01154'))"

The lenght of this string is 8198.
I'm using SQL2000.



Any help would be appreciated.
Thanks.




LVL 10
DxpertAsked:
Who is Participating?
 
geteConnect With a Mentor Commented:
I don't know about the performance issue, but how about:
string = "WHERE NAL_REGION+NAL_DLR_NO IN ('PENN00323', 'PENN00688', 'PENN0636', 'PENN0696',...and so on...)"

You can easily modify the string concatenation code, and the string size will be significantly smaller.
0
 
John844Commented:
have you tried the text datatype?
0
 
DxpertAuthor Commented:
How do you use the text datatype in a procedure?
When i try it, i get an error.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
John844Commented:
try something like this

    DECLARE @strSQLPrefix varchar(500)
    DECLARE @strSQLSuffix varchar(500)

    --set query string
    SET @strSQLPrefix = "SELECT make FROM used_cars  "
    SET @strSQLSuffix = "  GROUP BY make ORDER BY make "

    EXEC(@strSQLPrefix + @vChoose + @strSQLSuffix)

don't recall if you can do this in EXEC statement, but give it a shot
0
 
John844Commented:
or modify your where string like

string = "WHERE (NAL_REGION = 'PENN' AND ((NAL_DLR_NO = '00323') OR (NAL_DLR_NO = '00688') OR (NAL_DLR_NO = '00636') OR (NAL_DLR_NO = '00696') OR (NAL_DLR_NO = '00701') OR (NAL_DLR_NO = '00716')) OR ...........  )"

or

string = "WHERE (NAL_REGION = 'PENN' AND NAL_DLR_NO IN ('00323','00688','00636','00696','00701','00716'.......  )"


the where will be faster than the in, but in is easier to work with and build...
0
 
DxpertAuthor Commented:
I'm giving you the points, 'cause your sample helped me to reduce the size of the string.

John844, i will be giving you points too, i'll post a question.
0
All Courses

From novice to tech pro — start learning today.