Dynamic SQL for Search Results Proc

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL for Search Results Proc

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-04-13 : 17:28:14

Ok, here's the low down.

I have an asp.net webform with several dropdowns on it. The user can select values in any of those dropdowns for my search interface. If the user selects nothing in some of the dropdowns, then the value of 0 will be sent to my proc, for those fields. All fields on the webform will be passed to my stored proc obviously and so my proc needs to be able to bring back records matching any scenario...a basic type of thing we see time and time again. Problem is, I want to make sure I do this the best and most efficient and correct way in my stored proc.

While I know Dynamic SQL is not really a good thing (security-wise, yada, yada), I don't think I have a choice in the matter unless in my .NET code, I construct the proc there using a stringbuilder or soemthing...which again, for me, is a terrible way to go at least form my perspective...as the proc is not located in SQL Server anymore, it's located in my code...which I deem as very bad and unmanageable.

So with that, this is what I'm trying in my proc but need some insight or help and to let me know if this is the best approach, and if so, how do I get the CASE to work on that inner join. I only want to join to the Program table, if the user has selected a ProgramID on my Webform, else, do not join to the Program table.

Another note, It's bad practice I think to match PK or FKs using the where clause and you should always do a join on IDs. It's fine to specify all other matches that are not IDs such as First Name for example in the where clause like I did of course.

CREATE PROCEDURE GetTestSearchResults

@FirstName varchar(50),
@LastName varchar(50),
@ProgramID int

AS
BEGIN

SET NOCOUNT ON;

SELECT s.FirstName,
s.LastName
FROM mydb1.dbo.Staff s
INNER JOIN Intranet.dbo.StaffProgram sp ON p.StaffID = sp.StaffID
CASE WHEN @ProgramID <> 0
INNER JOIN mydb2.dbo.Programs p ON p.ProgramID = @ProgramID

WHERE (@FirstName = 0 or s.FirstName = @FirstName)
AND (@LastName = 0 or s.LastName = @LastName)

END
GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-13 : 17:33:43

I dont think what you are doing (joining the table based on a condition) is correct (syntactically). If thats the case you have to do dynamic SQL. Dynamic SQL by itsels is not bad. Its how you use it. Check out: http://www.sommarskog.se/dynamic_sql.html

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-04-13 : 19:46:29

>>>If thats the case you have to do dynamic SQL

correct. You always join using a left join, right join, or inner join. I would never so a hack job and join PK and FK using a Where clause. So with that, yea, that's what I'm having to end up doing, Dynamic SQL, with dynamic joins...and dynamic checks in the where clause for non-id fields.

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-04-13 : 23:30:46

ouch, that link you sent was great...but there is a LOT in that article.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-14 : 00:13:29

since you are building a dynamic search criteria, this other article from erland may be more appropriate:

http://www.sommarskog.se/dyn-search.html

www.elsasoft.org

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-04-14 : 00:32:51