从配置表中动态生成代码 [英] Dynamically generating code from a configuration table
问题描述
大家好。
我正在创建一个审计规范，我打算让它变得灵活，例如添加审计使用配置表和存储过程的项目。
I am creating an audit specification which is I intend to make it flexible, for example add audit items to it using a configuration table and a stored procedure.
我避免将它作为自由文本存储参数，这是因为拼写错误等，所以我在配置表中输入了值。
I avoided making this a free text stored parameter, this is because of spelling mistakes etc, so I entered the values in a configuration table.
因此该表将具有以下值作为示例
So the table would have the following values as an example
现在，我需要一个可以在下面的规范中添加项目的存储过程，还有一个用于删除项目的存储过程。
Now, I need to have a stored procedure that can add items to the specification below and also one to remove items.
创建服务器审核规范[Specification_X]
FOR SERVER AUDIT [Audit_X]
ADD（SCHEMA_OBJECT_ACCESS_GROUP），
ADD（DBCC_GROUP），
ADD（LOGOUT_GROUP）
CREATE SERVER AUDIT SPECIFICATION [Specification_X]
FOR SERVER AUDIT [Audit_X]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (DBCC_GROUP),
ADD (LOGOUT_GROUP)
在上面的例子中，我选择的项目相当于ID 9,4,8
In the example above, I have selected items which equate to ID 9,4,8
列表中的最后一项不应该有逗号， proc会接受审核ID，可能是以逗号分隔的形式，然后循环以根据ID添加项目，如果它只提供1个ID，没有逗号，如果它多于一个，那么最后一个项目
会省略ID。如果审计项已经存在，那么它应该被忽略。
The last item in the list should not have a comma, the proc would accept audit ID, probably in a comma delimited form and then loop through to add the items as per the ID, if its only 1 ID provided, no comma, if its more than one, then the last item would omit the ID. If the audit item already exists, then it should be ignored.
我正在为SQL 2008编写这个SQL到SQL 2017，所以请注意新的SQL功能可能不起作用。
I am writing this for SQL 2008 to SQL 2017, so mindful that new SQL features may not work.
任何帮助都将不胜感激。
Any help would be appreciated.
< table border ="0"cellpadding ="0"cellspacing ="0"width ="380"style ="border-collapse：collapse; width：285pt">
推荐答案
您好MrFlinstone，
Hi MrFlinstone,
感谢您的提问。
根据您的描述，我知道您希望自动生成代码以解决拼写错误的问题。但是，不需要根据配置表生成代码。而且，T-SQL不是唯一的创建
服务器审核规范的方法。您可以使用SSMS工具来实现它。请参考：
Per your description, I know that you want to generate code automatically for solving the problem of spelling mistakes. However, there is no need to generate code based on a configuration table. Also, T-SQL is not the only way to create SERVER AUDIT SPECIFICATION. You could achieve it by using the tool SSMS. Please refer:
最好 问候，
Best Regards,
将
这篇关于从配置表中动态生成代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
