Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use Table Column Contents As Where Clause Expand / Collapse
Author
Message
Posted Saturday, December 6, 2008 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Saturday, December 6, 2008 1:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
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 »

Add to briefcase

Permissions Expand / Collapse