View the original text, please click here
The following is some of the wonderful code:
Figure 1 generated T-SQL Script
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'prApp_Order_Details_Update') DROP PROC prApp_Order_Details_UpdateGO-- Update a single record in Order_DetailsCREATE PROC prApp_Order_Details_Update @OrderID int, @ProductID int, @UnitPrice money, @Quantity smallint, @Discount realASUPDATE Order_DetailsSET UnitPrice = @ Unitprice, Quantity = @quantity, discount = @discountwhere orderid = @Orderidand productId = @ProductIDGO
Figure 3 UDF
Create function dbo.fntableColumninfo (@stablename varchar (128))
Returns Table
AS
Return
Select c.name as scolumnname,
C.colid as ncolumnid,
DBO.FNISCOLUMNPRIMARYKEY (@stablename, c.name) as bprimaryKeyColumn,
Case
When T.Name in ('char', 'varchar', 'binary', 'varbinary', 'nchar',
'nvarchar') THEN 1
When T.Name in ('Decimal', 'Numeric') THEN 2
ELSE 0
End as 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
Inner Join SystemYpes T on C.XTYPE = T.XTYPE AND
C.USERTYPE = T. IUSERTYPE
WHERE C.ID = Object_ID (@stablename)
Figure 4 set up the variables
CREATE PR__SYS_MAKEUPDATERECORDPROC
@stablename varchar (128),
@Bexecute bit = 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 @sproctext varchar (8000),
@skeyfields varchar (2000),
@ssetclause varchar (2000),
@Swhereclause Varchar (2000),
@scolumnname varchar (128),
@ncolumnid smallint,
@BPrimaryKeyColumn bit,
@nalternateType Int,
@ncolumnLength Int,
@ncolumnPrecision Int,
@ncolumnscale int,
@Isnullable bit,
@IsIndentity 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 @ssetclause = ''
Set @SwhereClause = ''
Figure 5 Set Some Values
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
Figure 6 Declare Cursor and Read
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, @SDEFAULTVALUE
While (@@ fetch_status = 0)
Begin
Figure 7 crete the parameter list
IF (@skeyfields <> ')
Set @skeyfields = @skeyfields ',' @SCRLF
Set @skeyfields = @skeyfields @stab '@' @scolumnname '
' @stypename
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 (@isiDentity = 0)
Begin
IF (@isnullable = 1) or (@stypename = 'TimeStamp')
Set @skeyfields = @skeyfields '= null'
End
Figure 8 Create The Set CLAUSE
IF (@BPrimaryKeyColumn = 0)
Begin
IF (@ssetclause = '')
Set @ssetclause = 'set'
Else
Set @ssetclause = @ssetclause ',' @scrlfset @ssetClause = @SSETCLASE @stab @scolumnname '='
Set @ssetclause = @ssetclause '@' @scolumnname
End
Figure 9 create the where, if neseary
Else
Begin
IF (@swhereclause = '')
Set @Swhereclause = @SwhereClause 'Where'
Else
Set @SwhereClause = @SwherecLaSe 'and'
Set @Swhereclause = @Swhereclause @stab @Scolumnname '= @'
@scolumnname @scrlf
End
Fetch next
From crKeyfields
INTO @scolumnname, @ncolumnid, @BprimaryKeyColumn, @nalternatetype,
@ncolumnlength, @ncolumnprecision, @ncolumnscale, @isnullable,
@IsiDentity, @SDEFAULTVALUE
End
Close Crkeyfields
Deallocate CRKEYFIELDS
Figure 10 Print The Procedure
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 = 1exec (@sproctext)
© 2005 Microsoft Corporation. All Rights Reserved.
