Voting

Category

real language

Bookmarking

Del.icio.us Digg Diigo DZone Earthlink Google Kick.ie
Windows Live LookLater Ma.gnolia Reddit Rojo StumbleUpon Technorati

Language Transact-SQL

Date:04/20/05
Author:Joseph Thoennes
URL:n/a
Comments:2
Info:n/a
Score: (2.83 in 6 votes)
/*	Microsoft Transact-SQL version of the beer song
**	Joseph Thoennes, thoennes@paranet.com
*/
set nocount on
create table #beer (bottle tinyint identity)
while (select isnull(max(bottle),0) from #beer) < 99 insert into #beer
default values
select ltrim(str(bottle)) + ' bottle' + case when bottle > 1 then 's' end
+ ' of beer on the wall, '
+ ltrim(str(bottle)) + ' bottle' + case when bottle > 1 then 's' end + '
of beer, take '
+ case when bottle > 1 then 'one' else 'it' end + ' down, pass it around,
'
+ case when bottle - 1 > 0 then ltrim(str(bottle - 1)) else 'no more' end
+ ' bottle' + case when bottle - 1 <> 1 then 's' end + ' of beer on the
wall.'
from #beer order by bottle desc
drop table #beer

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Prints lyrics as message, not a tableD. Despain03/08/060
Single select query, no temp tablesRobert Bruce04/25/060

Comments

>>  Mark Hurd said on 07/01/05 11:29:46

Mark Hurd On MSDE 1.0 (MSSQL 7.0 based) and MSDE 2000 (MSSQL 2000 based) the plurals need an "else ''" clause, otherwise the last two verses are NULL.

>>  Dhumphries said on 10/12/09 22:34:35

Dhumphries I was looking for something else and accidently came across this page.
Here is an alternate version of the code. the posted code will never get to the last line and fails at 1 returning a null
set nocount on
create table #beer (bottle tinyint identity)
while (select isnull(max(bottle),0) from #beer) < 100 insert into #beer
default values
select
case
when (bottle - 1) > 0 then
ltrim(str(bottle - 1)) + ' bottles of beer on the wall, ' +
ltrim(str(bottle - 1)) + ' bottles of beer, take onedown, pass it around,' +
ltrim(str(bottle - 2)) + ' bottles of beer on the wall, '
when (bottle - 1) = 0 then 'no more bottles of beer on the wall, '
end
from #beer order by bottle desc

drop table #beer

Download Source | Write Comment

Add Comment

Please provide a value for the fields Name, Comment and Security Code.
This is a gravatar-friendly website.
E-mail addresses will never be shown.
Enter your e-mail address to use your gravatar.

Please don't post large portions of code here! Use the form to submit new examples or updates instead!

Name:

eMail:

URL:

Security Code:
  
Comment: