SQL Server (T-SQL)

Code to create an update statement:

-- =============================================
-- 
-- =============================================
-- 1/1/1900 created
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = N'sp_update' 
    AND    type = 'P')
    DROP PROCEDURE sp_update
GO


CREATE PROCEDURE sp_update 
-- sp_update 'cn20100'


@tablename varchar(50)



AS


set nocount on
 
declare @output varchar(8000)
declare @fieldTypeVB varchar(50)
declare @fieldName varchar(500)
declare @fieldType varchar(50)
declare @paramName varchar(255)
declare @paramList varchar(8000)
declare @paramListVB varchar(8000)
declare @paramListHelper varchar(8000)
declare @vchrProcName varchar(255),
 @vchrSetList varchar(8000),
 @intColStat int


set @output = ''


select
 @paramList = '',
 @vchrSetList = '',
 @paramListVB = '',
 @paramListHelper = '',
 @output = '',
 @vchrProcName = '_4P_' + upper(@tablename) + '_UPD',
 @tableName = upper(@tableName)



print 'IF EXISTS (SELECT name '
print '    FROM   sysobjects '
print '    WHERE  name = N' + char(39) + @tablename + char(39)
print '        AND type = ' + char(39) + 'P' + char(39) + ')'
print '    DROP PROCEDURE ' + @vchrProcName
print 'GO'
print ''
print 'CREATE PROCEDURE ' + @vchrProcName


declare curFields CURSOR for
select syscolumns.name,
  dbo.f_4P_columnType (xusertype,length, xprec , xscale),
  dbo.f_4P_columnTypeVB (xusertype),
  <a href="mailto:'@'">'@'</a> + syscolumns.name,
  syscolumns.colstat
 from sysobjects
  left join syscolumns on syscolumns.id = sysobjects.id
 where sysobjects.name = @tablename
--  and colstat <> 1



DECLARE @vchrWhere varchar(1000)
set @vchrWhere = ''


OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType, @fieldTypeVB,@paramName, @intColstat
WHILE @@FETCH_STATUS = 0
BEGIN
 --build a where clause
 if @intColStat = 1 begin
  set @vchrWhere = @vchrWhere  + '        ' + @fieldName + ' = ' + @paramName+ char(13)
 end
 --build a param list
 set @paramList = @paramList + char(13) + @paramName + ' ' + @fieldType + ','


 --build the 'set' list
 set @vchrSetList = @vchrSetList + char(13) + space(8) + @fieldName + ' = ' + @paramName + ','



 --build a VB param list
 set @paramListVB = @paramListVB + ', ' + 'ByVal ' + @fieldName + ' As ' + @fieldTypeVB  


 --build a sqlhelper param list
 set @paramListHelper = @paramListHelper + ', _ & ' + char(13) + space(16) + 'New SqlParameter("' + @paramName + '", ' + @fieldName + ')'
--
/*
 sp_update 'cn20100'


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD" )


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD" _ & 
                New SqlParameter("@DEX_ROW_ID", DEX_ROW_ID) )


    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "_4P_CN20100_UPD",  _ & 
                New SqlParameter("@CUSTNMBR", CUSTNMBR),  _ &
                New SqlParameter("@CPRCSTNM", CPRCSTNM),  _ &
                New SqlParameter("@DEX_ROW_ID", DEX_ROW_ID) )



*/


 FETCH NEXT FROM curFields INTO @fieldName, @fieldType,@fieldTypeVB, @paramName, @intColstat



END
CLOSE curFields
DEALLOCATE curFields


set @paramListVB = stuff(@paramListVB,1,2,'')


--remove the first and last char from the list
set @vchrSetList = stuff(@vchrSetList,1,1,'')
set @vchrSetList = left(@vchrSetList, len(@vchrSetList)-1)


--remove the last char from the list
set @paramList = left(@paramList, len(@paramList)-1)



print @paramlist
print ''
print 'AS'
print ''
print 'UPDATE ' + @tablename + ' SET'
print @vchrSetList


--print '    FROM ' + @tablename 
PRINT '    WHERE '
print @vchrWhere
-- sp_update 'cn20100'


print ''
print 'GO'
print ''
print 'grant all on ' + @vchrProcName + ' to public'
print ''
print ''
print ''
print ''
print 'Public Sub ' + @tableName + '_UPD(' + @paramListVB + ') '
print '    SqlHelper.ExecuteNonQuery(m_cs, CommandType.StoredProcedure, "' + @vchrProcName + '"' + @paramListHelper + ' )'
print 'End Sub'



go


grant all on sp_update to public