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

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
DB2 / ANSIKent Olsen11/29/051
MS SQL 2000David Teviotdale09/07/054
Self contained Oracle 9i+ VersionChris Farmer11/10/050
MS SQL 2005Johan ┼hlÚn09/28/091
CROSS JOIN - UNPIVOT - SQL SERVER 2008Nick Jacobsen09/18/110
standard versionRob van de Pol04/20/050
Oracle, DBA VersionThomas Rein02/03/070
DB2, no redundancy, with recursionJoachim Banzhaf05/17/060
Produces the exact lyrics, ORACLEErnst Madsen09/23/050

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!

Name:

eMail:

URL:

Security Code:
  
Comment: