SP to create insert SP tool

This stored procedure creates all the insert stored procedures for a database. The resulting stored procedures can be modified and called by user applications.

/*

function: sp_kb_validate_movement

params: kanban card id, normal or reverse scan, (warehouse) from which the

scan has taken place

output: returns a boolean value to determine if all the requirements

have been met for the transaction to occur

method: Check for: invalid card; blocked card, no movement defined in routing table,

if there is enough stock on hand to move the bin (eg: bin quantity is 15, but

only 10 on hand to move.)

*/

create

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

as

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

-- This procedure creates the SQL syntax to insert an entry into a table

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

declare @paramlist varchar(max)

declare @valuelist varchar(max)

declare @varlist varchar(max)

--declare @module_prefix varchar(max)

--declare @table varchar(30)

--set @table='KBRouteDetails'

--set @module_prefix='KB'

SELECT

@paramlist

= COALESCE(@paramlist + ',' +

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

cast('@'+COLUMN_NAME + ' ' + DATA_TYPE + (case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL then '('+ CAST(CHARACTER_OCTET_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)),

@varlist

= COALESCE(@varlist + ',@' + 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) + '_insert_' + lower(@table) + '(' + @paramlist + ') AS '

set @sql =@sql+ 'INSERT INTO '+@table+'('+ @valuelist + ') VALUES(' + @varlist + ') GO'

RETURN