Solved

using an array or temp table for a stored procedure in mssql

Posted on 2014-12-10
2
115 Views
Last Modified: 2014-12-10
I have the following declaration in a stored procedure:

DECLARE @EndDate AS DATETIME
SET @EndDate = CAST (@PeriodEnd AS DATE)
SET @EndDate = dbo.fn_LocalTimeToUTC(@EndDate)

I would like to create an array or table that lists all the dates from 1/1/2013 to date:

('01/01/2013', '01/02/2013',.....'12/10/2014')

and use it as the @PeriodEnd date to cycle thru as my stored procedure creates one row of data for each date input.

Thanks in advance for any help.
0
Comment
Question by:plainwrapname
2 Comments
 
LVL 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 40492038
create a loop and call your sp inside loop

set @MyDate = "2013-01-01";
while  @MyDate < @endDate
Begin
  MySP @MyDate;
  set @MyDate = @MyDate +1;
End

Open in new window

;
0
 
LVL 27

Expert Comment

by:Sinisa Vuk
ID: 40492370
Better pass start and end date in stored procedure, set them in your query and group all by date part:

select datefield1 from table1 where datefield1>=@StartDate and datefield1<=@EndDate
group by Date(datefield1)

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question