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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2003-09-16 : 14:22:37
|
| I've got a stored procedure that takes about 20 parameters and runs a search based on them. The parameters specify what columns matter in the search, the order to display results, and what columns need to be selected in the results.Right now, it's a massive exercise in dynamic SQL. It's ugly and slow, but functional. I've tried to rewrite it to not use dynamic SQL a few times, and it's always just been an exercise in frustration.People here have helped me a ton, and the scope of this is really more than I'd ask anyone to do just out of charity. So here you go: a bounty. De-dynamic-SQL this and get $100 to buy some good wine for your aching brain, or for whatever other purpose you'd like. I hope it doesn't seem gauche -- I just feel that it's too much work to ask someone to do for free.Here are the guidelines:- The output result needs to stay the same. - The functionality of the input parameters needs to remain the same, but their name and type don't. For instance, right now the "order by" clause is basically being passed in in a varchar. It's fine to change that to a tinyint which specifies the sort- If two or more people collaborate on the solution, the bounty may be split, or donated to SQLteam.com, or whatever, at the peoples' request.I've been told that I can't post the actual SP, table schemas, or supporting functions, since it's proprietary and key to our app. However, if you drop a note to brooks@frnk.com, I'll send you a zip file with all of the needed stuff.Cheers-b |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-16 : 15:15:13
|
| Why not Just doSELECT * FROM myTable99WHERE myCol1 = ISNULL(@myParam1,myCol1)ect...??Don't know if that'll incurr a scan..but if it works for you...send the buck to sqlTeam...I'll check it out further...Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-16 : 16:02:13
|
Well, that'll solve you're dynamic problem..but probably will impact performance...I was suprised to find the dynamic sql performed like the hard coded example..both did index seeksthe other one was scans..cut and paste this and check out the execution plans...but still, i wonder if a compiled index scan (compared to dynamic sql) is better...still a scans a scan...DECLARE @SQL varchar(8000), @orderId int, @ProductId intSELECT @OrderId = 10802, @ProductId = 55SELECT * FROM [Order Details] WHERE OrderId = 10802 AND ProductId = 55SELECT @SQL = 'SELECT * FROM [Order Details] ' + ' WHERE OrderId =' + CONVERT(varchar(5),@OrderId) + ' AND ProductId = ' + + CONVERT(varchar(5),@ProductId)EXEC(@SQL)SELECT * FROM [Order Details] WHERE OrderId = ISNULL(@OrderID,OrderId) AND ProductId = ISNULL(@ProductId,ProductId) Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-09-16 : 19:09:08
|
| Aiken,Several of the projects I have worked on require the same type of search routine.In every case, I have opted for using Analysis Services by way of a ROLAP Cubes..DavidM"SQL-3 is an abomination.." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-17 : 09:59:41
|
It's OLTP actually...I got the code...Now my brain hurts...quote:
I've been told that I can't post the actual SP, table schemas, or supporting functions, since it's proprietary
I don't think you'd have to worry about it...but...I'll keep looking...Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
|
|
|
|
$100 Bounty: Rewrite SP to avoid dynamic SQL