动态 SQL 函数的替代方法 [英] Alternative to function for dynamic SQL

问题描述

我有一个存储过程，它目前返回一个 projectID 列表.我想对此进行扩展以返回一些额外信息，以确定特定用户是否有权访问此项目.

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.

要确定用户是否具有访问权限，我需要在名为Project_XXX"的表中查找用户，其中 XXX 是项目 ID.是的，这是令人震惊的数据库设计，但遗憾的是我对此无能为力.我的第一直觉是我可以使用这样的函数:

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

但是，由于查找需要运行动态 SQL，它告诉我我不能这样做.

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.

我能想到的方法是运行 select 以获取 projectID 列表，然后在该表上运行光标调用我的存储过程，但我不再考虑那里，因为它看起来太复杂了...

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...

那么有什么方法可以用与上面类似的简单语法做我想做的事吗?有没有办法让用户函数基于动态 SQL 返回?有没有像我上面那样使用存储过程的好方法?我确定我遗漏了一些明显的东西——不过我很少需要用 SQL 做任何复杂的事情.;-)

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.

推荐答案

一种选择是从所有 project_xxx 表创建一个视图.类似的东西:

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

这篇关于动态 SQL 函数的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！