SP to create all updated SPs

This stored procedure creates all the stored procedures for updates in database

create

procedure [dbo].[sp_sql_create_update_sp](@table varchar(30),@module_prefix varchar(3), @sql varchar(max)=NULL OUTPUT)

as

------------------------------------------------------------------------

-- This procedure creates the SQL syntax to update an entry in a table

------------------------------------------------------------------------

declare @paramlist varchar(max)

declare @valuelist varchar(max)

declare @filterlist varchar(max)

SELECT

@paramlist

= COALESCE(@paramlist + ',' +

cast('@'+COLUMN_NAME + ' ' + DATA_TYPE + (case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL then '('+ CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + ')' else '' end) as varchar),

cast('@'+COLUMN_NAME + ' ' + DATA_TYPE + (case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL then '('+ CAST(CHARACTER_MAXIMUM_LENGTH as varchar) + ')' else '' end) as varchar)),

--Note:

--following was a replacement for COLUMN_NAME

--(case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL then COLUMN_NAME + '('+ CAST(CHARACTER_OCTET_LENGTH as varchar) + ')' else COLUMN_NAME end)

@valuelist

= COALESCE(@valuelist + ',' + cast(COLUMN_NAME as varchar)+'=@'+cast(COLUMN_NAME as varchar) , cast(COLUMN_NAME as varchar)+'=@'+cast(COLUMN_NAME as varchar)),

@filterlist

= COALESCE(@filterlist + ' AND ' + cast(COLUMN_NAME as varchar)+'=@'+cast(COLUMN_NAME as varchar) , cast(COLUMN_NAME as varchar)+'=@'+cast(COLUMN_NAME as varchar))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=@table

set @sql ='CREATE PROCEDURE sp_' +lower(@module_prefix) + '_update_' + lower(@table) + '(' + @paramlist + ') AS '

set @sql =@sql+ 'UPDATE '+@table+' SET '+ @valuelist + ' WHERE ' + @filterlist + ' GO'

RETURN