Can't work out how to retrieve data

Question

Post reply

Can't work out how to retrieve data

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ranjodh_makkar Valued Member Points: 68 More actions · Answer 1

If you dont want to use double hash global temp table then simply use single hash temp table instead.

Also about function let me write a quick template to do this

create function daterange_to_table

@start_date datetime,

@end_date datetime

returns table

as

begin

while @SD<=ED

begin

Insert into @temp

values @SD

set @SD=dateadd [day,1,@SD]

end

hope one'll be able to write a table return function from reference code above.

and with the help of execution of this function giving @SD and @ED as date range to fill the temp table.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

ranjodh_makkar (4/9/2011)
If you dont want to use double hash global temp table then simply use single hash temp table instead.
Also about function let me write a quick template to do this
create function daterange_to_table
@start_date datetime,
@end_date datetime
returns table
as
begin
while @SD<=ED
begin
Insert into @temp
values @SD
set @SD=dateadd [day,1,@SD]
end
end
hope one'll be able to write a table return function from reference code above.
and with the help of execution of this function giving @SD and @ED as date range to fill the temp table.

Gosh, NO! You're combining two performance sins, here. You're using an mTVF (Multi-line Table Valued Function) and RBAR. Please see the following article on how you can easily avoid such things.

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Jeff Moden (4/13/2011)
Gosh, NO! You're combining two performance sins, here. You're using an mTVF (Multi-line Table Valued Function) and RBAR. Please see the following article on how you can easily avoid such things.
http://www.sqlservercentral.com/articles/T-SQL/62867/

As an aside. I have a function I use all over the place which splits a comma separated string and returns a table and is used (generally) like ...

[Code="sql"]WHERE SomeID IN (SELECT [ThisRowsValue] FROM dbo.SplitCSV(@CSVList))[/code]

Is this bad practice?