Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
billsox
Yak Posting Veteran
74 Posts |
 Posted - 2003-07-14 : 16:08:07
|
Hi -- I'm writing a stored procedure that dynamically creates and executes a SQL statement. Everything works fine if my resulting statement contains 8,000 characters or less. More than that and I get an error. I am aware that you can chunk a SQL statement into several varchar variables and execute as such: EXECUTE (@SQL1 + @SQL2 + @SQL3) (As I'm previewing this message, the 2 plus signs between the 3 variables in the above statement are not visible. No idea why not.)But the above code would only work if my SQL statement had less than 24,000 characters (i.e. 8000 chars * 3 varchar variables). The problem is that I don't know at design time how many variables I need to store the statement. I guess I could create a lot of varchar variables and hope that no statement ever exceeds the limit but I don't like solutions like that. Is there a more elegant way to accomplish this type of statement storage/execution in a single stored procedure?Any help is any appreciated. Thanks.Bill |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-14 : 16:21:03
|
Well, BOL say 4000:quote:
@string_variableIs the name of a local variable. @string_variable can be of char, varchar, nchar, or nvarchar data type with a maximum value of the server's available memory. If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string. For more information about system-supplied SQL Server data types, see Data Types.
That's a pretty large string though. What does one look like?I would imagine performance would suffer...Brett8-) |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-14 : 16:21:11
|
| Generally the thought of elegance disappears when dealing with dynamic sql. It's like lipstick on a pig.Tell us about the business problem and what influenced your choice to use the dynamic approach.Jonathan{0} |
|
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 2003-07-14 : 16:35:01
|
| The statements that I generate are typically crosstab query statements that pivot on a column with many values (e.g., a date field with 200 or so distinct dates). This results in very large SQL statements with lots of CASE clauses.Bill |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-14 : 16:54:15
|
| It's tough to visualize, but I'd bet anything you could use a temp table...And why the CASE statements?Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-14 : 17:06:54
|
quote:
And why the CASE statements?
that's how you make crosstabs.billsox -- how are you presenting this data? in a report? on a web page? does the presentation layer allow for cross-tabs or pivoting? usually, that layer is much more efficient as doing things of that nature than SQL Server.- Jeff |
|
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 2003-07-14 : 17:41:06
|
quote:
billsox -- how are you presenting this data? in a report? on a web page? does the presentation layer allow for cross-tabs or pivoting? usually, that layer is much more efficient as doing things of that nature than SQL Server.- Jeff
Jeff -- Great point. My sproc's destination is Excel which I realize has pivoting abilities. But the sad fact is that my user base is either brain-dead or lazy (or both).  I will consider using Excel more for these types of presentation issues. Thanks for the advice.Bill |
|
|
|
|
|
|
|
|
Dynamic statement execution in sproc