THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

2008: And Even More Row Constructor Fun

I decided to do a quick post In addition to the posts by Peter and Louis. And yes I copied Louis' table and changed it a little  :-)

Row Constructor will get the most use by sending in a bunch of data to a proc and execute that. This way the app can just concatenate a bunch of values without having to call the proc 20 times or even having to know how many rows are affected. Another good thing about this is that it either succeeds or fails. If you call a proc 20 times to insert a row and one of them fails then you have to have special logic to delete all the other rows.

Here is how you would do that with dynamic SQL, of course you would sanitize the input to check for SQL injection.

--Create the table


SomeRatingId int NOT NULL,

Code varchar(20) NOT NULL,

Description varchar(200) NULL,

AllowYouthRentalFlag bit NOT NULL





--put all that comma delimited junk in a variable

DECLARE @V varchar(max)

SELECT @V = '(0, ''UR'',''Unrated'',1),

(1, ''G'',''General Audiences'',1),

(2, ''PG'',''Parental Guidance'',1),

(3, ''PG-13'',''Parental Guidance for Children Under 13'',1),

(4, ''R'',''Restricted, No Children Under 17 without Parent'',0)'



DECLARE @SQL varchar(max)

SELECT @SQL= 'INSERT INTO SomeRating (SomeRatingId, Code, Description, AllowYouthRentalFlag)' +

'VALUES ' + @v


--print, after all this is our unit test :-)



--Here is where the magic happens, or so they say



--Look at all that data

SELECT * FROM SomeRating

Published Thursday, December 6, 2007 12:41 PM by Denis Gobo
Filed under:



Peter W. DeBetta said:

I wouldn't do that. Instead, I'd use a table-valued parameter. I posted a blog entry at that gives more detail...

December 6, 2007 6:16 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement