How do I take a table, and auto-gen CRUD stored procs for it in SSMS?
8 Answers
You can generate four CRUD procedures using a predefined templates from the selected table. This functionality is available in several add-ins, such as SSMS Toolpack and SQL Complete.
CREATE PROCEDURE SP_USUARIO
@usuario varchar(30)=null,
@clave varchar(500)=null,
@idPersona int=null,
@idRol int=null,
@idUsuario int=null,
@TIPO TINYINT=1
AS
BEGIN
IF @TIPO=1 -- LISTAR PERSONAS
BEGIN
SELECT idPersona,Nombre,ApePaterno,ApeMaterno,DNI,Direccion,celular,email FROM Persona
END
IF @TIPO=2 -- LISTAR ROLES
BEGIN
SELECT idRol,Nombre,Descripcion FROM ROL
END
IF @TIPO=3 -- LISTAR USUARIOS
BEGIN
SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario
END
IF @TIPO=3 -- USUARIOS
BEGIN
SELECT idUsuario,usuario,pass,idPersona,idRol from Usuario where idUsuario=@idUsuario
END
IF @TIPO=4 -- AGREGAR USUARIO
BEGIN
INSERT Usuario(usuario,pass,idPersona,idRol) values(@usuario,@clave,@idPersona,@idRol)
END
IF @TIPO=5 -- EDITAR USUARIO
BEGIN
UPDATE Usuario SET usuario=@usuario,pass=@clave,@idPersona=@idPersona,idRol=@idRol where idUsuario=@idUsuario
END
IF @TIPO=6 -- ELIMINAR USUARIO
BEGIN
DELETE Usuario WHERE idUsuario=@idUsuario
END
END
go
Thanks for the original script.
I improved it with the following :
- Allow to do on another database
- For select, generate dynamically the query according parameters
- Manage columns CreatedBy/Date and ModificationBy/Date
- Work even if special characters are found in schema/table/column
- Allow to add systematically the user and culture.
- Template for procedure name
And lot of options.
Note: code send in two answers as limited to 30000 characters.
IF OBJECT_ID('dbo.GenerateDynamicallyProceduresForTables','P') IS NOT NULL
DROP PROCEDURE dbo.GenerateDynamicallyProceduresForTables
GO
CREATE PROCEDURE dbo.GenerateDynamicallyProceduresForTables @DatabaseName nvarchar(200)=NULL,
@SchemaName nvarchar(200) = NULL,
@TableName nvarchar(200) = NULL,
@NoCount bit=1,
@ManageTransaction bit=1,
@GenerateDebugScriptForList bit = 1,
@ParameterForUser nvarchar(20) = '@UserInP',
@ParameterForCulture nvarchar(20) = '@CultureInP',
@FirstParametersAreMandatory bit=1,
@ProcedureTemplateName nvarchar(100) = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
@ColumnNameLimitation nvarchar(500)= '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
@CreationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
@CreationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
@ModificationUserMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%'' OR syscolumns.name LIKE ''%ModifiedUser%''',
@ModificationDateMatch nvarchar(500) = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''
AS
BEGIN
DECLARE @UnCommentExecForDebug bit=0 --To set at 0 for final
DECLARE @StatementList TABLE(id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,FullTableName nvarchar(1000),StatementType nvarchar(100),Statement nvarchar(max))
DECLARE @FirstParameters nvarchar(400)='',@FirstParametersForExec nvarchar(400)=''
IF LEN(@ParameterForUser)>1
BEGIN
SET @FirstParameters = @FirstParameters + @ParameterForUser +' nvarchar(500)' + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '' END + ',
'
SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForUser + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE ' =''K2:Denallix\Administrator''' END + ',
'
END
IF LEN(@ParameterForCulture)>1
BEGIN
SET @FirstParameters = @FirstParameters + @ParameterForCulture + ' nvarchar(10)' + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '' END + ',
'
SET @FirstParametersForExec = @FirstParametersForExec + @ParameterForCulture + CASE WHEN @FirstParametersAreMandatory =0 THEN ' = NULL' ELSE '=''en-gb''' END + ',
'
END
IF NOT(LEN(@DatabaseName)>0)
SET @DatabaseName=DB_NAME()
IF LEN(@SchemaName)=0
SET @SchemaName=NULL
IF LEN(@TableName)=0
SET @TableName=NULL
IF NOT(LEN(@ColumnNameLimitation)>0)
SET @ColumnNameLimitation = '1=1'
IF NOT(LEN(@CreationUserMatch)>0)
SET @CreationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@CreationDateMatch)>0)
SET @CreationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@ModificationUserMatch)>0)
SET @ModificationUserMatch = 'syscolumns.name = ''BIDON12345678917071979'''
IF NOT(LEN(@ModificationDateMatch)>0)
SET @ModificationDateMatch = 'syscolumns.name = ''BIDON12345678917071979'''
DECLARE @strSpText nVarchar(max) ='USE [' + @DatabaseName + ']'
IF @DatabaseName!=DB_NAME()
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Set current database',@strSPText)
DECLARE @sqlstatementForTables nvarchar(max) = -- Not test with USE [' + @DatabaseName + '] ISSUE ON Table iDENTITY.Identity: 'Could not complete cursor operation because the set options have changed since the cursor was declared
N'
DECLARE Tables_cursor CURSOR FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=''BASE TABLE''
AND (TABLE_SCHEMA=@pSchemaName OR @pSchemaName IS NULL)
AND (Table_Name=@pTableName OR @pTableName IS NULL)'
--EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns
exec sp_executesql @sqlstatementForTables, N'@pSchemaName nvarchar(200),@pTableName nvarchar(200)', @pSchemaName=@SchemaName, @pTableName=@TableName;
OPEN Tables_cursor
DECLARE @CurrentSchemaName nvarchar(100),@CurrentFullTableName nvarchar(1000),@CurrentTableName nVarchar(1000),
@DropStatement nvarchar(max)=''
Fetch next
from Tables_cursor
INTO @CurrentSchemaName,@CurrentTableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @CurrentFullTableName='['+@CurrentSchemaName+'].['+@CurrentTableName+']';
--PRINT @CurrentFullTableName
Declare @dbName nVarchar(50)
Declare @insertSPName nVarchar(4000), @updateSPName nVarchar(4000), @deleteSPName nVarchar(4000), @listSPName nVarchar(4000)--, @ReadSPName nVarchar(50) ;
Declare @ColumnParametersInsert nVarchar(max), @ColumnDefForInsert nVarchar(max),@ColumnInValueForInsert nVarchar(max),
@ColumnParametersList nVarchar(max),@ColumnParametersListForExec nVarchar(max),
@tableColumnForWhereInList nvarchar(max),
@tableColumnForWhereInListVariables nVarchar(max), @tableColumnForWhereInListAffectVariables nVarchar(max),@DebugVariablesForList nvarchar(max)='',
@ColumnParametersInsertForExec nvarchar(max)
Declare @tableCols nVarchar(max), @ColumnParametersUpdate nVarchar(max),@ColumnParametersUpdateForExec nVarchar(max);
Declare @space nVarchar(50) = REPLICATE(' ', 4) ;
Declare @colName nVarchar(max) ;
Declare @DataType nvarchar(200),@colVariable nVarchar(200),@colVariableProc nVarchar(200);
Declare @colParameter nVarchar(max) ;
Declare @colAllowNull nvarchar(15), @colIsPrimaryKey INT,@ColIsIdentityAutoIncrement INT,@ColLength INT,@ColIsComputed INT,@ColMatchCreationUser INT,@ColMatchCreationDate INT,@ColMatchModificationUser INT,@ColMatchModificationDate INT;
Declare @updCols nVarchar(max);
Declare @ColumnParametersDelete nVarchar(max),@ColumnParametersDeleteForExec nVarchar(max),
@LastPrimaryKey nvarchar(max),@NbPrimaryKey INT=0,@ColNumber int=0
Declare @whereCols nVarchar(2000);
DECLARE @SetVariablesForExec nvarchar(max)='',@SetVariablesForExecUpdate nvarchar(max)='', @SetVariablesForExecDelete nvarchar(max)=''
DECLARE @strBegin nvarchar(1000)=' AS' + CHAR(13) + CHAR(10) + 'BEGIN',@spaceForTrans nvarchar(10)=''
IF @NoCount=1
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET NOCOUNT ON '
IF @ManageTransaction = 1
BEGIN
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'SET XACT_ABORT ON -- if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.'
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + ''
Set @strBegin = @strBegin + CHAR(13) + CHAR(10) + @space + 'BEGIN TRAN '
SET @spaceForTrans= @space;
END
DECLARE @strEnd nvarchar(1000)=''
IF @ManageTransaction = 1
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + @space + 'COMMIT TRAN '
SET @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'END'
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + 'GO'
Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + ''
IF @UnCommentExecForDebug = 0 Set @strEnd = @strEnd + CHAR(13) + CHAR(10) + '/*'
IF @ProcedureTemplateName IS NULL
BEGIN
Set @insertSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_insert]' ;
Set @updateSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_update]' ;
Set @deleteSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_delete]' ;
set @listSPName = '['+@CurrentSchemaName+'].[sp_' + @CurrentTableName +'_list]' ;
END
ELSE
BEGIN
DECLARE @ProcedureName nvarchar(200)=REPLACE(REPLACE(@ProcedureTemplateName,'{SchemaName}',ISNULL(@SchemaName,@CurrentSchemaName)),'{TableName}',ISNULL(@TableName,@CurrentTableName));
Set @insertSPName = REPLACE(@ProcedureName,'{ActionType}','Insert')
Set @updateSPName = REPLACE(@ProcedureName,'{ActionType}','Update')
Set @deleteSPName = REPLACE(@ProcedureName,'{ActionType}','Delete')
Set @listSPName = REPLACE(@ProcedureName,'{ActionType}','List')
END
SET @DropStatement = @DropStatement+ '
DROP PROCEDURE ' + @insertSPName + '
DROP PROCEDURE ' + @updateSPName + '
DROP PROCEDURE ' + @deleteSPName +'
DROP PROCEDURE ' + @listSPName
Set @ColumnParametersInsert = @FirstParameters ;
SET @ColumnParametersInsertForExec = @FirstParametersForExec
Set @ColumnParametersUpdate=@FirstParameters
SET @ColumnParametersUpdateForExec=@FirstParametersForExec
Set @ColumnParametersDelete = @FirstParameters ;
SET @ColumnParametersDeleteForExec = @FirstParametersForExec ;
SET @ColumnParametersList = @FirstParameters;
SET @ColumnParametersListForExec = @FirstParametersForExec
SET @tableColumnForWhereInList= ''
SET @tableColumnForWhereInListVariables =''
SET @tableColumnForWhereInListAffectVariables =''
SET @DebugVariablesForList ='';
Set @ColumnDefForInsert = '' ;
Set @ColumnInValueForInsert = '' ;
Set @strSPText = '' ;
Set @tableCols = '' ;
Set @updCols = '' ;
Set @whereCols = '' ;
SET NOCOUNT ON
CREATE TABLE #tmp_Structure (colid int,ColumnName nvarchar(max),
ColumnVariable nvarchar(max),
DataType nvarchar(max),
ColumnParameter nvarchar(max),
AllowNull int,
IsPrimaryKey int,
IsIdentityAutoIncrement int,
ColLength int,
IsIsComputedColumn int,
ColMatchCreationUser int,ColMatchCreationDate int,
ColMatchModificationUser INT,ColMatchModificationDate INT)
DECLARE @sqlstatementForColumns nvarchar(max) =
N'USE [' + @DatabaseName + ']
SELECT distinct
--sysobjects.name as ''Table'',
syscolumns.colid ,
''['' + syscolumns.name + '']'' as ''ColumnName'',
''@''+syscolumns.name as ''ColumnVariable'',
systypes.name +
Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''DataType'' ,
systypes.name + Case When systypes.xusertype in (165,167,175,231,239 ) Then ''('' + Convert(varchar(10),Case When syscolumns.length=-1 Then ''max'' else CAST(syscolumns.length AS nvarchar(10)) end) +'')'' Else '''' end as ''ColumnParameter'',
COLUMNPROPERTY(OBJECT_ID(@pFullTableName),syscolumns.name,''AllowsNull'') AS AllowNull,
(SELECT COUNT(*)
FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
[' + @DatabaseName + '].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = ''PRIMARY KEY''
AND Col.Table_Name = @pTableName
AND Tab.TABLE_SCHEMA=@pSchemaName
AND Col.Column_Name = syscolumns.name
) AS IsPrimaryKey,
SC.is_identity AS IsIdentityAutoIncrement,
syscolumns.length,
(SELECT COUNT(*)
FROM sys.computed_columns
WHERE computed_columns.object_id=sysobjects.id
AND computed_columns.Name=syscolumns.name) AS IsComputedColumn,
CASE WHEN ' + @CreationUserMatch +' THEN 1 ELSE 0 END AS ColMatchCreationUser,
CASE WHEN ' + @CreationDateMatch +' THEN 1 ELSE 0 END AS ColMatchCreationDate,
CASE WHEN ' + @ModificationUserMatch +' THEN 1 ELSE 0 END AS ColMatchModificationUser,
CASE WHEN ' + @ModificationDateMatch +' THEN 1 ELSE 0 END AS ColMatchModificationDate
FROM sysobjects
LEFT JOIN syscolumns ON syscolumns.id=sysobjects.id
LEFT JOIN systypes ON systypes.xusertype=syscolumns.xusertype
LEFT JOIN sys.columns SC ON SC.object_id = sysobjects.id
AND SC.name=syscolumns.name
Where sysobjects.xtype = ''u''
and sysobjects.id = OBJECT_ID(@pFullTableName)
AND (' + @ColumnNameLimitation + ')
Order by syscolumns.colid'
--PRINT @sqlstatementForColumns
--EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug '@sqlstatementForColumns',@sqlstatementForColumns
INSERT INTO #tmp_Structure
exec sp_executesql @sqlstatementForColumns, N'@pSchemaName nvarchar(200),@pTableName nvarchar(200),@pFullTableName nvarchar(1000)', @pSchemaName=@CurrentSchemaName, @pTableName=@CurrentTableName,@pFullTableName=@CurrentFullTableName;
--SELECT * FROM #tmp_Structure
/* Read the table structure and populate variables*/
DECLARE SpText_Cursor CURSOR FOR
SELECT ColumnName, ColumnVariable, DataType, ColumnParameter, AllowNull, IsPrimaryKey, IsIdentityAutoIncrement,ColLength, IsIsComputedColumn,ColMatchCreationUser,ColMatchCreationDate,ColMatchModificationUser,ColMatchModificationDate
FROM #tmp_Structure
OPEN SpText_Cursor
FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable, @DataType, @colParameter, @colAllowNull,@colIsPrimaryKey, @ColIsIdentityAutoIncrement,@ColLength, @ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ColNumber=@ColNumber+1
SET @SetVariablesForExec = @SetVariablesForExec + CASE WHEN @colAllowNull =1 THEN ''
ELSE CASE WHEN @DataType IN ('datetime','datetime2','smalldatetime','date') AND @SetVariablesForExec NOT LIKE '%@Date%' THEN CHAR(13) +CHAR(10) + 'DECLARE @Date datetime =GetDate()'
WHEN @DataType IN ('uniqueidentifier') AND @SetVariablesForExec NOT LIKE '%@GuidTest%' THEN CHAR(13) +CHAR(10) + 'DECLARE @TheGuid uniqueidentifier =NEWID()'
ELSE ''
END
END
--RegEx to keep only alphanumeric characters:
DECLARE @MatchExpression nvarchar(20) = '%[^a-z0-9]%',@DateTypeWithoutSpecialCharacters nvarchar(100)=@DataType;
WHILE PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters) > 0
SET @DateTypeWithoutSpecialCharacters = Stuff(@DateTypeWithoutSpecialCharacters, PatIndex(@MatchExpression, @DateTypeWithoutSpecialCharacters), 1, '')
--Remove Special characters (like space...) for variable name
WHILE PatIndex(@MatchExpression, @colVariable) > 0
SET @colVariable = Stuff(@colVariable, PatIndex(@MatchExpression, @colVariable), 1, '')
SET @colVariableProc = '@p'+ @colVariable
SET @colVariable = '@'+ @colVariable
SET @colParameter = @colVariable + ' ' + @colParameter
DECLARE @AffectationForExec nvarchar(max)=@colVariable + CASE WHEN @colAllowNull =1 THEN ' = NULL'
ELSE ' = ' + CASE WHEN @DataType IN ('Text','sysname') OR @DataType LIKE '%char%' THEN '''' + SUBSTRING ( CAST(ABS(@ColLength) AS nvarchar(10)) + 'TEST' + @DateTypeWithoutSpecialCharacters,0,CASE WHEN @ColLength < 0 THEN 1000 WHEN @DataType LIKE 'nchar%' THEN @ColLength/2+1 ELSE @ColLength END) + ''''
WHEN @DataType IN ('int','numeric','bigint','tinyint') THEN CAST(@ColNumber AS nvarchar(10))
WHEN @DataType IN ('bit') THEN '0'
WHEN @DataType IN ('float') THEN CAST(@ColNumber AS nvarchar(10)) + '.' + CAST(@ColNumber+1 AS nvarchar(10))
WHEN @DataType IN ('datetime','datetime2','smalldatetime','date') THEN '@Date'
WHEN @DataType IN ('uniqueidentifier') THEN '@TheGuid'
WHEN @DataType IN ('xml') THEN '''<testXML><value name="test">' + CAST(@ColNumber AS nvarchar(10)) + '</value></testXML>'''
ELSE '''1''--Currently Not managed'
END
END + ', --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
IF @ColIsIdentityAutoIncrement = 0 AND @ColIsComputed = 0
BEGIN
IF @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
Set @ColumnDefForInsert = @ColumnDefForInsert + @colName+ ',' + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans ;
IF @ColMatchCreationUser= 0 AND @ColMatchCreationDate = 0 AND @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
BEGIN
Set @ColumnParametersInsert = @ColumnParametersInsert + @colParameter + CASE WHEN @colAllowNull =1 THEN ' = NULL' ELSE '' END + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersInsertForExec = @ColumnParametersInsertForExec + @AffectationForExec
END
IF @ColMatchCreationUser= 1
BEGIN
IF LEN(@ParameterForUser)>1
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'ISNULL(' + @ParameterForUser + ',SYSTEM_USER)'
ELSE
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'SYSTEM_USER'
END
ELSE
BEGIN
IF @ColMatchCreationDate= 1
Set @ColumnInValueForInsert = @ColumnInValueForInsert + 'GETDATE()'
ELSE
IF @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
Set @ColumnInValueForInsert = @ColumnInValueForInsert + @colVariable
END
IF @ColMatchCreationUser= 1 OR @ColMatchCreationDate= 1 OR @ColMatchModificationUser = 0 AND @ColMatchModificationDate = 0
SET @ColumnInValueForInsert =@ColumnInValueForInsert + ',' + CHAR(13) + CHAR(10) + @space + @space+ @spaceForTrans
Set @tableCols = @tableCols + @colName + ',' ;
IF @ColMatchModificationUser = 1
BEGIN
IF LEN(@ParameterForUser)>1
Set @updCols = @updCols + @colName + ' = ISNULL(' + @ParameterForUser + ',SYSTEM_USER)';
ELSE
Set @updCols = @updCols + @colName + ' = SYSTEM_USER';
END
ELSE
BEGIN
IF @ColMatchModificationDate = 1
Set @updCols = @updCols + @colName + ' = GETDATE()';
ELSE
IF @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
Set @updCols = @updCols + @colName + ' = ' + @colVariable;
END
IF @ColMatchModificationUser = 1 OR @ColMatchModificationDate = 1 OR @ColMatchCreationUser=0 AND @ColMatchCreationDate=0
SET @updCols =@updCols + ',' + CHAR(13) + CHAR(10) + @space + @space+ ' ' + @spaceForTrans
END
SET @ColumnParametersList = @ColumnParametersList + @colParameter + ' = NULL' + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersListForExec = @ColumnParametersListForExec+ @colVariable + ' = NULL, --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
BEGIN
SET @SetVariablesForExecUpdate = CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MIN(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
SET @AffectationForExec = @colVariable + '= @PrimaryKeyValue, --Type ' + @DataType + CHAR(13) + CHAR(10) + @space
END
IF @ColIsComputed = 0 AND @ColMatchCreationUser=0 AND @ColMatchCreationDate=0 AND @ColMatchModificationUser=0 AND @ColMatchModificationDate=0
BEGIN
Set @ColumnParametersUpdate = @ColumnParametersUpdate + @colParameter + ',' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersUpdateForExec = @ColumnParametersUpdateForExec + @AffectationForExec
END
IF @DataType NOT IN ('text')
BEGIN
IF @DataType NOT IN ('Xml')
BEGIN
SET @tableColumnForWhereInList = @tableColumnForWhereInList + '
IF ' + @colVariable + ' IS NOT NULL
BEGIN
SET @Statement= @Statement+ @Separator + ''' + REPLACE(@colName,'''','''''') + '= '+ @colVariableProc +'''
SET @Separator = @SeparatorAnd
END'
SET @tableColumnForWhereInListVariables = @tableColumnForWhereInListVariables + @space + @space + @space + @spaceForTrans + @colVariableProc + ' ' + @DataType +',
'
SET @tableColumnForWhereInListAffectVariables = @tableColumnForWhereInListAffectVariables + @space + @space + @space + @spaceForTrans + @colVariableProc + '=' + @colVariable + ',
'
END
SET @DebugVariablesForList = @DebugVariablesForList+ CHAR(13) +CHAR(10) + @space + @space + @space + @space +@space+ @spaceForTrans
IF @DataType IN ('Xml')
SET @DebugVariablesForList = @DebugVariablesForList+ 'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = CAST('''''' + REPLACE(CAST(' +@colVariable + ' as nvarchar(max)),'''''''','''''''''''') + ''''''AS XML);''+CHAR(13)+CHAR(10) ,'''') + '
ELSE
SET @DebugVariablesForList = @DebugVariablesForList+ 'ISNULL(''DECLARE '+ @colVariableProc + ' ' + @DataType+' = '''''' + REPLACE(' +@colVariable + ','''''''','''''''''''') + '''''';''+CHAR(13)+CHAR(10) ,'''') + '
END
IF @colIsPrimaryKey= 1
BEGIN
IF @ColIsIdentityAutoIncrement = 1 AND @DataType='int'
BEGIN
SET @SetVariablesForExecDelete = CHAR(13)+CHAR(10)+'DECLARE @PrimaryKeyValue INT= (SELECT MAX(' + @colName + ') FROM ' + @CurrentFullTableName + ')'
END
SET @ColumnParametersDelete = @ColumnParametersDelete + @colParameter +', ' + CHAR(13) + CHAR(10) + @space ;
SET @ColumnParametersDeleteForExec = @ColumnParametersDeleteForExec + @AffectationForExec
SET @whereCols = @whereCols + @colName + ' = ' + @colVariable + ' AND ' ;
SET @NbPrimaryKey = @NbPrimaryKey +1
SET @LastPrimaryKey = @colName
END
FETCH NEXT FROM SpText_Cursor INTO @colName, @colVariable, @DataType,@colParameter, @colAllowNull,@colIsPrimaryKey,@ColIsIdentityAutoIncrement,@ColLength,@ColIsComputed,@ColMatchCreationUser,@ColMatchCreationDate,@ColMatchModificationUser,@ColMatchModificationDate
END
CLOSE SpText_Cursor
DEALLOCATE SpText_Cursor
IF @ColumnDefForInsert IS NULL
RAISERROR('@ColumnDefForInsert IS NULL',16,1)
IF @ColumnParametersInsert IS NULL
RAISERROR('@ColumnParametersInsert IS NULL',16,1)
IF @ColumnParametersInsertForExec IS NULL
RAISERROR('@ColumnParametersInsertForExec IS NULL',16,1)
IF @ColumnInValueForInsert IS NULL
RAISERROR('@ColumnInValueForInsert IS NULL',16,1)
IF @tableCols IS NULL
RAISERROR('@tableCols IS NULL',16,1)
IF @updCols IS NULL
RAISERROR('@updCols IS NULL',16,1)
IF @ColumnParametersDelete IS NULL
RAISERROR('@ColumnParametersDelete IS NULL',16,1)
IF @whereCols IS NULL
RAISERROR('@whereCols IS NULL',16,1)
DECLARE @LastPosOfComma INT
If (LEN(@ColumnParametersUpdate)>0)
BEGIN
Set @ColumnParametersUpdate = LEFT(@ColumnParametersUpdate,LEN(@ColumnParametersUpdate)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersUpdateForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersUpdateForExec))
SET @ColumnParametersUpdateForExec = LEFT(@ColumnParametersUpdateForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersUpdateForExec,@LastPosOfComma+5,40000);
END
--See next post for the end of procedure
-- Here end end of the procedure (started on another post)
If (LEN(@ColumnParametersInsert)>0)
Begin
Set @ColumnParametersInsert = LEFT(@ColumnParametersInsert,LEN(@ColumnParametersInsert)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersInsertForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersInsertForExec))
SET @ColumnParametersInsertForExec = LEFT(@ColumnParametersInsertForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersInsertForExec,@LastPosOfComma+5,40000);
Set @ColumnParametersDelete = LEFT(@ColumnParametersDelete,LEN(@ColumnParametersDelete)-4) ;
SET @LastPosOfComma = LEN(@ColumnParametersDeleteForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersDeleteForExec))
SET @ColumnParametersDeleteForExec = LEFT(@ColumnParametersDeleteForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersDeleteForExec,@LastPosOfComma+5,40000);
SET @ColumnParametersList = LEFT(@ColumnParametersList,LEN(@ColumnParametersList)-3) ;
SET @LastPosOfComma = LEN(@ColumnParametersListForExec) - CHARINDEX(' ,',REVERSE(@ColumnParametersListForExec))
SET @ColumnParametersListForExec = LEFT(@ColumnParametersListForExec,@LastPosOfComma+3) + SUBSTRING(@ColumnParametersListForExec,@LastPosOfComma+5,40000);
IF LEN(@ColumnInValueForInsert)>0
Set @ColumnInValueForInsert = LEFT(@ColumnInValueForInsert,LEN(@ColumnInValueForInsert)-3) ;
IF LEN(@ColumnDefForInsert)>0
Set @ColumnDefForInsert = LEFT(@ColumnDefForInsert,LEN(@ColumnDefForInsert)-3) ;
IF LEN(@tableCols)>0
Set @tableCols = LEFT(@tableCols,LEN(@tableCols)-1) ;
IF LEN(@updCols)>0
Set @updCols = LEFT(@updCols,LEN(@updCols)-3) ;
SET @tableColumnForWhereInListVariables = LEFT(@tableColumnForWhereInListVariables,LEN(@tableColumnForWhereInListVariables)-3)
SET @tableColumnForWhereInListAffectVariables = LEFT(@tableColumnForWhereInListAffectVariables,LEN(@tableColumnForWhereInListAffectVariables)-3) ;
END
If (LEN(@whereCols)>0)
Set @whereCols = 'WHERE ' + LEFT(@whereCols,LEN(@whereCols)-4) ;
ELSE
Set @whereCols = 'WHERE 1=0 --Too dangerous to do update or delete on all the table'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Insert Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@insertSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @insertSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @insertSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersInsert
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'INSERT INTO ' + @CurrentFullTableName + '('
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + ' ' + '' + @ColumnDefForInsert + ')'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'VALUES ('
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + ' ' + '' + @ColumnInValueForInsert + ')'
IF @NbPrimaryKey =1 --No return if 2 or 0 primarykeys
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans + 'SELECT SCOPE_IDENTITY() AS ' + @LastPrimaryKey
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @insertSPName + ' ' + @ColumnParametersInsertForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Insert',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Update Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@updateSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @updateSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @updateSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersUpdate
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ 'UPDATE ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ ' SET ' + @updCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @spaceForTrans+ ' ' + @whereCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExec + @SetVariablesForExecUpdate
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @updateSPName + ' ' + @ColumnParametersUpdateForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' '
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Update',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : Delete Procedure for ' + @CurrentTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@deleteSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @deleteSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersDelete
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + 'DELETE FROM ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' ' + @whereCols
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + @SetVariablesForExecDelete
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @deleteSPName + ' ' + @ColumnParametersDeleteForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1 DESC'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'Delete',@strSPText)
Set @strSPText = ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Author : ' + SYSTEM_USER
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Create date : ' + Convert(varchar(20),Getdate())
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- Description : List Procedure for ' + @CurrentFullTableName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '-- ============================================='
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'IF OBJECT_ID(''' + REPLACE(@listSPName,'''','''''') + ''',''P'') IS NOT NULL'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ' DROP PROCEDURE ' + @listSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + ''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'CREATE PROCEDURE ' + @listSPName
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + '' + @ColumnParametersList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strBegin
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @Separator nvarchar(20) =''
WHERE '''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @SeparatorAnd nvarchar(20) =''
AND '''
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space +' DECLARE @Statement nvarchar(max) =''SELECT *
FROM ' + REPLACE(@CurrentFullTableName,'''','''''') + '''' + @tableColumnForWhereInList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --PRINT @Statement'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN TRY'
IF @GenerateDebugScriptForList=1
BEGIN
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' IF 1=0--DEBUG --TODO: verify if not set for final version'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' DECLARE @FullQueryForDebug nvarchar(max)=' + @DebugVariablesForList
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' CHAR(13) +CHAR(10) + @Statement'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' '
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --PRINT @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --EXEC [K2FranceDebugDB].dbo.K2FranceDebug ''@FullQueryForDebug DIRECT'', @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' --EXEC loopbackServerForDebug.[K2FranceDebugDB].dbo.K2FranceDebug ''@FullQueryForDebug loopback'', @FullQueryForDebug'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END'
END
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' exec sp_executesql @Statement ,N''' + @tableColumnForWhereInListVariables + ''',' + @tableColumnForWhereInListAffectVariables
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END TRY'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' BEGIN CATCH'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' DECLARE @ErrorToDisplay nvarchar(max)= ''Error trying to execute Query Error number:'' + CAST(ERROR_NUMBER() AS nvarchar(max)) +
--'' Error severity:'' + ISNULL(CAST(ERROR_SEVERITY() AS nvarchar(max)),'''') +
--'' Error state:'' + ISNULL(CAST(ERROR_STATE() AS nvarchar(max)),'''') +
--'' Error procedure:'' + ISNULL(CAST(ERROR_PROCEDURE() AS nvarchar(max)),'''') +
--'' Error line:'' + ISNULL(CAST(ERROR_LINE() AS nvarchar(max)),'''') +
'' Error message:'' + ISNULL(CAST(ERROR_MESSAGE() AS nvarchar(max)),'''')
'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' RAISERROR(@ErrorToDisplay, 16, 1);'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @space + @space + @spaceForTrans + ' END CATCH'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + @strEnd
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'EXEC ' + @listSPName + ' ' + @ColumnParametersListForExec
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'GO'
Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + 'SELECT * FROM ' + @CurrentFullTableName + ' ORDER BY 1'
IF @UnCommentExecForDebug = 0 Set @strSPText = @strSPText + CHAR(13) + CHAR(10) + '*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES (@CurrentFullTableName,'List',@strSPText)
Drop table #tmp_Structure
Fetch next from Tables_cursor INTO @CurrentSchemaName,@CurrentTableName
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
SET @DropStatement = '
------------------------------------------- TO CLEAN COMPLETELY THE APPLICATION ---------------------------------------
/*' + @DropStatement +'
*/'
INSERT INTO @StatementList (FullTableName,StatementType,Statement) VALUES ('Common','Drop statement to put at the end of final script',@DropStatement)
SELECT * FROM @StatementList
ORDER BY 1
END
GO
--For all tables of schema dbo of database "OlivierDb":
EXEC dbo.GenerateDynamicallyProceduresForTables 'OlivierDB','dbo'
--With all possible parameters:
EXEC dbo.GenerateDynamicallyProceduresForTables @DatabaseName = 'OlivierDB',
@SchemaName = 'dbo',
@TableName = 'Table5',
@NoCount = 1,
@ManageTransaction = 1,
@GenerateDebugScriptForList = 1,
@ParameterForUser = '@UserInP',
@ParameterForCulture = '@CultureInP',
@FirstParametersAreMandatory= 1,
@ProcedureTemplateName = '[{SchemaName}].[{TableName}_Proc_{ActionType}]',
@ColumnNameLimitation = '', --(syscolumns.name LIKE ''%Creation%'' OR syscolumns.name IN (''SomeInt'',''Somebit'') )
@CreationUserMatch = 'syscolumns.name LIKE ''%CreationUser%'' OR syscolumns.name LIKE ''%CreationBy%''',
@CreationDateMatch = 'syscolumns.name LIKE ''%CreationDate%'' OR syscolumns.name LIKE ''%CreatedDate%''',
@ModificationUserMatch = 'syscolumns.name LIKE ''%ModificationUser%'' OR syscolumns.name LIKE ''%ModifiedBy%'' OR syscolumns.name LIKE ''%ModifiedUser%''',
@ModificationDateMatch = 'syscolumns.name LIKE ''%ModificationDate%'' OR syscolumns.name LIKE ''%ModifiedDate%'''
Related