Voting
Category
real language
Bookmarking
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
| 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 | |
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!
Comments
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