Friday, March 23, 2012
Making a dynamic table (view)?
contains like 4 years of records (1 record per day). Each record only has
that date in it. That's it. Simple enough, except I don't want this to be
a real table because it will be different day by day/year by year and it
needs to be relatively efficient. SQL2000.
What I have so far is:
begin
declare @.currdate SmallDateTime;
Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
Create Table #temp(
aDate SmallDateTime
)
While year(@.CurrDate)<=Year(GetDate())+2
begin
Set @.CurrDate=DateAdd(d,1,@.CurrDate)
Insert Into #temp (aDate) values(@.CurrDate);
end
Select * from #Temp
Drop Table #Temp
end
The problem I see is the use of a temporary table and how that will impact
the server when it gets really loaded. Besides, this is just a procedure, I
need this to function "as if" it were a table to query against.
Any ideas?Why a temporary and not a real one?, you will not hurt your server for havin
g
one, instead, you can make life easier.
Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
AMB
"Jon Glazer" wrote:
> I would like to create a view or something that functions like a table tha
t
> contains like 4 years of records (1 record per day). Each record only has
> that date in it. That's it. Simple enough, except I don't want this to b
e
> a real table because it will be different day by day/year by year and it
> needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
I
> need this to function "as if" it were a table to query against.
> Any ideas?
>
>|||Lets just say from a learning perspective, how would I achieve what I wish
to here?
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
> Why a temporary and not a real one?, you will not hurt your server for
> having
> one, instead, you can make life easier.
> Why should I consider using an auxiliary calendar table?
> http://www.aspfaq.com/show.asp?id=2519
>
> AMB
>
> "Jon Glazer" wrote:
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
Example:
use northwind
go
select
identity(int , 0, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
alter table number
add constraint pk_number primary key (number)
go
create function ufn_function1 (
@.sd datetime,
@.ed datetime
)
returns table
as
return (
select
dateadd(day, n.number, @.sd) as col_the_date
from
number as n
where
n.number <= datediff(day, @.sd, @.ed)
)
go
select
*
from
ufn_function1('20050128', '20050204')
order by
1
go
drop function ufn_function1
go
drop table number
go
AMB
"Jon Glazer" wrote:
> Lets just say from a learning perspective, how would I achieve what I wish
> to here?
> Jon
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:5DC6B54C-311C-4E6E-A07B-1A864041DBE2@.microsoft.com...
>
>|||You can't generate data out of nothing. Two alternatives to using an actual
table would be to use a table-valued function containing a loop or to use a
view with a large UNION statement and one SELECT for each date. Either of
those structures would get materialized as data the moment they were used,
so they would offer no obvious benefits over using a table.
Auxiliary tables (Calendars, Numbers, etc) are a standard technique for
doing this sort of thing. After all, even 10 years of dates in a Calendar
table is still less than 4000 rows.
David Portas
SQL Server MVP
--|||If you must...
select
dateadd(year,- 1,dateadd(day,datediff(day,0,getdate()),
0)-datepart(dy,getdate
()))
+digit as dt
from (
select 1 + unit.digit +
10 * ten.digit +
100 * hundred.digit +
1000 * thousand.digit as [number]
from (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as unit(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as ten(digit)
cross join (
select 0 union select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union select 7 union
select 8 union select 9) as hundred(digit)
cross join (
select 0 union select 1 union select 2 ) as thousand(digit)
) numbers(digit)
where digit <= datediff(day,dateadd(year,-1,
dateadd(day,datediff(day,0,getdate()),0)
-datepart(dy,getdate()))
,dateadd(year,3,getdate()-datepart(dy,getdate())))
order by digit
-oj
"Jon Glazer" <jglazer.delete.me@.adconn.com> wrote in message
news:SoxKd.62801$re1.24174@.fe2.columbus.rr.com...
>I would like to create a view or something that functions like a table that
>contains like 4 years of records (1 record per day). Each record only has
>that date in it. That's it. Simple enough, except I don't want this to be
>a real table because it will be different day by day/year by year and it
>needs to be relatively efficient. SQL2000.
> What I have so far is:
> begin
> declare @.currdate SmallDateTime;
> Set @.CurrDate='01/01/'+Cast(Year(GetDate())-1 as Char(4))
> Create Table #temp(
> aDate SmallDateTime
> )
> While year(@.CurrDate)<=Year(GetDate())+2
> begin
> Set @.CurrDate=DateAdd(d,1,@.CurrDate)
> Insert Into #temp (aDate) values(@.CurrDate);
> end
> Select * from #Temp
> Drop Table #Temp
> end
> The problem I see is the use of a temporary table and how that will impact
> the server when it gets really loaded. Besides, this is just a procedure,
> I need this to function "as if" it were a table to query against.
> Any ideas?
>sql
Monday, March 19, 2012
Make Dynamic cursor
I want to a create cursor, data get in this cursor is got from a table which i don't know name (the name table can change). But i can't do the select to create this cursor by exec. Please help me, thanks.
Try this:
use pubs
go
declare @.Table as Varchar(128)
set @.Table = 'Authors'
declare @.SQL Varchar(1000)
set @.SQL = ''
set @.SQL = @.SQL + ' declare DYNCUR cursor for select * from ' + @.Table
set @.SQL = @.SQL + ' open DYNCUR'
set @.SQL = @.SQL + ' fetch next from DYNCUR'
set @.SQL = @.SQL + ' while @.@.fetch_status = 0'
set @.SQL = @.SQL + ' begin'
set @.SQL = @.SQL + ' fetch next from DYNCUR'
set @.SQL = @.SQL + ' end'
set @.SQL = @.SQL + ' close DYNCUR'
set @.SQL = @.SQL + ' deallocate DYNCUR'
exec(@.SQL)
Monday, March 12, 2012
Make a Dynamic Cursor in a Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ?
My code:
Declare @.idRelat int, @.cmd_FROM nvarchar(1024), @.cmd_Det nvarchar(50)
SELECT @.idRelat = idRelat, @.cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @.p_Relat
Declare @.Tot_Col smallint, @.Tot_Lin smallint, @.Campos smallint,
@.Aux_Select nvarchar(1024), @.Aux_Group nvarchar(1024), @.Aux_Order nvarchar(1024)
Select @.Tot_Col = 0
Select @.Tot_Lin = 0
Select @.Campos = 0
Select @.Aux_Select = "SELECT " + @.cmd_DET + "AS Soma"
Select @.Aux_Group = "GROUP BY "
Select @.Aux_Order = "ORDER BY "
Declare @.a_Local char(1), @.a_Linha smallint, @.a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @.idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
While @.@.FETCH_status = 0 begin
If @.a_Local = "C"
Select @.Tot_Col = @.Tot_Col + 1
Else
Select @.Tot_Lin = @.Tot_Lin + 1
Select @.Campos = @.Campos + 1
If @.Aux_Group <> "GROUP BY " begin
Select @.Aux_Group = @.Aux_Group + ", "
If @.Aux_Order <> "ORDER BY " begin
Select @.Aux_Order = @.Aux_Order + ", "
Select @.Aux_Select = sSelect + ", " + @.a_Campo + " AS Campo" + @.Campos
Select @.Aux_Group = @.Aux_Group + @.a_Campo
Select @.Aux_Order = @.Aux_Order + @.a_Campo
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
End
Select @.Aux_Select = @.Aux_Select
-- <<<< MONTA COMANDO SQL
Select @.Aux_Select = @.Aux_Select + " " + @.cmd_FROM + " " + @.p_Filtro + " " + @.Aux_Group + " " + @.Aux_Order
Declare @.Cursor_Aux cursor
Set @.Cursor_Aux = cursor for @.Aux_Select
Open @.Cursor_Aux
Not working !!!!
Luiz Amrico wrote:
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ?
My code:
Declare @.idRelat int, @.cmd_FROM nvarchar(1024), @.cmd_Det nvarchar(50)
SELECT @.idRelat = idRelat, @.cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @.p_RelatDeclare @.Tot_Col smallint, @.Tot_Lin smallint, @.Campos smallint,
@.Aux_Select nvarchar(1024), @.Aux_Group nvarchar(1024), @.Aux_Order nvarchar(1024)Select @.Tot_Col = 0
Select @.Tot_Lin = 0
Select @.Campos = 0
Select @.Aux_Select = "SELECT " + @.cmd_DET + "AS Soma"
Select @.Aux_Group = "GROUP BY "
Select @.Aux_Order = "ORDER BY "
Declare @.a_Local char(1), @.a_Linha smallint, @.a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @.idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
While @.@.FETCH_status = 0 begin
If @.a_Local = "C"
Select @.Tot_Col = @.Tot_Col + 1
Else
Select @.Tot_Lin = @.Tot_Lin + 1
Select @.Campos = @.Campos + 1
If @.Aux_Group <> "GROUP BY " begin
Select @.Aux_Group = @.Aux_Group + ", "
If @.Aux_Order <> "ORDER BY " begin
Select @.Aux_Order = @.Aux_Order + ", "
Select @.Aux_Select = sSelect + ", " + @.a_Campo + " AS Campo" + @.Campos
Select @.Aux_Group = @.Aux_Group + @.a_Campo
Select @.Aux_Order = @.Aux_Order + @.a_Campo
Fetch cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
End
Select @.Aux_Select = @.Aux_Select
-- <<<< MONTA COMANDO SQL
Select @.Aux_Select = @.Aux_Select + " " + @.cmd_FROM + " " + @.p_Filtro + " " + @.Aux_Group + " " + @.Aux_Order
Declare @.Cursor_Aux cursor
Set @.Cursor_Aux = cursor for @.Aux_Select
Open @.Cursor_AuxNot working !!!!
Your missing some keywords in your cursor sytax:
Fetch Next From cur_Aux into @.a_Local, @.a_Linha, @.a_Campo
And you're not closing and deallocating your cursor:
CLOSE cur_Aux
DEALLOCATE cur_Aux
Use this syntax:
DECLARE @.AuthorID char(11)
OPEN c1DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors
FETCH NEXT FROM c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.AuthorID
FETCH NEXT FROM c1
INTO @.AuthorID
END
CLOSE c1
DEALLOCATE c1
Adamus