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 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)
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply