|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to pass in a long string to stored procedureUsing SS2000 SP4.
We have inherited an application that uses dynamic sql in almost all of the stored procedures. Most of them specify a limit of 8000 characters for the sql variable. The client wants to be able to pass in a list of all of their reps or all of their franchises. It is possible that the list could exceed the 8000 character limit. Can the 8000 limit be increased somehow or is there another way to accomplish the same thing? Thanks, -- Dan D. You can't increase the 8000 limit for varchar fields. What you could
do, however, is have multiple fields and have the front-end application split the larger string into 2 or more smaller strings. Within the stored procedure, you could insert those values into a temporary table. There's probably a better way to accomplish what they are looking for than passing in 8000-char lists of items. Johnny Thawte http://www.johnnysthoughts.com Dan D. wrote: Show quote > Using SS2000 SP4. > > We have inherited an application that uses dynamic sql in almost all of the > stored procedures. Most of them specify a limit of 8000 characters for the > sql variable. > > The client wants to be able to pass in a list of all of their reps or all of > their franchises. It is possible that the list could exceed the 8000 > character limit. Can the 8000 limit be increased somehow or is there another > way to accomplish the same thing? > > Thanks, > -- > Dan D. Is the 8000 char limit on what is passed in to the sp or is it a limit on how
long the string that I execute can be (i.e. EXEC(@SQL))? Thanks for the suggestion. -- Show quoteDan D. "Johnny Thawte" wrote: > You can't increase the 8000 limit for varchar fields. What you could > do, however, is have multiple fields and have the front-end application > split the larger string into 2 or more smaller strings. > > Within the stored procedure, you could insert those values into a > temporary table. > > There's probably a better way to accomplish what they are looking for > than passing in 8000-char lists of items. > > Johnny Thawte > http://www.johnnysthoughts.com > > > Dan D. wrote: > > Using SS2000 SP4. > > > > We have inherited an application that uses dynamic sql in almost all of the > > stored procedures. Most of them specify a limit of 8000 characters for the > > sql variable. > > > > The client wants to be able to pass in a list of all of their reps or all of > > their franchises. It is possible that the list could exceed the 8000 > > character limit. Can the 8000 limit be increased somehow or is there another > > way to accomplish the same thing? > > > > Thanks, > > -- > > Dan D. > > 8,000 bytes is the size limit on regular (small) character/binary data
types. That works out to 8,000 characters for CHAR and VARCHAR, 8,000 bytes for BINARY and VARBINARY, and 4,000 characters for NCHAR and NVARCHAR. I believe you can get around the dynamic SQL limit by concatenating multiple variables together: EXEC (@SQL1 + @SQL2 + ...) That's an awfully large SQL statement to execute dynamically though... Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message news:3BCFC075-E4F3-4271-BC76-4ECA6D821869@microsoft.com... > Is the 8000 char limit on what is passed in to the sp or is it a limit on > how > long the string that I execute can be (i.e. EXEC(@SQL))? > > Thanks for the suggestion. > -- > Dan D. > > > "Johnny Thawte" wrote: > >> You can't increase the 8000 limit for varchar fields. What you could >> do, however, is have multiple fields and have the front-end application >> split the larger string into 2 or more smaller strings. >> >> Within the stored procedure, you could insert those values into a >> temporary table. >> >> There's probably a better way to accomplish what they are looking for >> than passing in 8000-char lists of items. >> >> Johnny Thawte >> http://www.johnnysthoughts.com >> >> >> Dan D. wrote: >> > Using SS2000 SP4. >> > >> > We have inherited an application that uses dynamic sql in almost all of >> > the >> > stored procedures. Most of them specify a limit of 8000 characters for >> > the >> > sql variable. >> > >> > The client wants to be able to pass in a list of all of their reps or >> > all of >> > their franchises. It is possible that the list could exceed the 8000 >> > character limit. Can the 8000 limit be increased somehow or is there >> > another >> > way to accomplish the same thing? >> > >> > Thanks, >> > -- >> > Dan D. >> >> > Is the 8000 char limit on what is passed in to the sp or is it a limit on Neither. You can pass TEXT in to the parameter, which doesn't have an 8000 > how > long the string that I execute can be (i.e. EXEC(@SQL))? character limit. It will just be much harder to execute it because you can't EXEC(@TextParameter) and you can't manipulate the local variable directly. And as far as EXEC goes, you can do this: EXEC(@vc_8000_1 + @vc_8000_2 + @vc_8000_3); A > The client wants to be able to pass in a list of all of their reps or all Perhaps Erland's site will give you some ideas> of > their franchises. It is possible that the list could exceed the 8000 > character limit. Can the 8000 limit be increased somehow or is there > another > way to accomplish the same thing? http://www.sommarskog.se/ Thanks for the link.
-- Show quoteDan D. "Scott Morris" wrote: > > The client wants to be able to pass in a list of all of their reps or all > > of > > their franchises. It is possible that the list could exceed the 8000 > > character limit. Can the 8000 limit be increased somehow or is there > > another > > way to accomplish the same thing? > > Perhaps Erland's site will give you some ideas > http://www.sommarskog.se/ > > > You can pass a TEXT parameter, formatted as a comma-delimited (or other
delimiter) list, to a stored procedure. You can split the TEXT parameter along the delimiter and put the results into a temp table or table variable. Here's an example: http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/03/09/529.aspx Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message news:834B9C6E-B439-46D8-9731-5DD488BC4D23@microsoft.com... > Using SS2000 SP4. > > We have inherited an application that uses dynamic sql in almost all of > the > stored procedures. Most of them specify a limit of 8000 characters for the > sql variable. > > The client wants to be able to pass in a list of all of their reps or all > of > their franchises. It is possible that the list could exceed the 8000 > character limit. Can the 8000 limit be increased somehow or is there > another > way to accomplish the same thing? > > Thanks, > -- > Dan D. I'll read through that article. Thanks,
-- Show quoteDan D. "Mike C#" wrote: > You can pass a TEXT parameter, formatted as a comma-delimited (or other > delimiter) list, to a stored procedure. You can split the TEXT parameter > along the delimiter and put the results into a temp table or table variable. > Here's an example: > http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2006/03/09/529.aspx > > > "Dan D." <D***@discussions.microsoft.com> wrote in message > news:834B9C6E-B439-46D8-9731-5DD488BC4D23@microsoft.com... > > Using SS2000 SP4. > > > > We have inherited an application that uses dynamic sql in almost all of > > the > > stored procedures. Most of them specify a limit of 8000 characters for the > > sql variable. > > > > The client wants to be able to pass in a list of all of their reps or all > > of > > their franchises. It is possible that the list could exceed the 8000 > > character limit. Can the 8000 limit be increased somehow or is there > > another > > way to accomplish the same thing? > > > > Thanks, > > -- > > Dan D. > > > - you could split the list into chunks and pass in multiple VARCHAR(8000)
parameters. - you could set a reasonable limit on - you could pass in a TEXT parameter, jam it into a #temp table, and parse it using a loop, patindex, etc. Not for the weak and definitely not something that will be coded and bulletproven very quickly. - you could upgrade to SQL Server 2005 and take advantage of VARCHAR(MAX) which has very few of the limitations of either regular varchar() or text. A Show quote "Dan D." <D***@discussions.microsoft.com> wrote in message news:834B9C6E-B439-46D8-9731-5DD488BC4D23@microsoft.com... > Using SS2000 SP4. > > We have inherited an application that uses dynamic sql in almost all of > the > stored procedures. Most of them specify a limit of 8000 characters for the > sql variable. > > The client wants to be able to pass in a list of all of their reps or all > of > their franchises. It is possible that the list could exceed the 8000 > character limit. Can the 8000 limit be increased somehow or is there > another > way to accomplish the same thing? > > Thanks, > -- > Dan D. > - you could set a reasonable limit on *poof* don't know where the rest of that paragraph went. But this gives me a chance to elaborate a bit. - you could set a reasonable limit on the number of individual items they can choose. If appropriate, when they get above a certain limit, make them choose all. I don't know if this latter part is possible because I have no idea if these strings can be represented in different ways in the database. Are they picking from a list of known entities, or are they typing freetext? If the former, then the strings can be represented by surrogate key values. If you assign a unique integer to each string, and pass that list to the stored procedure instead of the list of strings, you can make this call: EXEC dbo.MyProc @myList = 'englebert humperdink, barry manilow, barry white, weird al yankovic, buddy holly'; Look like this call: EXEC dbo.MyProc @myList = '3, 225, 7, 82, 36'; A I'm not sure we're going to be able force them to take the entire list when
they get above a certain amount. I'll give all of these some thought. Thanks for the suggestions. -- Show quoteDan D. "Aaron Bertrand [SQL Server MVP]" wrote: > > - you could set a reasonable limit on > > *poof* don't know where the rest of that paragraph went. But this gives me > a chance to elaborate a bit. > > - you could set a reasonable limit on the number of individual items they > can choose. If appropriate, when they get above a certain limit, make them > choose all. I don't know if this latter part is possible because I have no > idea if these strings can be represented in different ways in the database. > Are they picking from a list of known entities, or are they typing freetext? > If the former, then the strings can be represented by surrogate key values. > If you assign a unique integer to each string, and pass that list to the > stored procedure instead of the list of strings, you can make this call: > > EXEC dbo.MyProc @myList = 'englebert humperdink, barry manilow, barry white, > weird al yankovic, buddy holly'; > > Look like this call: > > EXEC dbo.MyProc @myList = '3, 225, 7, 82, 36'; > > A > > > > character limit. Can the 8000 limit be increased somehow or is there another Dan,> way to accomplish the same thing? You might want to read Erland Sommarskog's article "Arrays and Lists in SQL Server". One more thing: if you need to pass a list of numbers, you can pack
then in binary format into an image. I think it is fast to transfer (less bytes) and simple to both pack on the client and parse on the server. That's a novel idea. Thanks.
-- Show quoteDan D. "Alexander Kuznetsov" wrote: > One more thing: if you need to pass a list of numbers, you can pack > then in binary format into an image. I think it is fast to transfer > (less bytes) and simple to both pack on the client and parse on the > server. > >  |
|||||||||||||||||||||||
Other interesting topics