Script voor cre-eeren van CRUD stored procedures voor alle tabellen in database

Het volgende script ( van www.sqlbook.com ) cre-eert CRUD stored procedures voor alle tabbellen in de database.

Of voor alle tabellen die je specificeert in je select statement.

CRUD:
- Create
- Update
- Delete
- Get specific record

#########################################################

-- Author: www.sqlbook.com

-- Copyright: (c) www.sqlbook.com. You are free to use and redistribute

-- this script as long as this comments section with the

-- author and copyright details are not altered.

-- Purpose: For a specified user defined table (or all user defined

-- tables) in the database this script generates 4 Stored

-- Procedure definitions with different Procedure name

-- suffixes:

-- 1) List all records in the table (suffix of _lst)

-- 2) Get a specific record from the table (suffix of _sel)

-- 3) UPDATE or INSERT (UPSERT) - (suffix of _ups)

-- 4) DELETE a specified row - (suffix of _del)

-- e.g. For a table called location the script will create

-- procedure definitions for the following procedures:

-- dbo.udp_Location_lst

-- dbo.udp_Location_sel

-- dbo.udp_Location_ups

-- dbo.udp_Location_del

-- Notes: The stored procedure definitions can either be printed

-- to the screen or executed using EXEC sp_ExecuteSQL.

-- The stored proc names are prefixed with udp_ to avoid

-- conflicts with system stored procs.

-- Assumptions: - This script assumes that the primary key is the first

-- column in the table and that if the primary key is

-- an integer then it is an IDENTITY (autonumber) field.

-- - This script is not suitable for the link tables

-- in the middle of a many to many relationship.

-- - After the script has run you will need to add

-- an ORDER BY clause into the '_lst' procedures

-- according to your needs / required sort order.

-- - Assumes you have set valid values for the

-- config variables in the section immediately below

-- #########################################################

-- ##########################################################

/* SET CONFIG VARIABLES THAT ARE USED IN SCRIPT */

-- ##########################################################

-- Do we want to generate the SP definitions for every user defined

-- table in the database or just a single specified table?

-- Assign a blank string - '' for all tables or the table name for

-- a single table.

DECLARE

@GenerateProcsFor varchar(100)

-- SET @GenerateProcsFor = 'app_Indicatoren'

SET

@GenerateProcsFor = ''

-- which database do we want to create the procs for?

-- Change both the USE and SET lines below to set the datbase name

-- to the required database.

USE

iPrestoTest

DECLARE

@DatabaseName varchar(100)

SET

@DatabaseName = 'iPrestoTest'

-- do we want the script to print out the CREATE PROC statements

-- or do we want to execute them to actually create the procs?

-- Assign a value of either 'Print' or 'Execute'

DECLARE

@PrintOrExecute varchar(10)

SET

 

@PrintOrExecute = 'Print'

-- Is there a table name prefix i.e. 'tbl_' which we don't want

-- to include in our stored proc names?

DECLARE

@TablePrefix varchar(10)

SET

@TablePrefix = ''

-- For our '_lst' and '_sel' procedures do we want to

-- do SELECT * or SELECT [ColumnName,]...

-- Assign a value of either 1 or 0

DECLARE

SET

@UseSelectWildCard bit @UseSelectWildCard = 0

-- ##########################################################

/* END SETTING OF CONFIG VARIABLE

-- do not edit below this line */

-- ##########################################################

 

-- DECLARE CURSOR containing all columns from user defined tables

-- in the database

DECLARE

TableCol Cursor FOR

SELECT

c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH

FROM

INFORMATION_SCHEMA.Columns c INNER JOIN

 

INFORMATION_SCHEMA.Tables t ON c.TABLE_NAME = t.TABLE_NAME

WHERE

 

t.Table_Catalog = @DatabaseNameAND t.TABLE_TYPE = 'BASE TABLE'

 

AND t.TABLE_NAME like 'app_%'

ORDER

BY c.TABLE_NAME, c.ORDINAL_POSITION

-- Declare variables which will hold values from cursor rows

DECLARE

@TableSchema varchar(100), @TableName varchar(100)

DECLARE

@ColumnName varchar(100), @DataType varchar(30)

DECLARE

DECLARE

@CharLength int @ColumnNameCleaned varchar(100)

-- Declare variables which will track what table we are

-- creating Stored Procs for

DECLARE

@CurrentTable varchar(100)

DECLARE

DECLARE

@FirstTable bit @FirstColumnName varchar(100)

DECLARE

@FirstColumnDataType varchar(30)

DECLARE

@ObjectName varchar(100) -- this is the tablename with the

 

-- specified tableprefix lopped off.

DECLARE

@TablePrefixLength int

-- init vars

SET

@CurrentTable = ''

SET

@FirstTable = 1

SET

@TablePrefixLength = Len(@TablePrefix)

-- Declare variables which will hold the queries we are building use unicode

-- data types so that can execute using sp_ExecuteSQL

DECLARE

@LIST nvarchar(4000), @UPSERT nvarchar(4000)

DECLARE

@SELECT nvarchar(4000), @INSERT nvarchar(4000), @INSERTVALUES varchar(4000)

DECLARE

 

@UPDATE nvarchar(4000), @DELETE nvarchar(4000)

-- open the cursor

OPEN

TableCol

-- get the first row of cursor into variables

FETCH

NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength

-- loop through the rows of the cursor

WHILE

@@FETCH_STATUS = 0 BEGIN

 

SET @ColumnNameCleaned = Replace(@ColumnName, ' ', '')

 

-- is this a new table?

 

IF @TableName <> @CurrentTable BEGIN

 

 

-- if is the end of the last table

 

IF @CurrentTable <> '' BEGIN

 

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

 

-- first add any syntax to end the statement

 

 

-- _lst

 

SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)

 

SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)

 

SET @LIST = @LIST + Char(13)

 

 

-- _sel

 

SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)

 

SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

 

SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13) + Char(13)

 

SET @SELECT = @SELECT + Char(13)

 

 

 

-- UPDATE (remove trailing comma and append the WHERE clause)

 

SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

 

 

-- INSERT

 

SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)

 

SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'

 

 

 

SET @INSERT = @INSERT + @INSERTVALUES-- _ups

 

SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)

 

SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)

 

IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')

 

BEGIN

 

SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)

 

END ELSE BEGIN

 

 

SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13) END

 

SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)

 

SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)

 

SET @UPSERT = @UPSERT + 'END' + Char(13)

 

SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)

 

SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)

 

SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)

 

SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13) + Char(13)

 

SET @UPSERT = @UPSERT + Char(13)

 

 

-- _del

 

-- delete proc completed already

 

 

-- --------------------------------------------------

 

-- now either print the SP definitions or

 

-- execute the statements to create the procs

 

-- --------------------------------------------------

 

IF @PrintOrExecute <> 'Execute' BEGIN

 

 

 

 

 

PRINT @LISTPRINT @SELECTPRINT @UPSERTPRINT @DELETEEND ELSE BEGIN

 

 

 

 

 

EXEC sp_Executesql @LISTEXEC sp_Executesql @SELECTEXEC sp_Executesql @UPSERTEXEC sp_Executesql @DELETEEND

 

END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable

 

END

 

 

-- update the value held in @CurrentTable

 

 

 

 

 

SET @CurrentTable = @TableNameSET @FirstColumnName = @ColumnNameSET @FirstColumnDataType = @DataTypeIF @TablePrefixLength > 0 BEGIN

 

IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix BEGIN

 

--PRINT Char(13) + 'DEBUG: OBJ NAME: ' + RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)

 

SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)

 

END ELSE BEGIN

 

 

SET @ObjectName = @CurrentTableEND

 

END ELSE BEGIN

 

 

SET @ObjectName = @CurrentTableEND

 

 

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

 

 

-- ----------------------------------------------------

 

-- now start building the procedures for the next table

 

-- ----------------------------------------------------

 

 

-- _lst

 

SET @LIST = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_lst]' + Char(13)

 

SET @LIST = @LIST + 'AS' + Char(13)

 

SET @LIST = @LIST + 'SET NOCOUNT ON' + Char(13)

 

IF @UseSelectWildcard = 1 BEGIN

 

SET @LIST = @LIST + Char(13) + 'SELECT * '

 

 

END ELSE BEGIN

 

SET @LIST = @LIST + Char(13) + 'SELECT [' + @ColumnName + ']'

 

END

 

 

-- _sel

 

SET @SELECT = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_sel]' + Char(13)

 

 

SET @SELECT = @SELECT + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataTypeIF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN

 

SET @SELECT = @SELECT + '(' + CAST(@CharLength As varchar(10)) + ')'

 

END

 

SET @SELECT = @SELECT + Char(13) + 'AS' + Char(13)

 

SET @SELECT = @SELECT + 'SET NOCOUNT ON' + Char(13)

 

IF @UseSelectWildcard = 1 BEGIN

 

SET @SELECT = @SELECT + Char(13) + 'SELECT * '

 

 

END ELSE BEGIN

 

SET @SELECT = @SELECT + Char(13) + 'SELECT [' + @ColumnName + ']'

 

END

 

 

-- _ups

 

SET @UPSERT = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_ups]' + Char(13)

 

 

SET @UPSERT = @UPSERT + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataTypeIF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN

 

SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As Varchar(10)) + ')'

 

END

 

 

-- UPDATE

 

SET @UPDATE = Char(9) + 'UPDATE ' + @TableName + ' SET ' + Char(13)

 

 

-- INSERT -- don't add first column to insert if it is an

 

-- integer (assume autonumber)

 

SET @INSERT = Char(9) + 'INSERT INTO ' + @TableName + ' (' + Char(13)

 

SET @INSERTVALUES = Char(9) + 'VALUES (' + Char(13)

 

 

IF @FirstColumnDataType NOT IN ('int', 'bigint', 'smallint', 'tinyint')

 

BEGIN

 

SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)

 

SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)

 

END

 

 

-- _del

 

SET @DELETE = 'CREATE PROC [dbo].[udp_' + @ObjectName + '_del]' + Char(13)

 

 

SET @DELETE = @DELETE + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataTypeIF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN

 

SET @DELETE = @DELETE + '(' + CAST(@CharLength As Varchar(10)) + ')'

 

END

 

SET @DELETE = @DELETE + Char(13) + 'AS' + Char(13)

 

SET @DELETE = @DELETE + 'SET NOCOUNT ON' + Char(13) + Char(13)

 

SET @DELETE = @DELETE + 'DELETE FROM ' + @TableName + Char(13)

 

SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + Char(13)

 

SET @DELETE = @DELETE + Char(13) + 'SET NOCOUNT OFF' + Char(13)

 

 

SET @DELETE = @DELETE + Char(13) END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable

 

END

 

ELSE BEGIN

 

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

 

 

-- is the same table as the last row of the cursor

 

-- just append the column

 

 

-- _lst

 

IF @UseSelectWildCard = 0 BEGIN

 

SET @LIST = @LIST + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'

 

END

 

 

-- _sel

 

IF @UseSelectWildCard = 0 BEGIN

 

SET @SELECT = @SELECT + ', ' + Char(13) + Char(9) + '[' + @ColumnName + ']'

 

END

 

 

-- _ups

 

 

SET @UPSERT = @UPSERT + ',' + Char(13) + Char(9) + '@' + @ColumnNameCleaned + ' ' + @DataTypeIF @DataType IN ('varchar', 'nvarchar', 'char', 'nchar') BEGIN

 

SET @UPSERT = @UPSERT + '(' + CAST(@CharLength As varchar(10)) + ')'

 

END

 

 

-- UPDATE

 

SET @UPDATE = @UPDATE + Char(9) + Char(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + Char(13)

 

 

-- INSERT

 

SET @INSERT = @INSERT + Char(9) + Char(9) + '[' + @ColumnName + '],' + Char(13)

 

SET @INSERTVALUES = @INSERTVALUES + Char(9) + Char(9) + '@' + @ColumnNameCleaned + ',' + Char(13)

 

 

-- _del

 

-- delete proc completed already

 

END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable'

 

END

 

-- fetch next row of cursor into variables

 

FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength

END

-- ----------------

-- clean up cursor

-- ----------------

CLOSE

TableCol

DEALLOCATE

TableCol

-- ------------------------------------------------

-- repeat the block of code from within the cursor

-- So that the last table has its procs completed

-- and printed / executed

-- ------------------------------------------------

-- if is the end of the last table

IF

@CurrentTable <> '' BEGIN

 

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN

 

-- first add any syntax to end the statement

 

 

-- _lst

 

SET @LIST = @List + Char(13) + 'FROM ' + @CurrentTable + Char(13)

 

SET @LIST = @LIST + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)

 

SET @LIST = @LIST + Char(13)

 

 

-- _sel

 

SET @SELECT = @SELECT + Char(13) + 'FROM ' + @CurrentTable + Char(13)

 

SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

 

SET @SELECT = @SELECT + Char(13) + Char(13) + 'SET NOCOUNT OFF' + Char(13)

 

 

SET @SELECT = @SELECT + Char(13)

 

-- UPDATE (remove trailing comma and append the WHERE clause)

 

SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE)- 1) + Char(13) + Char(9) + 'WHERE [' + @FirstColumnName + '] = @' + Replace(@FirstColumnName, ' ', '') + Char(13)

 

 

-- INSERT

 

SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + Char(13) + Char(9) + ')' + Char(13)

 

SET @INSERTVALUES = SUBSTRING(@INSERTVALUES, 0, LEN(@INSERTVALUES) -1) + Char(13) + Char(9) + ')'

 

 

 

SET @INSERT = @INSERT + @INSERTVALUES-- _ups

 

SET @UPSERT = @UPSERT + Char(13) + 'AS' + Char(13)

 

SET @UPSERT = @UPSERT + 'SET NOCOUNT ON' + Char(13)

 

IF @FirstColumnDataType IN ('int', 'bigint', 'smallint', 'tinyint', 'float', 'decimal')

 

BEGIN

 

SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = 0 BEGIN' + Char(13)

 

END ELSE BEGIN

 

 

SET @UPSERT = @UPSERT + 'IF @' + Replace(@FirstColumnName, ' ', '') + ' = '''' BEGIN' + Char(13) END

 

SET @UPSERT = @UPSERT + ISNULL(@INSERT, '') + Char(13)

 

SET @UPSERT = @UPSERT + Char(9) + 'SELECT SCOPE_IDENTITY() As InsertedID' + Char(13)

 

SET @UPSERT = @UPSERT + 'END' + Char(13)

 

SET @UPSERT = @UPSERT + 'ELSE BEGIN' + Char(13)

 

SET @UPSERT = @UPSERT + ISNULL(@UPDATE, '') + Char(13)

 

SET @UPSERT = @UPSERT + 'END' + Char(13) + Char(13)

 

SET @UPSERT = @UPSERT + 'SET NOCOUNT OFF' + Char(13)

 

SET @UPSERT = @UPSERT + Char(13)

 

-- _del

 

-- delete proc completed already

 

-- --------------------------------------------------

 

-- now either print the SP definitions or

 

-- execute the statements to create the procs

 

-- --------------------------------------------------

 

IF @PrintOrExecute <> 'Execute' BEGIN

 

 

 

 

 

PRINT @LISTPRINT @SELECTPRINT @UPSERTPRINT @DELETEEND ELSE BEGIN

 

 

 

 

 

EXEC sp_Executesql @LISTEXEC sp_Executesql @SELECTEXEC sp_Executesql @UPSERTEXEC sp_Executesql @DELETEEND

 

END -- end @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable

END



{{opmerking.Naam}}:

{{opmerking.OpmerkingText}}

            

Saving your comment....

Naam is verplicht!
Email is verplicht!
Opmerking is verplicht!