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 |
|
rternier
Starting Member
44 Posts |
 Posted - 2007-08-23 : 12:34:53
|
| I've built a very strong form engine for one of our web apps. It's more featuristic that the TFS Work ITem Tracking.. and more complicated. In order to get the complexity It requires over 50 tables to support the 20+ fields I've created for the users. The problem I have now is doing REports and Filtering on these tables.Like TFS, it has querying... but I have a problem with storing the query. I have two columns right now:ItemQueryTaskQuery.The TaskQuery unions the task query and the ItemQuery and I can do sorting etc. on that. Without any constraints on the filter (say they jsut want to select column headers) the query can exceed 10,000 characters.If someone wanted to have a constraint:I want to check if the CascadingDropDown "Strategic Plan" was ever "Maximize Potential" (or some other management term like that). That constraint sub-query is 500 characters.Meaning, if someone put 20 constraints on a filter like that, I already have 10,000 characters.Average Query without constraints:-I have my ItemQuery which contains the "columns" Query: 6,000 characters.-I have my TaskQuery which contains the "Columns" Query: 6,000 characters.Which turns into:SET @ItemQuery = (SELECT ...)SET @TaskQuery = (SELECT ...)EXEC(@ItemQuery + ' ' + @TaskQuery)But now that I add constraints to those, I'm hooped.I can create another column: ItemConstraint and TaskConstraint... but what if users create massive constraints and the query exceeds 8,000 characters, the Max a VARCHAR can hold...I can't use TEXT as you can't create local variables of type TEXT in a stored procedure (DECLARE @Sample TEXT)... so I'm forced to use VARCHAR(8000).Any thoughts? We use SQL 2005. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-23 : 13:00:35
|
SQL Server 2005, has the VARCHAR(MAX) datatype, which can hold 2147483647 characters.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
rternier
Starting Member
44 Posts |
Posted - 2007-08-23 : 13:05:18
|
| really? Ashamed to say it but I assumed the VARCHAR(MAX) field used only as much as was needed... well that solves that :P |
|
|
|
|
|
|
|
|
Help saving a very long query to DB