Stored Procedure Parameter Builder

One of the most common things we do is encapsulate the CRUD (Create Retrieve Update Delete) functions in a stored procedure. For tables with a lot of columns, typing in parameters to be used for Insert and Update stored procedures gets tedious. The solution is to build a generator for these parameters based from table columns. SQL can help us with this. Below is the code for a parameter builder:



create proc _parambuilder
@TableName varchar(100)
as

select ',@' + COLUMN_NAME + ' ' + DATA_TYPE + COALESCE('(' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')', '')
from information_schema.columns
where table_name = @TableName



It will produce an output like

,@CustomerID varchar(15)
,@SiteID int
,@UserID int
,@CreatedBy varchar(50)
,@CreatedByDate datetime
,@LastUpdatedBy varchar(50)
,@LastUpdatedByDate datetime



All we have to do is remove the first comma and these parameters are good as copy and paste. This script can be customized to generate Insert and Update scripts.