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 |
|
Huligan
Yak Posting Veteran
66 Posts |
 Posted - 2004-12-15 : 16:11:17
|
| Hello. I have a dynamic stored procedure where the user can create their own WHERE statement through an ASP page. The WHERE statement they create is brought in as nvarchar(4000). Now I'm running into the issue where users are creating where statements that are longer than 4000 characters.I tried changing it to ntext and SQL tells me it won't allow that as a local variable and I think it would be best not to use that one if I can help it. I tried using varchar(8000) and that doesn't work. I think it's because the SELECT statement has characters that don't play well with the varchar data type (maybe there's some uni-code in there). Here's how I set up my SELECT statement.-- Create a variable @SQLStatementDECLARE @SQLStatement nvarchar(4000) -- Enter the dynamic SQL statement into the variable @SQLStatementSELECT @SQLStatement = N'SELECT...Basically, I would like to know what you would recommend for a large data type that could hold significantly more than 4000 characters. Is varchar what I should use? Am I just screwing something up when I try to implement it? Thanks for your input.Huligan |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-15 : 16:16:31
|
| Wow...pretty scary....There's also an upper limit on the number of bytes you can supply anyway....Brett8-) |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-15 : 22:04:09
|
there are a lot of risks and performance issues by allowing your users to dynamically supply the where clause of your query.will it not be possible to assign variable and check for that?where field=@var?else, if you really need dsql, you can pass the where clause as ntext in your sproc. you can't declare it locally but you can declare it as a parameter  --------------------keeping it simple... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-15 : 22:27:41
|
| See the warning inhttp://www.nigelrivett.net/SQLTsql/TableNameAsVariable.htmlIt applies to your situation as well.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-12-15 : 22:43:38
|
What about running the profiler and determining common tables that are being used. Mabe if you limited them to using some custom views that handled many of the table joins and common return fields you could trim this down.Are you sure your not getting some Yahoo that select every item from a list box of 1000 items and that is getting executed in your where clause? So you end up with where mycolumn in(1,2,3,4,5,6,7,8,9,........) and instead of integers you are looking at the descriptive values of a foreign key table???I would run some traces and see if you can clean up the way the interface builds the queries.Good luck! |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2004-12-16 : 11:03:11
|
| Hello. First, I am limiting the number of items they can include in the query. They can't select every item in the database and kill the server. Second, I am error handling what they are requesting so I'm not concerned about getting malicious requests. Their selections go through a filter first before they are used to build a query. The system is being used to compare the user's item to other items in the database. It's building a comparison. The reason the WHERE statement is getting so long is because of all the combinations one item can have. Here's an example, the user can compare their home to other homes. So the WHERE statement looks something like this:(HouseType = '2-story') AND (Exterior = 'Brick') AND (Year = '1999') AND (Garage = 'Y') OR...That is a simple example of what the WHERE statement looks like for just one item. I'm allowing them to select a couple to compare against. All of the combinations available for an item are creating a very long WHERE statement that is exceeding 4000 characters in some situations.Thanks again for your help.Huligan |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-16 : 12:37:32
|
| you can put entries for each home to find in a temp table, and then join your data to the temp table, on the columns selected. and, again, that way you don't need dynamic sql.also -- instead of bulding a 8000 character SQL statement, if you feel you must dynamically build a WHERE clause, create a View (w/o any criteria at all) and then select from the View using your criteria.i.e., instead of SELECT ...INNER JOIN ...INNER JOIN ...INNER JOIN ...... etc ... (lots of stuff here)....WHERE (your dynamic WHERE here)you would write:SELECT * FROM SomeViewWHERE (your dynamic WHERE here)- Jeff |
|
|
|
|
|
|
|
|
Dynamic storedproc: Need advice on variable limit