Multiple Where Clauses - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Multiple Where Clauses

Author

Topic

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-09-18 : 06:19:15

I want to run multiple SQL statements that are stored in a table.

The table looks like this:

Logic JobID
----------------------------------------------------------------
CompID = 2 1
DeptID = 2 AND JobTitle = 'Clerk' 2
CONTAINS(JobTitle, 'HR') 3
JobTitle = 'Storesman' OR JobTitle = 'Warehouse Op' 4

Then my source data might look like this:

EmpID CompID DeptID JobID
-----------------------------------------------
1 1 2 Clerk
2 2 2 Clerk
3 2 4 HR Manager
4 3 6 Storesman
5 4 6 Warehouse Op

Expected Output:

EmpID ResultID
---------------------
1 2
2 1
2 2
3 1
3 3
4 4
5 4

Do I have to use a cursor to step through the logic table, or can I do it with declarative code?

I tried:
SELECT EmpID FROM tblEmp WHERE (SELECT Logic From tblLogic);
and it said 'An expression of non-boolean type specified in a context where a condition is expected'

Hope this makes sense and you can see what I'm trying to do

Jason

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 07:13:05

SELECT EmpID FROM tblEmp WHERE logic in (SELECT Logic From tblLogic);

Madhivanan

Failing to plan is Planning to fail

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-09-18 : 10:27:28

THat's not going to work because tblEmp doesn't contain Logic.
Perhaps I didn't explain it very well. I'm not trying to link on the Logic field I'm trying to use it as a WHERE clause.

Jason

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 10:46:19

Ok. What is the relation between the two tables?

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-18 : 11:36:24

I'm not sure I understand how you want to apply the Logic to tblEmp. Do you want to run one of the logic items or all of them? If all of them do you want ot run them each separately and combine the results or apply all the Logic filters in one shot?

In either case you'll have to use Dynamic SQL.

[url]http://www.sommarskog.se/dynamic_sql.html[/url]

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-09-19 : 19:42:06

Sorry my original question looks like garbage because the tabs didn't render. It should say:

Logic JobID
----------------------------------------------------------------
CompID = 2 1
DeptID = 2 AND JobTitle = 'Clerk' 2
CONTAINS(JobTitle, 'HR') 3
JobTitle = 'Storesman' OR JobTitle = 'Warehouse Op' 4

Then my source data might look like this:

EmpID CompID DeptID JobTitle
-----------------------------------------------
1 1 2 Clerk
2 2 2 Clerk
3 2 4 HR Manager
4 3 6 Storesman
5 4 6 Warehouse Op

Expected Output:

EmpID ResultID
---------------------
1 2
2 1
2 2
3 1
3 3
4 4
5 4

Yes Lamprey, I want to run all of them. I'd like to run them all at once but I don't think it's possible.
This is where I've got to now:

DECLARE csrLogic CURSOR
    FOR SELECT LogicID, Logic, ResultID FROM tblLogic
OPEN csrLogic

FETCH NEXT FROM csrLogic
INTO @LogicID, @Logic, @ResultID;

--WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'INSERT INTO tblResult (EmpID, ResultID) ' + 
	'SELECT EmpID, ' + Convert(nvarchar(20),@ResultID) + ' AS R   FROM tblEmp ' +
	'WHERE ('@Logic + ')'
	
	EXEC (@SQL);
	
	FETCH NEXT FROM csrLogic
	INTO @LogicID, @Logic, @ResultID;
END
CLOSE csrLogic
DEALLOCATE csrLogic

So is this possible without a cursor?

Jason

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-09-19 : 19:55:48

O Dear - Seem to have pasted the same garbage again

Logic......................................................................JobID
----------------------------------------------------------------
CompID = 2.................................................................1
DeptID = 2 AND JobTitle = 'Clerk'.....................................2
CONTAINS(JobTitle, 'HR')...............................................3
JobTitle = 'Storesman' OR JobTitle = 'Warehouse Op'...........4

Jason

Jas_The_Ace
Starting Member

15 Posts

Posted - 2009-09-19 : 20:29:49

Thanks Lamprey - I read that article it's really useful!!!

Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources