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 and for tables with a lot of columns (like 20 here) typing in parameters to be used for Insert and Update stored procedures this gets pretty tedious. So the solution is build a generator for these parameters based from table columns. We don't need to do a separate program for this, SQL can help us with that. Below is my 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. Insert and Update scripts are better left for the Script Table feature of SQL Server.