Use Table Column Contents As Where Clause

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Use Table Column Contents As Where Clause

Use Table Column Contents As Where Clause

Rate Topic

Display Mode

Topic Options

Author

Message

Derek-918769

Posted Saturday, December 06, 2008 1:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 15, 2010 1:42 PM
Points: 4, Visits: 8

Hello, I'm hoping someone has come across a similar issue and may have a solution for me.

Currently have the following table setup (only showing affected columns). Column list is as follows:

Table Name: tblActivity (tblDenyActivity)
DenyDateUserID
DenyDate
InvalidDate

Table Name: tblUserData
UserID

Table Name: tblUserSkillset
UserID
SkillsetTypeID

Table Name: tblSkillsetType
SkillsetTypeID
SkillSetType
SQLSnippet

SELECT tblDenyActivity.DenyDateUserID, tblDenyActivity.DenyDate FROM dbo.tblActivity AS tblDenyActivity INNER JOIN
dbo.tblUserData ON tblDenyActivity.DenyDateUserID = dbo.tblUserData.UserID AND tblDenyActivity.InvalidDate IS NULL INNER JOIN dbo.tblUserSkillset ON dbo.tblUserData.UserID = dbo.tblUserSkillset.UserID INNER JOIN dbo.tblSkillsetType ON dbo.tblUserSkillset.SkillsetTypeID = dbo.tblSkillsetType.SkillsetTypeID
WHERE (dbo.tblSkillsetType.SQLSnippet)

The column SQLSnippet will contain something like the following (DenyDate IS NOT NULL AND DATEDIFF(dd,DenyDate,GETDATE()) > 31)

So my end result would be:

SELECT tblDenyActivity.DenyDateUserID, tblDenyActivity.DenyDate FROM dbo.tblActivity AS tblDenyActivity INNER JOIN
dbo.tblUserData ON tblDenyActivity.DenyDateUserID = dbo.tblUserData.UserID AND tblDenyActivity.InvalidDate IS NULL INNER JOIN dbo.tblUserSkillset ON dbo.tblUserData.UserID = dbo.tblUserSkillset.UserID INNER JOIN dbo.tblSkillsetType ON dbo.tblUserSkillset.SkillsetTypeID = dbo.tblSkillsetType.SkillsetTypeID
WHERE (DenyDate IS NOT NULL AND DATEDIFF(dd,DenyDate,GETDATE()) > 31)

Has anyone ever tried to do anything like this?

Post #615077

GilaMonster

Posted Saturday, December 06, 2008 1:32 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

The only way to do that is to use dynamic SQL, build up the final query and then use sp_executeSQL to run it.

Be aware of the downsides of dynamic SQL and watch for SQL injection.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #615084

« Prev Topic | Next Topic »

Permissions