Generate Insert/Update/Delete Sprocs for a table
A week ago I posted this script. It was a much simpeler version without error checking. Now all the sprocs return the same errors.
0 THERE WERE NO ERRORS
-99 UNEXPECTED NR OF RECORDS AFFECTED
0 THE SQL ERROR NUMBER
Also with the insert statement the @ID is set. So you can retrieve the Identity of the last row inserted.
The construct with the returnvalue could be simpeler but then we could not use it with the .net applicationblocks.
This script will generate Insert,Update,Delete,Get stored procedures for a given table.
It will only work if the Primary key is also an Identity field.
If you like it vote for it.......
SET NOCOUNT ON
-- DECLARE THE NAME OF THE TABLE
DECLARE @TBL SYSNAME
SET @TBL = 'RESOURCES'
-- DECLARE THE VARIABLES
DECLARE @MAXITEMS AS INTEGER
DECLARE @COUNTER AS INTEGER
DECLARE @COLUMNNAME AS SYSNAME
DECLARE @COLUMNTYPE AS SYSNAME
DECLARE @ISINPRIKEY AS BIT
DECLARE @ANDVAR AS INTEGER
DECLARE @COUNTPRIKEYS AS INTEGER
DECLARE @COLUMNLENGT AS INTEGER
DECLARE @COLUMNDATATYPE AS SYSNAME
DECLARE @VBREALNAME AS SYSNAME
DECLARE @VBTYPE AS SYSNAME
DECLARE @SQLDBTYPE AS SYSNAME
-- CREATE AN ARRAY TO HOLD THE TABLE FIELDS
DECLARE @MEMARRAY TABLE
( ID INT IDENTITY,
COLUMN_NAME SYSNAME,
COLUMN_TYPE SYSNAME,
PRIMARYKEYFIELD BIT,
MEMVAR_NAME SYSNAME,
CCOLUMN_NAME SYSNAME,
COLUMN_LENGTH INTEGER,
COLUMN_DATATYPE SYSNAME,
VBREALNAME SYSNAME,
VBTYPE SYSNAME,
SQLDBTYPE SYSNAME)
-- FILL THE MEMORY TABLE WITH ALL THE FIELDS
INSERT @MEMARRAY (COLUMN_NAME,COLUMN_TYPE,PRIMARYKEYFIELD,MEMVAR_NAME,CCOLUMN_NAME,COLUMN_LENGTH,COLUMN_DATATYPE,VBREALNAME,VBTYPE,SQLDBTYPE) SELECT SC.NAME AS 'COLUMNNAME',
CASE BT.NAME
WHEN 'INT' THEN 'INTEGER'
WHEN 'IMAGE'THEN 'IMAGE'
WHEN 'TEXT' THEN 'TEXT'
WHEN 'VARCHAR' THEN 'VARCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'NVARCHAR' THEN 'NVARCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'NCHAR' THEN 'NCHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'CHAR' THEN 'CHAR('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
WHEN 'VARBINARY' THEN 'VARBINARY('+ RTRIM(LTRIM(STR(SC.LENGTH)))+')'
ELSE TD.NAME
END AS 'COLUMNTYPE',0,'','',SC.LENGTH,BT.NAME,'','',''
FROM SYSCOLUMNS SC
LEFT JOIN SYSTYPES TD ON TD.XUSERTYPE = SC.XUSERTYPE -- AND SYSTYPES.TYPE = SYSTYPES.XTYPE
LEFT JOIN SYSTYPES BT ON BT.XUSERTYPE = TD.XTYPE
WHERE ID = OBJECT_ID(@TBL)
ORDER BY SC.COLID
-- FIGURE OUT IF THERE IS A PRIMARY KEY. THERE SHOULD BE ONE FOR THIS TO WORK.
DECLARE @PK_INDEX SYSNAME
DECLARE @PK_INDID INTEGER
DECLARE @PK_ID AS INTEGER
SELECT @PK_INDEX=NAME FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ = OBJECT_ID(@TBL)
SELECT @PK_INDID = INDID , @PK_ID=ID FROM SYSINDEXES WHERE NAME = @PK_INDEX
-- FIGURE OUT THE COLUMNS IN THE PRIMARY KEY
DECLARE @PK_COLUMNS AS SYSNAME
SET @PK_COLUMNS=''
SET @COUNTER = 1
WHILE INDEX_COL(@TBL,@PK_INDID,@COUNTER) IS NOT NULL
BEGIN
SELECT @PK_COLUMNS=@PK_COLUMNS + '#'+ INDEX_COL(@TBL,@PK_INDID,@COUNTER) + '#,'
SET @COUNTER = @COUNTER + 1
END
-- SET THE PRIMARYKEYFIELD IN THE MEMARRAY
SELECT @MAXITEMS = MAX(ID) FROM @MEMARRAY
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD FROM @MEMARRAY WHERE ID = @COUNTER
IF CHARINDEX('#'+@COLUMNNAME+'#',@PK_COLUMNS) > 0
BEGIN
UPDATE @MEMARRAY SET PRIMARYKEYFIELD = 1 WHERE ID = @COUNTER
END
SET @COUNTER=@COUNTER + 1
END
-- PATCH THE FIELDS MEMVAR_NAME AND CCOLUMN_NAME
DECLARE @MEMVARNAME SYSNAME
DECLARE @CCOLUMNNAME SYSNAME
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
--REPLACE SPACES IN MEMVAR NAME
SET @MEMVARNAME = '@'+REPLACE(@COLUMNNAME,' ','_')
SET @CCOLUMNNAME = '['+ @COLUMNNAME+']'
SET @VBREALNAME = REPLACE(@COLUMNNAME,' ','_')
UPDATE @MEMARRAY SET MEMVAR_NAME = @MEMVARNAME,CCOLUMN_NAME=@CCOLUMNNAME,VBREALNAME=@VBREALNAME WHERE ID = @COUNTER
SET @COUNTER=@COUNTER + 1
END
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO INSERT RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '--'
PRINT '-- RETURN VALUES :'
PRINT '-- 0 THERE WERE NO ERRORS'
PRINT '-- -99 UNEXPECTED NR OF RECORDS AFFECTED'
PRINT '-- <>0 THE SQL ERROR NUMBER '
PRINT '--'
PRINT '-- IF THE INSERT WENT WELL THE IDENTITY FIELD'
PRINT '-- WILL HOLD THE INSERTED ID.'
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_INSERT_'+@TBL
PRINT '('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
BEGIN
IF @COUNTER < @MAXITEMS
BEGIN
IF @ISINPRIKEY = 1
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+ ' = NULL OUT,'
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+ ','
END
ELSE
BEGIN
IF @ISINPRIKEY = 1
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+ ' = NULL OUT,'
END
ELSE
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE + ','
END
PRINT ' @RETURNVALUE INT = NULL OUT'
END
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT ''
PRINT 'AS'
PRINT ''
PRINT 'DECLARE @ERROR_VAR INT'
PRINT 'DECLARE @ROWCOUNT_VAR INT'
PRINT ''
PRINT 'BEGIN TRANSACTION INSERT_'+@TBL
PRINT ''
PRINT 'INSERT '+@TBL + ' ('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@CCOLUMNNAME+','
ELSE
PRINT ' '+@CCOLUMNNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'VALUES ('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME+','
ELSE
PRINT ' '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT ''
PRINT 'SELECT @ERROR_VAR = @@ERROR,@ROWCOUNT_VAR=@@ROWCOUNT'
PRINT ''
PRINT 'IF @ERROR_VAR = 0 AND @ROWCOUNT_VAR = 1'
PRINT ' BEGIN'
PRINT ' COMMIT TRANSACTION INSERT_'+@TBL
PRINT ' SET @RETURNVALUE = 0'
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
PRINT ' SET ' + @MEMVARNAME + ' = @@IDENTITY'
SET @COUNTER=@COUNTER + 1
END
PRINT ' END'
PRINT 'ELSE'
PRINT ' BEGIN'
PRINT ' ROLLBACK TRANSACTION INSERT_'+@TBL
PRINT ' IF @ERROR_VAR <> 0 '
PRINT ' SET @RETURNVALUE = @ERROR_VAR'
PRINT ' ELSE'
PRINT ' SET @RETURNVALUE = -99 -- UNEXPECTED NR OF RECORDS AFFECTED'
PRINT ' END'
PRINT ''
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO UPDATE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '--'
PRINT '-- RETURN VALUES :'
PRINT '-- 0 THERE WERE NO ERRORS'
PRINT '-- -99 UNEXPECTED NR OF RECORDS AFFECTED'
PRINT '-- <>0 THE SQL ERROR NUMBER '
PRINT '--'
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_UPDATE_'+@TBL
PRINT '('
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE + ','
PRINT ' @RETURNVALUE INT = NULL OUT'
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT ''
PRINT 'DECLARE @ERROR_VAR INT'
PRINT 'DECLARE @ROWCOUNT_VAR INT'
PRINT ''
PRINT 'BEGIN TRANSACTION UPDATE_'+@TBL
PRINT ''
PRINT 'UPDATE '+@TBL + ' SET '
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 0 -- SKIP FIELDS IN THE PRIMARY KEY
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ','
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'SELECT @ERROR_VAR = @@ERROR,@ROWCOUNT_VAR=@@ROWCOUNT'
PRINT ''
PRINT 'IF @ERROR_VAR = 0 AND @ROWCOUNT_VAR = 1'
PRINT ' BEGIN'
PRINT ' COMMIT TRANSACTION UPDATE_'+@TBL
PRINT ' SET @RETURNVALUE = 0'
PRINT ' END'
PRINT 'ELSE'
PRINT ' BEGIN'
PRINT ' ROLLBACK TRANSACTION UPDATE_'+@TBL
PRINT ' IF @ERROR_VAR <> 0 '
PRINT ' SET @RETURNVALUE = @ERROR_VAR'
PRINT ' ELSE'
PRINT ' SET @RETURNVALUE = -99 -- UNEXPECTED NR OF RECORDS AFFECTED'
PRINT ' END'
PRINT ''
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO DELETE RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '--'
PRINT '-- RETURN VALUES :'
PRINT '-- 0 THERE WERE NO ERRORS'
PRINT '-- -99 UNEXPECTED NR OF RECORDS AFFECTED'
PRINT '-- <>0 THE SQL ERROR NUMBER '
PRINT '--'
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_DELETE_'+@TBL
PRINT '('
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
ELSE
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+','
PRINT ' @RETURNVALUE INT = NULL OUT'
END
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT ''
PRINT 'DECLARE @ERROR_VAR INT'
PRINT 'DECLARE @ROWCOUNT_VAR INT'
PRINT ''
PRINT 'BEGIN TRANSACTION DELETE_'+@TBL
PRINT ''
PRINT ' DELETE FROM '+@TBL
PRINT ' WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'SELECT @ERROR_VAR = @@ERROR,@ROWCOUNT_VAR=@@ROWCOUNT'
PRINT ''
PRINT 'IF @ERROR_VAR = 0 AND @ROWCOUNT_VAR = 1'
PRINT ' BEGIN'
PRINT ' COMMIT TRANSACTION DELETE_'+@TBL
PRINT ' SET @RETURNVALUE = 0'
PRINT ' END'
PRINT 'ELSE'
PRINT ' BEGIN'
PRINT ' ROLLBACK TRANSACTION DELETE_'+@TBL
PRINT ' IF @ERROR_VAR <> 0 '
PRINT ' SET @RETURNVALUE = @ERROR_VAR'
PRINT ' ELSE'
PRINT ' SET @RETURNVALUE = -99 -- UNEXPECTED NR OF RECORDS AFFECTED'
PRINT ' END'
PRINT ''
PRINT 'GO'
PRINT '-- ============================================='
PRINT '-- STORED PROCEDURE TO GET RECORDS IN '
PRINT '-- THE TABLE '+ @TBL
PRINT '-- GENERATED ON '+CONVERT(VARCHAR(20),GETDATE())
PRINT '--'
PRINT '-- RETURN VALUES :'
PRINT '-- 0 THERE WERE NO ERRORS'
PRINT '-- -99 UNEXPECTED NR OF RECORDS AFFECTED'
PRINT '-- <>0 THE SQL ERROR NUMBER '
PRINT '--'
PRINT '-- ============================================='
PRINT 'CREATE PROCEDURE GS_GET_'+@TBL
PRINT '('
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
BEGIN
IF @COUNTER < @MAXITEMS
BEGIN
IF @ISINPRIKEY = 1
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+ ' OUT,'
ELSE
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+' = NULL OUT,'
END
ELSE
BEGIN
IF @ISINPRIKEY = 1
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+ ' OUT,'
PRINT ' @RETURNVALUE INT = NULL OUT'
END
ELSE
BEGIN
PRINT ' '+@MEMVARNAME+' '+@COLUMNTYPE+' = NULL OUT,'
PRINT ' @RETURNVALUE INT = NULL OUT'
END
END
END
SET @COUNTER=@COUNTER + 1
END
PRINT ')'
PRINT 'AS'
PRINT ''
PRINT 'DECLARE @ERROR_VAR INT'
PRINT 'DECLARE @ROWCOUNT_VAR INT'
PRINT ''
PRINT 'SELECT '
SET @COUNTER = 1
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
BEGIN
IF @COUNTER < @MAXITEMS
PRINT ' '+@MEMVARNAME + ' = '+ @CCOLUMNNAME+','
ELSE
PRINT ' '+@MEMVARNAME + ' = '+ @CCOLUMNNAME
END
SET @COUNTER=@COUNTER + 1
END
PRINT 'FROM '+@TBL
PRINT 'WHERE'
SELECT @ANDVAR = COUNT(*) FROM @MEMARRAY WHERE PRIMARYKEYFIELD = 1
SET @COUNTER = 1
SET @COUNTPRIKEYS = 0
WHILE @COUNTER <= @MAXITEMS
BEGIN
SELECT @COLUMNNAME=COLUMN_NAME,@COLUMNTYPE=COLUMN_TYPE,@ISINPRIKEY=PRIMARYKEYFIELD,@MEMVARNAME=MEMVAR_NAME,@CCOLUMNNAME=CCOLUMN_NAME FROM @MEMARRAY WHERE ID = @COUNTER
IF @ISINPRIKEY = 1
BEGIN
SET @COUNTPRIKEYS=@COUNTPRIKEYS + 1
IF @COUNTPRIKEYS < @ANDVAR
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME + ' AND'
ELSE
PRINT ' '+@CCOLUMNNAME+' = '+@MEMVARNAME
END
SET @COUNTER=@COUNTER + 1
END PRINT ''
PRINT 'SELECT @ERROR_VAR = @@ERROR,@ROWCOUNT_VAR=@@ROWCOUNT'
PRINT ''
PRINT 'IF @ERROR_VAR = 0 AND @ROWCOUNT_VAR = 1'
PRINT ' BEGIN'
PRINT ' SET @RETURNVALUE = 0'
PRINT ' END'
PRINT 'ELSE'
PRINT ' BEGIN'
PRINT ' IF @ERROR_VAR <> 0 '
PRINT ' SET @RETURNVALUE = @ERROR_VAR'
PRINT ' ELSE'
PRINT ' SET @RETURNVALUE = -99 -- UNEXPECTED NR OF RECORDS AFFECTED'
PRINT ' END'
PRINT ''
PRINT 'GO' 
