Use Table Column Contents As Where Clause

  • 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?

  • 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, MVP, M.Sc (Comp Sci)
    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

Viewing 2 posts - 1 through 1 (of 1 total)

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