Table containing SQL statements - can this be done?

Question

Post reply

Table containing SQL statements - can this be done?

Jas_The_Ace

Valued Member

Points: 53
More actions
September 18, 2009 at 3:56 am

#135158

I want to run multiple SQL statements that are stored in a table.
The table looks like this:
LogicJobID
---------------------------------------------------------------------
CompID = 21
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:
EmpIDCompIDDeptID JobID
-----------------------------------------------
112Clerk
222Clerk
324HR Manager
436Storesman
546Warehouse Op
Expected Output:
EmpIDResultID
---------------------
12
21
22
31
33
44
54
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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I understand what you're trying to do, but there's no way to achieve it that way.

You will have to build a sql query in a variable and execute it with EXEC or sp_executesql.

This doesn't necessarily mean you will need a cursor.

Regards

Gianluca

-- Gianluca Sartori

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

And this would be a prime candidate for a bit of SQL Injection unless you parse the statements first.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

The table looks like this:
LogicJobID
---------------------------------------------------------------------
CompID = 21
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:
EmpIDCompIDDeptID JobID
-----------------------------------------------
112Clerk
222Clerk
324HR Manager
436Storesman
546Warehouse Op

Is the column name and column values misplaced here in this table?

Yeah, as suggested above I think u need to use dynamic query, something like this.

Declare @Wherecondition as varchar(100)

declare @sql as varchar(max)

Select @WhereCondition from your logic table

where jobid

set @sql = 'Select * from tblemp WHERE ' + @wherecondition

sp_execute @sql

Now u need to think further if you want to get all the results at once.

---------------------------------------------------------------------------------

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

ohh sorry, now I realize it should've been jobtitle instead of jobid

---------------------------------------------------------------------------------