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 |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2009-08-27 : 18:34:28
|
We have a table that contains a field called "AssignmentFilter" which holds a SQL string (e.g. (Extra3 LIKE '%rereee%') AND (Country LIKE '%canada%') AND (Students.DepartmentID = '53'))I need to know how to include that sql in a query. Something to the effect of...select * from Students INNER JOIN Courses on Students.CourseID = Courses.CourseID and <<<Course.AssignmentFilter>>> Is this possible? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2009-08-27 : 18:46:31
|
quote:
Originally posted by tkizer
This is a very bad design. Can your system be redesigned so that code is not stored in the database?To answer your question though, you'll need to use dynamic SQL.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Can't be designed in time to meet my deadline of tomorrow unfortunately but maybe afterwards I can undertake a redesign. I thought this might work but I was wrong...select * from studentsinner join subscriptions on subscriptions.studentsid = students.studentsidinner join courses on subscription.courseid = courses.courseidand (select assignmentfilter from courses as a where a.courseid = courses.courseid) Have to figure out what you mean by "dynamic sql" I guess ;) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-27 : 20:07:10
|
| [code]declare @sql varchar(max)set @sql=''select @sql=@sql + 'select * from Students INNER JOIN Courses on Students.CourseID = Courses.CourseID and ' + Course.AssignmentFilter + '; 'from Students INNER JOIN Courses on Students.CourseID = Courses.CourseIDexec(@sql)[/code]Warning: not tested. Another warning: this assumes the first part of the query is always the same. Additional warning: will fail on invalid SQL syntax in the AssignmentFilter column. You might be better off with:[code]select 'select * from Students INNER JOIN Courses on Students.CourseID = Courses.CourseID and ' + Course.AssignmentFilter + '; 'from Students INNER JOIN Courses on Students.CourseID = Courses.CourseID[/code]Run that query and paste the results into another query window, then you can examine it for syntax errors before running it.Tara is absolutely right about redesigning this, start tomorrow after you deliver these results. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2009-08-27 : 21:40:31
|
Thanks so much for the help folks. In terms of proper design what would you recommend? I've included an image to give you an idea of what we need to store. You can limit a "Learning Path" by an endless number of criteria. So these learning paths will only be viewable by students who meet all of the criteria. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-28 : 06:42:22
|
| There's an interesting approach documented here:http://msdn.microsoft.com/en-us/library/aa964135%28SQL.90%29.aspxYou'll have to adapt the JOIN conditions to support the comparison operators (contains, is, starts with) but they could all use LIKE. You'd have to do testing to see if this gives you good enough performance. |
|
|
|
|
|
|
Include SQL in a query that exists in a field?