De: T-sql SP Builder | Universidad Net
When things are complex, and it is preferable to not use any ORM like EF, or the database needs to be secured, probably you prefer to use SQL Server Stored procedures.
However, it happens that in those cases, the database uses to have hundreds of tables, and it takes a lot of time to write the standard procedures for CRUD operations for them. This code helps you to build the Insert, Update and Delete procedure for each table. In all cases, the task is performed in just one row, using the Primary key to identify it for update and delete.
How it works.
By getting information from the INFORMATION_SCHEMA view, and using some special system tables like sys.columns and sys.computed_columns, the code retrieves the information for the required table.
The information is stored in a table variable which contains columns to make easier to build the different parts of the sentence, like adding parenthesis, default values, etc. which contains data as you can see in the next image
Then, several queries against the table variable constructs the parameters, sentence, and where segments of the different procedures.
In this case, I will not explain each part. Most of it use STRING_AGG to concatenate the different parts.
However, some comments about the different “issues” I found making this:
- The parameters for the Insert procedures, include an OUTPUT statement, in case a column has identity specification to get the value for the inserted row. To do so, the procedure uses IDENT_CURRENT function. But the parameters for Update and Delete do not need the OUTPUT.
- The computed columns must not be inserted or updated, so must be excluded of the procedure. This happens in case a column is of timestamp datatype as well.
- Just to be sure what the script generates, the results are printed so you can review them.
You can download this script here.
The script as Stored Procedure.
If you want to use this several times during your development, you can use this second script, which add this as a Stored Procedure, so you can call it just passing the table name, and, when needed, the schema name.
Moreover, it accepts a third parameter, to perform the procedures’ creation directly.
Finally, if you want to use the procedure for all the tables of a database at once, just use this sentence for creating a complete script.
SELECT 'EXECUTE @RC = [Development].[SPBuilder] @TableName' + QUOTENAME([table_schema]) + ',@SchemaName =' + QUOTENAME([table_name]) + ' ,@Create=1' FROM [INFORMATION_SCHEMA].[TABLES] WHERE [table_type] = 'BASE TABLE' AND [table_name] NOT LIKE 'sys%';
