Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2001-07-06
6
Medium Priority
?
269 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
  • 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
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.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
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…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

595 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