99 Bottles of Beer

Voting

Bookmarking

Language Transact-SQL

Date:	04/20/05
Author:	Joseph Thoennes
URL:	n/a
Comments:	1
Info:	n/a
Score:	(3.67 in 3 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

Version	Author	Date	Comments	Rate
Prints lyrics as message, not a table	D. Despain	03/08/06	0
Single select query, no temp tables	Robert Bruce	04/25/06	0

Comments

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

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.

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!

one program in 1229 variations