Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2001-07-06
6
Medium Priority
?
266 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Accepted Solution

by:
gete earned 400 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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/…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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