August 11, 2008 at 3:29 am
Hey Hey, I need some Help,
I Got a bit of homework 2 do yesterday and I cant seem to Figure it out.
I got this table
USE ALPHA
GO
CREATE TABLE NUMBER
( NumberID SMALLINT PRIMARY KEY NONCLUSTERED,
NumberValue SMALLINT NOT NULL DEFAULT '0',
CHECK (NumberValue <10),
NumberTimesUsed SMALLINT NULL
)
INSERT NUMBER VALUES ('1', '1', '2')
INSERT NUMBER VALUES ('2', '2', '2')
INSERT NUMBER VALUES ('3', '3', '2')
INSERT NUMBER VALUES ('4', '4', '2')
INSERT NUMBER VALUES ('5', '5', '2')
I Need to Make a stored Procedure to print somethig like this
RESULT
1
11
111
1111
111
11
1
I Have Got This So Far, but it doesnt seem right
DECLARE @Loop INT, @Number INT
SET @Loop = 0
WHILE @Loop < 6
BEGIN
SET @Loop = @Loop +1
SET @Number = (SELECT NumberValue FROM Number WHERE NumberID = '1')
PRINT @Number
END
I May NOT use Cursors.
I Have To Use Loops.
Any1 help?
August 11, 2008 at 5:11 am
I Have To Use Loops.
why ?
karthik
August 11, 2008 at 5:18 am
Strangest question I ever saw.
Why do you even need teh table?
DECLARE @Loop INT, @Number INT
DECLARE @Loop2 INT
SET @Loop = 0
SET @Loop2 = 3
WHILE @Loop <= 7
BEGIN
IF @Loop<=4
BEGIN
PRINT REPLICATE('1',@Loop)
SET @Loop = @Loop +1
END
ELSE
BEGIN
PRINT REPLICATE('1',@Loop2)
SET @Loop2 = @Loop2 -1
SET @Loop = @Loop +1
END
END
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 11, 2008 at 5:36 am
Christopher,
why can't we use Tally or Number table ?
My approach :
Select REPLICATE('1',N),N
from Tally,NUM
where N <= 6
and NumberValue = 1
union all
select REP,N from (Select REPLICATE('1',N) as REP ,N
from Tally,NUM
where N <= 6
and NumberValue = 1
order by N Desc) a
karthik
August 11, 2008 at 5:36 am
Hey, I need the table because one(1) is not the only value in the table, And the User Can only choose a number from the table else it gives an error
Also Its Part Of A Stored Procedure, and the user must just enter an ID
August 11, 2008 at 5:47 am
If we convert it into a SP, then we can change the input values as required.
Create Procedure PrintReverse
(
@NumberToReverse int,
@NoOfTimesRepeat int
)
Select REPLICATE(@NumberToReverse,N)
from Tally,NUM
where N <= @NoOfTimesRepeat
and NumberValue = @NumberToReverse
UNION ALL
select REP from (Select REPLICATE(@NumberToReverse,N) as REP
from Tally,NUM
where N <= @NoOfTimesRepeat
and NumberValue = @NumberToReverse
order by N Desc) a
End
karthik
August 11, 2008 at 5:57 am
karthikeyan
I will Give that a try now, but is it just me or is there no loops?
August 11, 2008 at 6:08 am
trevorjv,
Before that i would suggest you to read about 'Tally' table. Becuase i have used that table in my code.
karthik
August 11, 2008 at 6:12 am
August 17, 2008 at 8:32 pm
Hi Trevor...
It's odd that you must use a While loop instead of a cursor because a cursor uses a While loop! They both suck performance dry. 🙂
I'd recommend that you do what your instructor wants so you can "get the grade"... use the loop. But, for extra credit, show how it can be done without the loop... that it can be done in a set based fashion! And, sorry, the Tally table is not the solution here...
SELECT e.Ones
FROM (SELECT d.MaxNumberValue-NumberValue AS NumberValue,
REPLICATE('1',NumberValue) AS Ones
FROM Number n
CROSS JOIN (SELECT MAX(NumberValue) AS MaxNumberValue FROM Number)d
UNION ALL
SELECT n.NumberValue-d.MaxNumberValue AS NumberValue,
REPLICATE('1',n.NumberValue) AS Ones
FROM Number n
CROSS JOIN (SELECT MAX(NumberValue) AS MaxNumberValue FROM Number)d)e
ORDER BY e.NumberValue
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2008 at 8:46 pm
karthikeyan (8/11/2008)
Christopher,why can't we use Tally or Number table ?
My approach :
Select REPLICATE('1',N),N
from Tally,NUM
where N <= 6
and NumberValue = 1
union all
select REP,N from (Select REPLICATE('1',N) as REP ,N
from Tally,NUM
where N <= 6
and NumberValue = 1
order by N Desc) a
Heh... OP gave you a perfectly good test table... you should use it to test your code! 🙂
[font="Courier New"]Server: Msg 1033, Level 15, State 1, Procedure PrintReverse, Line 18The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2008 at 8:48 pm
karthikeyan (8/11/2008)
If we convert it into a SP, then we can change the input values as required.Create Procedure PrintReverse
(
@NumberToReverse int,
@NoOfTimesRepeat int
)
Select REPLICATE(@NumberToReverse,N)
from Tally,NUM
where N <= @NoOfTimesRepeat
and NumberValue = @NumberToReverse
UNION ALL
select REP from (Select REPLICATE(@NumberToReverse,N) as REP
from Tally,NUM
where N <= @NoOfTimesRepeat
and NumberValue = @NumberToReverse
order by N Desc) a
End
Ditto... 😉
[font="Courier New"]Server: Msg 156, Level 15, State 1, Procedure PrintReverse, Line 7
Incorrect syntax near the keyword 'Select'.
Server: Msg 1033, Level 15, State 1, Procedure PrintReverse, Line 18The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2008 at 8:49 pm
Jeff Moden (8/17/2008)
Hi Trevor...It's odd that you must use a While loop instead of a cursor because a cursor uses a While loop! They both suck performance dry. 🙂
I'd recommend that you do what your instructor wants so you can "get the grade"... use the loop. But, for extra credit, show how it can be done without the loop... that it can be done in a set based fashion! And, sorry, the Tally table is not the solution here...
SELECT e.OnesFROM (SELECT d.MaxNumberValue-NumberValue AS NumberValue,
REPLICATE('1',NumberValue) AS Ones
FROM Number n
CROSS JOIN (SELECT MAX(NumberValue) AS MaxNumberValue FROM Number)d
UNION ALL
SELECT n.NumberValue-d.MaxNumberValue AS NumberValue,
REPLICATE('1',n.NumberValue) AS Ones
FROM Number n
CROSS JOIN (SELECT MAX(NumberValue) AS MaxNumberValue FROM Number)d)e
ORDER BY e.NumberValue
By the way... if you only want the ones for the "hi" number to appear just once, change the UNION ALL to just a UNION... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 2:05 am
Heh... OP gave you a perfectly good test table... you should use it to test your code!
Server: Msg 1033, Level 15, State 1, Procedure PrintReverse, Line 18The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
Ditto...
Server: Msg 156, Level 15, State 1, Procedure PrintReverse, Line 7
Incorrect syntax near the keyword 'Select'.
Server: Msg 1033, Level 15, State 1, Procedure PrintReverse, Line 18The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
You are right !
And, sorry, the Tally table is not the solution here...
How and why ? Can you explain it ?
I have modifed my previous code as below.
Select REPLICATE('1',N) as NumValue,N
into #t1
from Tally,NUMBER
where N <= 6
and NumberValue = 1
Select REPLICATE('1',N) as REP ,N
into #t2
from Tally,NUMBER
where N <= 5
and NumberValue = 1
order by N Desc
select NumValue,N
from #t1
union all
select REP,N
from #t2
It is working perfectly, But it is using # tables.
karthik
August 18, 2008 at 5:00 am
UNIONs? IFs?
DECLARE@Loop INT
SET@Loop = 1
WHILE @Loop < 8
BEGIN
PRINTREPLICATE('1', 4 - ABS(4 - @Loop))
SET@Loop = @Loop + 1
END
SELECTREPLICATE('1', 4 - ABS(4 - Number))
FROMmaster..spt_values
WHEREType = 'P'
AND Number BETWEEN 1 AND 7
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply