June 24, 2011 at 1:30 pm
Dear all,
I am trying to make a triangle pattern using number.
so please help me ,and my query is below ...
declare @n1 int
declare @n2 int
declare @c1 int
set @n1 = 1
set @n2 = 1
set @c1 = 5
while(@n1<=@c1)
begin
print @n1
while(@n2<=@c1)
begin
set @c1 = @n1 +''+@n2
set @c1 = @c1 + 1
end
print @c1
set @n1 = @n1 + 1
set @c1 =0
end
Thanks in advance and for your time
June 24, 2011 at 2:25 pm
Not really sure what you are trying to but this will never work as it is. Here is your code formatted for readability.
declare @n1 int
declare @n2 int
declare @c1 int
set @n1 = 1
set @n2 = 1
set @c1 = 5
while(@n1<=@c1) begin
print @n1
while(@n2<=@c1)begin --this will be an endless loop because the value of @n2 or @n3 is never changed inside the loop
print @c1
set @c1 = @n1 +''+@n2
set @c1 = @c1 + 1
end
print @c1
set @n1 = @n1 + 1
set @c1 =0
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2011 at 3:10 pm
Hi Sean,
I am trying to make a triangle pattern like this
i want to implement while loop.But whenever i try to run then it goes to infinite loop
my pattern is like this
1
12
123
123
12345
June 24, 2011 at 3:49 pm
If you insist in using a while loop: I don't know how to do it.
But here's a set based solution:
declare @c1 int
set @c1 = 5
;
WITH cte_tally AS
(SELECT Number +1 AS N FROM master..spt_values WHERE type='P' AND Number <@c1)
SELECT (SELECT n+'' FROM cte_tally t2 WHERE t2.n<=cte_tally.n FOR XML PATH ('') )x
FROM cte_tally WHERE n<=@c1
June 24, 2011 at 4:26 pm
Hi Lutz,
Thanks for help me out.
But it is difficult for me using CTE.
anyway, i know we can do also another way using nested while loop.
it's much easier that CTE but i dont getting the same pattern.
Thanks Lutz
June 24, 2011 at 4:59 pm
jkp2311 (6/24/2011)
Hi Lutz,Thanks for help me out.
But it is difficult for me using CTE.
anyway, i know we can do also another way using nested while loop.
it's much easier that CTE but i dont getting the same pattern.
Thanks Lutz
Would you please show me what you expect? Based on your previous post I can't see any difference (ok, there's an additional 4 in my solution in row 4 but I thought that's just a typo on your side...)
That's the result of the code I proposed:
1
12
123
1234
12345
June 24, 2011 at 5:28 pm
Hi Lutz
You get the right output what i want.
But i want same output using nested while loop.
Is that possible?
If it is let me know?>
Thanks,
Jignesh Patel
June 24, 2011 at 5:53 pm
Here it is as a WHIE loop
SET NOCOUNT ON
DECLARE @S INT
DECLARE @P VARCHAR(20)
DECLARE @X VARCHAR(20)
SET @X = ' '
SET @S = 1
WHILE (@S < 10)
BEGIN
SET @P = (CAST(@S AS VARCHAR(10)))
SET @X = @X + @P
SET @S = @S + 1
SELECT @X
PRINT @X
IF @S =10
Break
ELSE
CONTINUE
END
June 24, 2011 at 6:04 pm
Hi bitbucket-25253
Thanks for Posting your reply.
It will be helpfull to on millions of way.
That's the logic what i want.
I guess it will be helpful all members of our community
who is the biggeners in T-SQL programming.
Thanks a lot
From:
Jignesh Patel
June 24, 2011 at 6:38 pm
Hi bitbucket-25253
suppose to be i want make pattern like this :
12345
1234
123
12
1
so i have to use a decrement counter?
and how can i input the string into the loop?
let me know..if u know..
Thanks a lot
From:
Jignesh Patel
June 25, 2011 at 2:06 am
I have to ask again: why do you insist in using a loop?
Even if there is a set based solution available?
It seems like performance is not on your list of items being considered when programming. Sad. :crying:
And no, I don't think the solution Ron (bitbucket) posted is helpful to the community, since it shows the 2nd best option in almost any case. (@Ron: no personal offense intended!)
Once you're in a situation to deal with a million rows or more or very complex joins, you might remember what I posted...
If you're a beginner, you should learn it the right way from the very first line of code.
June 25, 2011 at 2:09 am
Oh, before I forget:
To get the result you're looking for (desc order), just add an "ORDER BY cte_tally.n DESC" to the code I posted.
June 25, 2011 at 7:57 am
LutzM
And no, I don't think the solution Ron (bitbucket) posted is helpful to the community, since it shows the 2nd best option in almost any case. (@Ron: no personal offense intended!)
No offense taken, in fact your saying 2nd best, is a compliment, when in truth it is the worst possible method. I only posted the code, since I had the gut feeling that the OP was new enough not to understand what a CTE is and how powerful it can be, plus the OP mentioned
.But whenever i try to run then it goes to infinite loop
, and thought if I showed the OP how to write a proper WHILE statement some knowledge may have been imparted.
Now on that note here is another very poor way to do the inverse of the original question. (Again using a WHILE statement.)
.But whenever i try to run then it goes to infinite loop
SET NOCOUNT ON
DECLARE @S INT
DECLARE @P VARCHAR(20)
DECLARE @X VARCHAR(20)
SET @X = '123456789'
SET @S = 9
WHILE (@S > 0)
BEGIN
SET @X = SUBSTRING(@X,1,@S)
SET @S = @S - 1
SELECT @X
PRINT @X
IF @S = 0
Break
ELSE
CONTINUE
END
Now mr/mrs jkp2311 please, please examine how LutzM's code works, for his solution (using a CTE) is a most powerful tool, not only for solving your immediate request, but for so many other RBAR (Row by agonizing row) problems such as finding duplicate entries in a table.
June 25, 2011 at 12:10 pm
Hi Bitbucket
I find another solution...
declare @vint int
declare @vcount int
declare @vintnew varchar(200)
declare @vintnew1 varchar(200)
set @vint = 1
set @vintnew = cast(@vint as varchar(200))
while @vint < 5
begin
set @vintnew = @vintnew + cast(@vint as varchar(200))
end
print @vintnew
set @vcount = LEN(@vintnew)
while @vcount > 0
begin
set @vintnew1 = substring(@vintnew ,1 ,@vcount-1)
set @vcount = @vcount -1
print @vintnew1
end
go
June 25, 2011 at 12:20 pm
jkp2311 (6/24/2011)
Hi bitbucket-25253Thanks for Posting your reply.
It will be helpfull to on millions of way.
That's the logic what i want.
I guess it will be helpful all members of our community
who is the biggeners in T-SQL programming.
Thanks a lot
From:
Jignesh Patel
Using a loop is absolutely the worst thing to do here. Learning how to use a loop for this is the 2nd worst thing you could do. It's absolutely the wrong thing to learn and there's no way that I'd ever teach a beginner to loop.
With that in mind, why do you insist on using a loop? It's a two way street here and we'd all like to know why you insist on making this mistake. What I think is that you were given an assignment by an instructor.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy