99 Bottles of Beer

Voting

Bookmarking

Language SQL

(MS-Transact-SQL 8.00)

Date:	07/06/05
Author:	Karl
URL:	n/a
Comments:	0
Info:	n/a
Score:	(2.90 in 10 votes)

declare @first_part char(20), @second_part char(34), @third_part char(7), @forth_part char(37)
declare @b char(8), @uB char(8)
declare @bottles tinyint
set @bottles = 99

set @b =' bottles'
set @uB =' Bottles'
set @first_part=' of beer on the wall'
set @second_part='Take one down and pass it around, '
set @third_part='no more'
set @forth_part='Go to the store and buy some more...'

while @bottles>0
begin
	if @bottles=2
	begin
		print convert(nvarchar, @bottles)+@uB+@first_part+', '+cast(@bottles as nvarchar)+
		@b+substring(@first_part,0,9)+'.'
		print @second_part+cast(@bottles-1 as nvarchar)+substring(@b,0,8)+@first_part+'.'
		set @bottles=@bottles-1
		print ''	
	end
	if @bottles=1
	begin
		print cast(@bottles as nvarchar)+substring(@uB,0,8)+@first_part+', '+cast(@bottles as nvarchar)+
		substring(@b,0,8)+substring(@first_part,0,9)+'.'
		print @second_part+@third_part+@b+@first_part+'.'
		set @bottles=@bottles-1
		print ''	
		break
	end	
print cast(@bottles as nvarchar)+@uB+@first_part+', '+cast(@bottles as nvarchar)+@b+
substring(@first_part,0,9)+'.'
print @second_part+cast(@bottles-1 as nvarchar)+@b+@first_part+'.'
print ''
set @bottles=@bottles-1
end

print 'No'+substring(@third_part,3,5)+substring(@b,0,8)+@first_part+'. '+'No'+
substring(@third_part,3,5)+substring(@b,0,8)
+substring(@first_part,0,9)+'...'
print rtrim(@forth_part)+'99'+@b+substring(@first_part,0,9)+'.'

Download Source | Write Comment

Alternative Versions

Version	Author	Date	Comments
Oracle 9i+ hierarchical query	Christoph Linder	11/21/07	0
DB2 / ANSI	Kent Olsen	11/29/05	1
MS SQL 2000	David Teviotdale	09/07/05	4
Self contained Oracle 9i+ Version	Chris Farmer	11/10/05	0
MS SQL 2005	Johan Åhlén	09/28/09	1
CROSS JOIN - UNPIVOT - SQL SERVER 2008	Nick Jacobsen	09/18/11	0
standard version	Rob van de Pol	04/20/05	0
Oracle, DBA Version	Thomas Rein	02/03/07	0
DB2, no redundancy, with recursion	Joachim Banzhaf	05/17/06	0
Produces the exact lyrics, ORACLE	Ernst Madsen	09/23/05	0

Comments

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 1500 variations