Alternative to function for dynamic SQL

Question

I have a stored procedure that returns a list of projectIDs at the moment. I want to extend this to return some extra information to determine if a specific user has access to this project.

To determine if a user has access I need to look up the user in a table called 'Project_XXX' where XXX is the projectID. Yes, this is shocking database design but sadly there is nothing I can do about it. My first instinct was that I could use a function as such:

SELECT ProjectID WHERE [criteria] AND myfunc(ProjectID, @username)=1

However, because the lookup needs to run dynamic SQL it tells me that I can't do this.

This leaves me with the option of putting my logic in a stored procedure but then the syntax of my select is not simple.

The way I can think of to do it is to run the select to get the list of projectIDs and then run a cursor over that table calling my stored procedure but I stopped thinking there because it seems far too complicated...

So is there some way I can do what I want with similarly simple syntax to above? Is there some way to get a user function to return based on dynamic SQL? Is there a nice way to use stored procedures as I have above? I'm sure I am missing something obvious - I rarely need to do anything complicated with SQL though. ;-)

I should also reiterate that I know that the underlying problem is with the stupid individual project tables but this is not something that can be changed.

Answer 1

One option would be to create a view from all the project_xxx tables. Something like:

CREATE VIEW SecurityTable
AS
SELECT 'Project_1', User, HasAccess
FROM   Project_1
UNION
SELECT 'Project_2', User, HasAccess
FROM   Project_2
UNION
SELECT 'Project_3', User, HasAccess
FROM   Project_3
etc...

Then you can simply query your view as though the database had been designed properly in the first place ;-)

SELECT ProjectID, User
FROM   SecurityTable
WHERE [criteria]
AND   HasAccess=1

---

As you state that you will be frequently adding new project tables, I would suggest you could have a security table populated with a dynamic query every morning. For example:

CREATE TABLE Project_1 (
    Usr varchar(20),
    HasAccess bit)
GO
CREATE TABLE Project_2 (
    Usr varchar(20),
    HasAccess bit)
GO
CREATE TABLE SecurityTable (
    Usr varchar(20),
    HasAccess bit)
GO


INSERT INTO Project_1 (Usr, HasAccess) VALUES ('Kermit', 1)
INSERT INTO Project_1 (Usr, HasAccess) VALUES ('MissPiggy', 1)
INSERT INTO Project_2 (Usr, HasAccess) VALUES ('Beaker', 1)
INSERT INTO Project_2 (Usr, HasAccess) VALUES ('TheCount', 0)
GO

Create Procedure LoadSecurityTable 
AS
    DELETE * FROM SecurityTable

    EXEC sp_MSForEachTable 
        @command1 = 'INSERT INTO SecurityTable (Usr, HasAccess) SELECT Usr, HasAccess FROM ?',
        @whereand = 'AND o.name LIKE ''Project_%'''
GO

EXEC LoadSecurityTable
SELECT * FROM SecurityTable

Answer 2

As you seem to know, you cannot run dynamic SQL or call a stored procedure from within a function.

There is a very ugly workaround that involves calling a batch script (.bat) from within your function using xp_cmdshell, and using sp_executesql in the batch script to execute the dynamic SQL.

However, I'd go with RB's view solution for the sake of simplicity!