Blogs - sp_update

DEVSHED Blog

As always, I welcome your comments!

sp_update

Dec 10

Written by: Steve Gray
12/10/2010 12:37 PM

This procedure creates an update statement. Refer to the Data Access index for the documentation

Prerequisites:

f_4P_columnType

Code Snippet
 --updated 12/10/2010
 
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 'POP10300'
 
@tablename varchar(50)
 
AS
 
set nocount on
    
declare @vchrFieldName varchar(255),
    @vchrFieldType varchar(50),
    @vchrParamName varchar(255),
    @vchrParamList varchar(8000),
    @vchrProcName varchar(255),
    @vchrSetList varchar(8000),
    @vchrOrdinalPosition int,
    @vchrAnd varchar(5),
    @vchrWhere varchar(1000)
 
 
select
    @vchrAnd = '',
    @vchrWhere = '',
    @vchrParamList = '',
    @vchrSetList = '',
    @vchrProcName = 'FP_' + @tablename + '_UPD'
 
 
print 'IF EXISTS (SELECT name '
print '    FROM   sysobjects '
print '    WHERE  name = N' + char(39) + @vchrProcName + 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 c.name,
        dbo.f_4P_columnType (xusertype,length, xprec , xscale),
        char(64) + c.name,
        /* the ordinal position of the key fields */
        k.ordinal_position
    from sysobjects o
        left join syscolumns c on c.id = o.id
        left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS con 
            on con.table_name = o.name and con.constraint_type = 'PRIMARY KEY'
        left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
            on Con.TABLE_NAME = K.TABLE_NAME 
            AND Con.CONSTRAINT_NAME = K.CONSTRAINT_NAME and k.column_name = c.name
    where o.name = @tablename
        /* don't mess with timestamp columns */
        and xusertype <> 189
    order by c.colorder
 
 
 
OPEN curFields
FETCH NEXT FROM curFields INTO @vchrFieldName, @vchrFieldType, 
    @vchrParamName, @vchrOrdinalPosition
WHILE @@FETCH_STATUS = 0
BEGIN
    --build a where clause
    if not @vchrOrdinalPosition is null begin
        set @vchrWhere = @vchrWhere  + '        ' + @vchrAnd 
            + @vchrFieldName + ' = ' + @vchrParamName + char(13)
        set @vchrAnd = 'and '
    end
 
    --build a param list
    set @vchrParamList = @vchrParamList + char(13) 
        + @vchrParamName + ' ' + @vchrFieldType + ','
 
    --build the 'set' list
    set @vchrSetList = @vchrSetList + char(13) 
        + space(8) + @vchrFieldName + ' = ' + @vchrParamName + ','
 
    FETCH NEXT FROM curFields INTO @vchrFieldName, 
        @vchrFieldType,@vchrParamName, @vchrOrdinalPosition
END
CLOSE curFields
DEALLOCATE curFields
 
--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 @vchrParamList = left(@vchrParamList, len(@vchrParamList)-1)
 
 
print @vchrParamList
print ''
print 'AS'
print ''
print 'UPDATE ' + @tablename + ' SET'
print @vchrSetList
 
PRINT '    WHERE '
print @vchrWhere
 
go
 
grant exec on sp_update to public
 

Trackback Print

Tags:

Categories:

Location: Blogs Parent Separator

DEVSHED Blogs Child Separator

SQL

0 comment(s) so far...

As always, I welcome your comments!