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.

Sukiyaki

 

I think I was the only one who enjoyed the shabu shabu at John and Yoko last 0214. The shabu shabu was so good that I tried another type of hot pot at Tempura, the sukiyaki. The main difference is sukiyaki has a sweet taste while shabu shabu had no flavor-- if miso and soy sauce were not mixed in it. Tempura's sukiyaki was composed of beef, shiitake, tofu, konnyaku (noodles), cabbage, green onions, and raw egg. The shabu shabu at J&Y had more: tofu, shrimp, crabsticks, beef, squid, fish, enokitake, shiitake, cabbage, pechay. I'm quite curious what J&Y's sukiyaki is composed of.

 

The coming Wednesday will be the last day of Korean class. In the last 4 meetings the attendance have dropped from the original 8 students to 3.