This improvement purpose is to unify input, the operation of the same table, when the external program is called, only one code can be implemented, and then replenishment example code.
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [pr__SYS_MakeDeleteRecordProc]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [pr__SYS_MakeDeleteRecordProc] GO
Create proc PR__sys_makedeleterecordproc @stablename varchar (128), @bexecute bit = 0as
If DBO.FNTABLEHASPRIMARYKEYKEY (@stablename) = 0 Begin Raiserror ('Procedure Cannot Be CREATED ON A Table with no primary key.', 10, 1) Return End
Declare @sprocText Varchar (8000), @skeyfields varchar (2000), @Swhereclause VARCHAR (2000), -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------- @sallfields varchar (2000), @Sallparams Varchar (2000), @Biswhere Bit, --------------- ------------------------------
@sColumnName varchar (128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar (128), @sDefaultValue varchar ( 4000), @scrlf char (2), @stab char (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 =' 'up_' @sTableName '_Delete' ')' @sCRLFSET @sProcText = @sProcText @sTAB 'DROP PROC up_' @stablename '_delete' @scrlf @Bexecute = 0 set @sproctext = @sprocText 'Go' @scrlfset @sprockText = @sproctext @SCRLF
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Set @sproctext = '' set @sproctext = @sproctext '------------------------------------ --------------------------------------- ' @scrlfset @SPROCTEXT = @SprockText '- delete a single record from' @Stablename @scrlfset @sproctext = @sproctext '------------------------------------------------------------------------------------------ ------------------------------------------------ ' @scrlfset @SPROCTEXT = @SPROCTEXT 'CREATE PROC UP_' @stablename '_delete' @scrlf
Declare CRKEYFIELDS CURSOR for Select * from dbo.FntableColumninfo (@stablename) Order by 2
Open crKeyfields
FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@ FETCH_STATUS = 0) BEGIN ---- -------------------------------------------------- --------------------------------------------- IF (@skeyfields < > '') Set @skeyfields = @skeyfields ',' @scrlfset @skeyfields = @Skeyfields @STAB '@P_' @Scolumnname '' @StypeName
IF (@sallfields <> ') begin set @sallparams = @sallparams ', 'set @sallfields = @sallfields ', 'end
IF (@sTypeName = 'timestamp') SET @sAllParams = @sAllParams 'NULL' ELSE IF (@sDefaultValue IS NOT NULL) SET @sAllParams = @sAllParams 'COALESCE (@p_' @sColumnName ',' @ SDEFAULTVALUE ')' else set @sallparams = @Sallparams '@P_' @scolumnname
Set @sallfields = @sallfields @scolumnname
-------------------------------------------------- -------------------------------------------------
IF (@nalternateType = 2) - Decimal, Numeric set @skeyfields = @SKEYFIELDS '(' Cast (@ncolumnprecision as varchar (3)) ',' Cast (@ncolumnscale as varchar (3)) ' ) '
Else if (@nalternateType = 1) - Character and binary set @skeyfields = @skeyfields '(' Cast (@ncolumnlength as varchar (4)) ')' IF (@sdefaultvalue is not null) or (@isnullable = 1) or (@stypename = 'timestamp') set @skeyfields = @skeyfields '= null' - set @skeyfields = @SKEYFIELDS '= 0' -------------- -------------------------------------------------- -----------------------------------
IF (@bPrimaryKeyColumn = 1) BEGIN / * IF (@sKeyFields <> '') SET @sKeyFields = @sKeyFields ',' @sCRLF SET @sKeyFields = @sKeyFields @sTAB '@p_' @sColumnName '' @ stylepe = 2) - Decimal, Numeric set @skeyfields = @SKEYFIELDS '(' Cast (@ncolumnprecision as varchar (3)) ',' Cast (@ncolumnscale as VARCHAR (3)) ')' Else if (@nalternatetype = 1) - Character and binary set @skeyfields = @SKEYFIELDS '(' Cast (@ncolumnlength as varchar (4)) ')' IF (@ Swhereclause = '') set @Swhereclause = @Swhereclause 'Where' else set @Swhereclause = @Swhereclause 'and'
Set @Swhereclause = @SwhereClause @stab @scolumnname '= @P_' @scolumnname @SCRLF END
FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue ENDCLOSE crKeyFieldsDEALLOCATE crKeyFields
SET @sProcText = @sProcText @sKeyFields @sCRLFSET @sProcText = @sProcText 'AS' @sCRLFSET @sProcText = @sProcText @sCRLFSET @sProcText = @sProcText 'DELETE' @sTableName @sCRLFSET @sProcText = @SprockText @SwhereClausset @SPROCTEXT = @SPROCTEXT @ScrlfiF @Bexecute = 0 set @sprocktext = @SPROCTEXT 'Go' @SCRLF
Print @sproctext
IF @bexecute = 1 exec (@sproctext)
Goset quoted_identifier off goset ANSI_NULLS ON Go
Set quoted_identifier on goset ANSI_NULLS ON GO
