Solved

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

Posted on 2001-07-06
6
259 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error viewing ASP page 12 177
Select only the top record in a left join 13 48
ASP/VB email question 4 61
Validating Date 4 28
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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