----------------------------- Proc_Delete_begin ------------------------------------- SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CreatePROC pr__SYS_MakeDeleteRecordProc @sTableNamevarchar(128), @bExecutebit=0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) =0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE@sProcTextvarchar(8000), @sKeyFieldsvarchar(2000), @sWhereClausevarchar(2000), @sColumnNamevarchar(128), @nColumnIDsmallint, @bPrimaryKeyColumnbit, @nAlternateTypeint, @nColumnLengthint, @nColumnPrecisionint, @nColumnScaleint, @IsNullablebit, @IsIdentityint, @sTypeNamevarchar(128), @sDefaultValuevarchar(4000), @sCRLFchar(2), @sTABchar(1) SET@sTAB=char(9) SET@sCRLF=char(13) +char(10) SET@sProcText='' SET@sKeyFields='' SET@sWhereClause='' SET@sProcText=@sProcText+'IF EXISTS(Select * FROM sysobjects Where name = ''prApp_'+@sTableName+'_Delete'')'+@sCRLF SET@sProcText=@sProcText+@sTAB+'Drop PROC prApp_'+@sTableName+'_Delete'+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF SET@sProcText=@sProcText+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) SET@sProcText='' SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'-- Delete a single record from '+@sTableName+@sCRLF SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'Create PROC prApp_'+@sTableName+'_Delete'+@sCRLF DECLARE crKeyFields cursorfor Select* FROM dbo.fnTableColumnInfo(@sTableName) orDERBY2 OPEN crKeyFields FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS=0) BEGIN IF (@bPrimaryKeyColumn=1) BEGIN IF (@sKeyFields<>'') SET@sKeyFields=@sKeyFields+','+@sCRLF SET@sKeyFields=@sKeyFields+@sTAB+'@'+@sColumnName+''+@sTypeName IF (@nAlternateType=2) --decimal, numeric SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnPrecisionASvarchar(3)) +', ' +CAST(@nColumnScaleASvarchar(3)) +')' ELSEIF (@nAlternateType=1) --character and binary SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnLengthASvarchar(4)) +')' IF (@sWhereClause='') SET@sWhereClause=@sWhereClause+'Where ' ELSE SET@sWhereClause=@sWhereClause+' AND ' SET@sWhereClause=@sWhereClause+@sTAB+@sColumnName+' = @'+@sColumnName+@sCRLF END FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET@sProcText=@sProcText+@sKeyFields+@sCRLF SET@sProcText=@sProcText+'AS'+@sCRLF SET@sProcText=@sProcText+@sCRLF SET@sProcText=@sProcText+'Delete '+@sTableName+@sCRLF SET@sProcText=@sProcText+@sWhereClause SET@sProcText=@sProcText+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------- Proc_Delete_end ------------------------------------- ----------------------------- Proc_Insert_begin ------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreatePROC pr__SYS_MakeInsertRecordProc @sTableNamevarchar(128), @bExecutebit=0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) =0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE@sProcTextvarchar(8000), @sKeyFieldsvarchar(2000), @sAllFieldsvarchar(2000), @sAllParamsvarchar(2000), @sWhereClausevarchar(2000), @sColumnNamevarchar(128), @nColumnIDsmallint, @bPrimaryKeyColumnbit, @nAlternateTypeint, @nColumnLengthint, @nColumnPrecisionint, @nColumnScaleint, @IsNullablebit, @IsIdentityint, @HasIdentityint, @sTypeNamevarchar(128), @sDefaultValuevarchar(4000), @sCRLFchar(2), @sTABchar(1) SET@HasIdentity=0 SET@sTAB=char(9) SET@sCRLF=char(13) +char(10) SET@sProcText='' SET@sKeyFields='' SET@sAllFields='' SET@sWhereClause='' SET@sAllParams='' SET@sProcText=@sProcText+'IF EXISTS(Select * FROM sysobjects Where name = ''prApp_'+@sTableName+'_Insert'')'+@sCRLF SET@sProcText=@sProcText+@sTAB+'Drop PROC prApp_'+@sTableName+'_Insert'+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF SET@sProcText=@sProcText+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) SET@sProcText='' SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'-- Insert a single record into '+@sTableName+@sCRLF SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'Create PROC prApp_'+@sTableName+'_Insert'+@sCRLF DECLARE crKeyFields cursorfor Select* FROM dbo.fnTableColumnInfo(@sTableName) orDERBY2 OPEN crKeyFields FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS=0) BEGIN IF (@IsIdentity=0) BEGIN IF (@sKeyFields<>'') SET@sKeyFields=@sKeyFields+','+@sCRLF SET@sKeyFields=@sKeyFields+@sTAB+'@'+@sColumnName+''+@sTypeName IF (@sAllFields<>'') BEGIN SET@sAllParams=@sAllParams+', ' SET@sAllFields=@sAllFields+', ' END IF (@sTypeName='timestamp') SET@sAllParams=@sAllParams+'NULL' ELSEIF (@sDefaultValueISNOTNULL) SET@sAllParams=@sAllParams+'COALESCE(@'+@sColumnName+', '+@sDefaultValue+')' ELSE SET@sAllParams=@sAllParams+'@'+@sColumnName SET@sAllFields=@sAllFields+@sColumnName END ELSE BEGIN SET@HasIdentity=1 END IF (@nAlternateType=2) --decimal, numeric SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnPrecisionASvarchar(3)) +', ' +CAST(@nColumnScaleASvarchar(3)) +')' ELSEIF (@nAlternateType=1) --character and binary SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnLengthASvarchar(4)) +')' IF (@IsIdentity=0) BEGIN IF (@sDefaultValueISNOTNULL) or (@IsNullable=1) or (@sTypeName='timestamp') SET@sKeyFields=@sKeyFields+' = NULL' END FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET@sProcText=@sProcText+@sKeyFields+@sCRLF SET@sProcText=@sProcText+'AS'+@sCRLF SET@sProcText=@sProcText+@sCRLF SET@sProcText=@sProcText+'Insert '+@sTableName+'('+@sAllFields+')'+@sCRLF SET@sProcText=@sProcText+'VALUES ('+@sAllParams+')'+@sCRLF SET@sProcText=@sProcText+@sCRLF IF (@HasIdentity=1) BEGIN SET@sProcText=@sProcText+'RETURN SCOPE_IDENTITY()'+@sCRLF SET@sProcText=@sProcText+@sCRLF END IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------- Proc_Insert_end ------------------------------------- ----------------------------- Proc_Select_begin ------------------------------------- SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO CreatePROC pr__SYS_MakeSelectRecordProc @sTableNamevarchar(128), @bExecutebit=0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) =0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE@sProcTextvarchar(8000), @sKeyFieldsvarchar(2000), @sSelectClausevarchar(2000), @sWhereClausevarchar(2000), @sColumnNamevarchar(128), @nColumnIDsmallint, @bPrimaryKeyColumnbit, @nAlternateTypeint, @nColumnLengthint, @nColumnPrecisionint, @nColumnScaleint, @IsNullablebit, @IsIdentityint, @sTypeNamevarchar(128), @sDefaultValuevarchar(4000), @sCRLFchar(2), @sTABchar(1) SET@sTAB=char(9) SET@sCRLF=char(13) +char(10) SET@sProcText='' SET@sKeyFields='' SET@sSelectClause='' SET@sWhereClause='' SET@sProcText=@sProcText+'IF EXISTS(Select * FROM sysobjects Where name = ''prApp_'+@sTableName+'_Select'')'+@sCRLF SET@sProcText=@sProcText+@sTAB+'Drop PROC prApp_'+@sTableName+'_Select'+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF SET@sProcText=@sProcText+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) SET@sProcText='' SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'-- Select a single record from '+@sTableName+@sCRLF SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'Create PROC prApp_'+@sTableName+'_Select'+@sCRLF DECLARE crKeyFields cursorfor Select* FROM dbo.fnTableColumnInfo(@sTableName) orDERBY2 OPEN crKeyFields FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS=0) BEGIN IF (@bPrimaryKeyColumn=1) BEGIN IF (@sKeyFields<>'') SET@sKeyFields=@sKeyFields+','+@sCRLF SET@sKeyFields=@sKeyFields+@sTAB+'@'+@sColumnName+''+@sTypeName IF (@nAlternateType=2) --decimal, numeric SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnPrecisionASvarchar(3)) +', ' +CAST(@nColumnScaleASvarchar(3)) +')' ELSEIF (@nAlternateType=1) --character and binary SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnLengthASvarchar(4)) +')' IF (@sWhereClause='') SET@sWhereClause=@sWhereClause+'Where ' ELSE SET@sWhereClause=@sWhereClause+' AND ' SET@sWhereClause=@sWhereClause+@sTAB+@sColumnName+' = @'+@sColumnName+@sCRLF END IF (@sSelectClause='') SET@sSelectClause=@sSelectClause+'Select' ELSE SET@sSelectClause=@sSelectClause+','+@sCRLF SET@sSelectClause=@sSelectClause+@sTAB+@sColumnName FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET@sSelectClause=@sSelectClause+@sCRLF SET@sProcText=@sProcText+@sKeyFields+@sCRLF SET@sProcText=@sProcText+'AS'+@sCRLF SET@sProcText=@sProcText+@sCRLF SET@sProcText=@sProcText+@sSelectClause SET@sProcText=@sProcText+'FROM '+@sTableName+@sCRLF SET@sProcText=@sProcText+@sWhereClause SET@sProcText=@sProcText+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------- Proc_Select_end ------------------------------------- ----------------------------- Proc_Update_begin ------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreatePROC pr__SYS_MakeUpdateRecordProc @sTableNamevarchar(128), @bExecutebit=0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) =0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE@sProcTextvarchar(8000), @sKeyFieldsvarchar(2000), @sSetClausevarchar(2000), @sWhereClausevarchar(2000), @sColumnNamevarchar(128), @nColumnIDsmallint, @bPrimaryKeyColumnbit, @nAlternateTypeint, @nColumnLengthint, @nColumnPrecisionint, @nColumnScaleint, @IsNullablebit, @IsIdentityint, @sTypeNamevarchar(128), @sDefaultValuevarchar(4000), @sCRLFchar(2), @sTABchar(1) SET@sTAB=char(9) SET@sCRLF=char(13) +char(10) SET@sProcText='' SET@sKeyFields='' SET@sSetClause='' SET@sWhereClause='' SET@sProcText=@sProcText+'IF EXISTS(Select * FROM sysobjects Where name = ''prApp_'+@sTableName+'_Update'')'+@sCRLF SET@sProcText=@sProcText+@sTAB+'Drop PROC prApp_'+@sTableName+'_Update'+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF SET@sProcText=@sProcText+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) SET@sProcText='' SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'-- Update a single record in '+@sTableName+@sCRLF SET@sProcText=@sProcText+'----------------------------------------------------------------------------'+@sCRLF SET@sProcText=@sProcText+'Create PROC prApp_'+@sTableName+'_Update'+@sCRLF DECLARE crKeyFields cursorfor Select* FROM dbo.fnTableColumnInfo(@sTableName) orDERBY2 OPEN crKeyFields FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS=0) BEGIN IF (@sKeyFields<>'') SET@sKeyFields=@sKeyFields+','+@sCRLF SET@sKeyFields=@sKeyFields+@sTAB+'@'+@sColumnName+''+@sTypeName IF (@nAlternateType=2) --decimal, numeric SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnPrecisionASvarchar(3)) +', ' +CAST(@nColumnScaleASvarchar(3)) +')' ELSEIF (@nAlternateType=1) --character and binary SET@sKeyFields=@sKeyFields+'('+CAST(@nColumnLengthASvarchar(4)) +')' IF (@bPrimaryKeyColumn=1) BEGIN IF (@sWhereClause='') SET@sWhereClause=@sWhereClause+'Where ' ELSE SET@sWhereClause=@sWhereClause+' AND ' SET@sWhereClause=@sWhereClause+@sTAB+@sColumnName+' = @'+@sColumnName+@sCRLF END ELSE IF (@IsIdentity=0) BEGIN IF (@sSetClause='') SET@sSetClause=@sSetClause+'SET' ELSE SET@sSetClause=@sSetClause+','+@sCRLF SET@sSetClause=@sSetClause+@sTAB+@sColumnName+' = ' IF (@sTypeName='timestamp') SET@sSetClause=@sSetClause+'NULL' ELSEIF (@sDefaultValueISNOTNULL) SET@sSetClause=@sSetClause+'COALESCE(@'+@sColumnName+', '+@sDefaultValue+')' ELSE SET@sSetClause=@sSetClause+'@'+@sColumnName END IF (@IsIdentity=0) BEGIN IF (@IsNullable=1) or (@sTypeName='timestamp') SET@sKeyFields=@sKeyFields+' = NULL' END FETCHNEXT FROM crKeyFields INTO@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET@sSetClause=@sSetClause+@sCRLF SET@sProcText=@sProcText+@sKeyFields+@sCRLF SET@sProcText=@sProcText+'AS'+@sCRLF SET@sProcText=@sProcText+@sCRLF SET@sProcText=@sProcText+'Update '+@sTableName+@sCRLF SET@sProcText=@sProcText+@sSetClause SET@sProcText=@sProcText+@sWhereClause SET@sProcText=@sProcText+@sCRLF IF@bExecute=0 SET@sProcText=@sProcText+'GO'+@sCRLF PRINT@sProcText IF@bExecute=1 EXEC (@sProcText) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------- Proc_Update_end ------------------------------------- ----------------------------- Function_begin ------------------------------------- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreateFUNCTION dbo.fnCleanDefaultValue(@sDefaultValuevarchar(4000)) RETURNSvarchar(4000) AS BEGIN RETURNSubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreateFUNCTION dbo.fnColumnDefault(@sTableNamevarchar(128), @sColumnNamevarchar(128)) RETURNSvarchar(4000) AS BEGIN DECLARE@sDefaultValuevarchar(4000) Select@sDefaultValue= dbo.fnCleanDefaultValue(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME =@sTableName AND COLUMN_NAME =@sColumnName RETURN@sDefaultValue END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreateFUNCTION dbo.fnIsColumnPrimaryKey(@sTableNamevarchar(128), @nColumnNamevarchar(128)) RETURNSbit AS BEGIN DECLARE@nTableIDint, @nIndexIDint, @iint SET@nTableID=OBJECT_ID(@sTableName) Select@nIndexID= indid FROM sysindexes Where id =@nTableID AND indid BETWEEN1And254 AND (status &2048) =2048 IF@nIndexIDIsNull RETURN0 IF@nColumnNameIN (Select sc.[name] FROM sysindexkeys sik INNERJOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid Where sik.id =@nTableID AND sik.indid =@nIndexID) BEGIN RETURN1 END RETURN0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreateFUNCTION dbo.fnTableColumnInfo(@sTableNamevarchar(128)) RETURNSTABLE AS RETURN Select c.name AS sColumnName, c.colid AS nColumnID, dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn, CASEWHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN1 WHEN t.name IN ('decimal', 'numeric') THEN2 ELSE0 ENDAS nAlternateType, c.length AS nColumnLength, c.prec AS nColumnPrecision, c.scale AS nColumnScale, c.IsNullable, SIGN(c.status &128) AS IsIdentity, t.name as sTypeName, dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue FROM syscolumns c INNERJOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype Where c.id =OBJECT_ID(@sTableName) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CreateFUNCTION dbo.fnTableHasPrimaryKey(@sTableNamevarchar(128)) RETURNSbit AS BEGIN DECLARE@nTableIDint, @nIndexIDint SET@nTableID=OBJECT_ID(@sTableName) Select@nIndexID= indid FROM sysindexes Where id =@nTableID AND indid BETWEEN1And254 AND (status &2048) =2048 IF@nIndexIDISNOTNull RETURN1 RETURN0 END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ----------------------------- Function_end -------------------------------------