December 9, 2007 at 11:50 pm
Hi Experts,
I have written a query to produce the following result
A
A B
A B C
A B C D
A B C D E
A B C D E F
Query :
------------------------------------------------------------
Declare @Str char(6),@Len int,@IntitalValue int,@FinalResult Varchar(6)
Set @Str = 'ABCDEF'
Set @Len = len(@Str)
set @InitialValue = 1
while @InitialValue <= @Len
Begin
select @FinalResult = substring(@Str,1,@InitialValue)
print @FinalResult
End
-----------------------------------------------------------
I dont want to use 'While Loop' becuase it uses RBAR Logic.How to avoid while loop in this situation ? I want to apply SetBased logic for the above problem.
Experts inputs are welcome !
karthik
December 9, 2007 at 11:58 pm
I forgot to include
select @InitialValue = @InitialValue + 1
karthik
December 10, 2007 at 12:05 am
Hey Karthikeyan,
Just have a Look on this Post.
http://www.sqlservercentral.com/articles/T-SQL/61539/
By Jeff Moden, 2007/12/06
Hope, you can get idea from this.
Cheers!
Sandy.
--
December 10, 2007 at 12:05 am
I'll show how, you explain why...
DECLARE @Str CHAR(6)
SET @Str = 'ABCDEF'
SELECT LEFT(@Str,Number)
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 6
ORDER BY Number
Do be advised that there are certain types of RBAR that are faster than "set based"... they're far and few between, but they do exist...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2007 at 12:08 am
Sandy (12/10/2007)
Hey Karthikeyan,Just have a Look on this Post.
http://www.sqlservercentral.com/articles/T-SQL/61539/
By Jeff Moden, 2007/12/06
Hope, you can get idea from this.
Cheers!
Sandy.
Ummm... thanks Sandy, but that whole article is about how bad triangular joins are... not meant to be a fix for things like what Karthik asked for.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2007 at 12:13 am
Jeff,
Can you explain me the logic used in your query ?
Really i am confused. I don't understand the link between 'BETWEEN 1 AND 6' and 'LEFT (@STR,number).Also what is the use of master.dbo.spt_values table ? Why are you using here ?
Kindly clarify my doubts.
karthik
December 10, 2007 at 12:16 am
Also if i want to print A-Z in the mentioned format ,can i use the same logic or do i need to change your code ?
karthik
December 10, 2007 at 12:40 am
Of course the "code" must change... but the changes I made also make it more flexible...
DECLARE @Str VARCHAR(100)
SET @Str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT LEFT(@Str,Number)
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 1 AND LEN(@Str)
ORDER BY Number
Go look at the spt_Values table... you'll figure it out...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2007 at 1:09 am
Thanks Jeff Moden,
Cheers!
Sandy.
--
December 10, 2007 at 1:27 am
You bet, Sandy... thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2007 at 7:06 am
Karthik...
What about you... you all set?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2007 at 10:34 pm
Jeff,
Can you explain me the logic ?
karthik
December 11, 2007 at 7:35 am
Ok... sure...
In order to do what you wanted, you wrote a loop to have a variable (@InitialValue) count from 1 to 6 in your first example. Then you used the content of that variable in the LEFT(@Str,@InitialValue).
I just happen to know that spt_Values table in SQL Server 2k has a particular TYPE (Type = 'P') that contains all the whole numbers from 0 to 255. So, if I write a query like this...
SELECT Number
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 6
ORDER BY Number
...I'll get a result that, like your loop, counts from 1 to 6...
Number
-----------
1
2
3
4
5
6
(6 row(s) affected)
Now, a count is a count whether it comes from a loop variable like you had or a column of numbers like I have. I used the values from the Number column to replace your variable...
...that's all there is to it.
As you can see, a "list of numbers" can come in quite handy... there's a huge number of things you can do with it including parsing columns of delimited data, creating a "Title Case" function, separating alpha and numeric characters in functions, creating columns of contiguous dates, etc, etc, etc.
Some folks call those a "Numbers" table... I call it a "Tally" table because it sounds cooler 😉 and it's used to count (tally) things. Here's how to make a Tally table that has more than the max number of characters a VARCHAR holds and can also be used to generate just over 30 years of dates...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2007 at 7:12 pm
So... are you happy with that answer or is there something else?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2007 at 1:02 am
Really i am happy with your answer.But...Still i have some questions on spt_values table.
1) How many types are there like 'P' ?
2) I did search in Google,but i didn't get clear information about the table.So can you explain me where we need to apply this table ?I mean ,as you told,In future if i faced the above kind of problem then i would use this table,like this in which situations we can use this table.
---------------------------------------------
declare @ServerRole nvarchar(35)
declare @MemberName sysname
declare srvrolemember cursor for
select 'ServerRole' = spv.name, 'MemberName' = lgn.name
from
master.dbo.spt_values spv,
master.dbo.sysxlogins lgn,
sysusers u
where
spv.low = 0 and
spv.type = 'SRV' and
lgn.srvid IS NULL and
spv.number & lgn.xstatus = spv.number and
lgn.sid = u.sid and
lgn.name <> 'sa'
order by 'MemberName'
for read only
open srvrolemember
fetch next from srvrolemember into @ServerRole, @MemberName
while @@fetch_status = 0
begin
Print 'exec sp_addsrvrolemember N''' + @MemberName + ''', ' + @ServerRole
fetch next from srvrolemember into @ServerRole, @MemberName
end
close srvrolemember
deallocate srvrolemember
--------------------------------------------------------------
If you see the above example , it uses spt_values table.Like this where we can use this table ?
Can you give me the detailed information about master.dbo.spt_values table ? Also can you list out some situations that we can use this table ?
karthik
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply