Solved

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

Posted on 2001-07-06
6
264 Views
Last Modified: 2010-05-18
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.




0
Comment
Question by:Dxpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:John844
ID: 6260175
have you tried the text datatype?
0
 
LVL 10

Author Comment

by:Dxpert
ID: 6260183
How do you use the text datatype in a procedure?
When i try it, i get an error.
0
 
LVL 7

Expert Comment

by:John844
ID: 6260188
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 6

Accepted Solution

by:
gete earned 100 total points
ID: 6260206
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
 
LVL 7

Expert Comment

by:John844
ID: 6260216
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
 
LVL 10

Author Comment

by:Dxpert
ID: 6260542
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question