Stored Procedures and Arrays
-
Hi,
I'm using SQL Server and I am trying to convert some ASP code I wrote into a User Defined Function that will work with a stored procedure I have written.
I seem to be having problems trying to create an array in the Function, I have looked on line and most websites seem to comment that SQL Server doesn't support Arrays. If anybody has any ideas on a way round this it would be greatly appreciated.
Cheers
Mark
-
SQL only has atomic values and relations/recordsets (tables, views etc.). Use a table variable with two columns: an identity(0,1) column to order the values (acts like the accessor subscript in an array) and another column containing the values. Then instead of
ihatevariants = arr(4)
in a non-relational language, you would use
- set @typedvariable = t.data_col
- from @tmptable t where t.id_col = 4
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thanks for the reply Tim and the good advice on arrays, while trying to converting my ASP function to a Stored Procedure I've found that while support for the date functions i've used are supported in SQL Server, as we have discussed arrays are not, and as i'm new to stored procedures i've also struggled with the syntax.
I'm also unsure if SQL Server supports for loops, I don't want to appear forward but i've included the function below and any comments, feedback or advice on converting the syntax would be greatly appreciated.
Thanks again for the reply
Mark
1 AND Weekday(dateToCheck(mlCounter))
-
Yes, SQL server supports a WHILE loop. But don't forget that you are translating from a primarily procedural laguage to a primarily relational language. So some of the things you would do with loops in your ASP function probably can (and if so, definitely should) be done using SQL, rather than fiddly row-by-row processing.
I, and no doubt the others on this board, would be glad to look at the function, but i think you have forgotten to append the code to your previous post.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Arrays? Maybe this will help?
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi - please post the code and we'll have a look. You have several issues
1. Passing multiple data values to a stored proc from ASP - Ryan's link above should do the trick.
2. Learning how to think in a set-based way in SQL - if you want to do an operation to a set of rows in your table, rather than looping over the rows one by one in a loop (a cursor or while loop) you can tell SQL to do the whole lot in one go which will be MUCH more efficient
3. Another common issue I've seen is when you want to, for example, get a number of records from an orders table as well as retrieving records from an order details table for each order. Many folk not familar with SQL DB access (or from a MySQL v3 background which I found didn't support this) would run a separate DB query for each order. It is more efficient to run one query that returns two recordsets - the first lists the orders, the second lists all of the order details for all of the orders in the first recordset. The big saving is due to cutting down on round trips to the server.... I'm not sure if you'll encounter this situation but it sounds like if you haven't got this far already you soon will.
So please post some of your code and we'll give you a hand. Cheers
-
Hi,
thanks for the reply and offering to help, any advice would be much appreciated.
Thanks
this is my ASP function:
1 AND Weekday(dateToCheck(mlCounter))
-
It looks a little underdeveloped!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Yes, there isn't really any more advice we can give unless you are more specific about your requirements, e.g., by posting the ASP code, or whatever SQL code you have come up with so far...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Are you posting code with < & > in it? Perhaps try uploading it somewhere as a text file and linking to it if it won't post to SQL Server Central then place a link to it within your next message. Make sure that you upload it is a .txt file rather than a .asp file so the web server to which you upload it will not attempt to execute the code!
-
This my function below Ian, any help would be appreciated thanks:
VBScript Function used to calculate number
'of working days between two dates
'----------------------------------
Function WorkingDays(msDate1, msDate2)
Dim msFirstDate
Dim msSecondDate
Dim mlArraySize
Dim dateToCheck()
Dim mlWeekdays
Dim mlCounter
msFirstDate = msDate1
msSecondDate = msDate2
'DateDiff function calculates the number of days betweeen 2 dates
mlArraySize = DateDiff("d",msFirstDate ,msSecondDate)
Redim dateToCheck(mlArraySize)
mlWeekdays = 0
For i = 0 to mlArraySize-1
'add 1 day onto the first date and assign to array
dateToCheck(i) = DateAdd("d", i, msFirstDate)
Next
mlCounter = 0
For Each Item In dateToCheck
'if dateToCheck in array is greater than 1 and less than 7
'Weekday function takes array date value & returns as a number where 0 = Sunday AND 7 = Saturday
If (Weekday(dateToCheck(mlCounter)) > 1 AND Weekday(dateToCheck(mlCounter)) <7) Then
'assign number to mlWeekdays variable and increment by 1 every time condition is true
mlWeekdays = mlWeekdays + 1
End If
'increment counter by 1 every time through the loop
mlCounter = mlCounter + 1
Next
WorkingDays = mlWeekdays
End Function
-
Use a function like this, or any other similar. Call with
SELECT COUNT(*) Workdays FROM dbo.fnSeqDates('8/1/2006', '8/31/2006') WHERE DATEPART(dw, SeqDate) BETWEEN 2 AND 6
or
SELECT SeqDate Dates FROM dbo.fnSeqDates('8/31/2006', '8/1/2006') WHERE DATEPART(dw, SeqDate) BETWEEN 2 AND 6
or
SELECT MONTH(SeqDate) [Month],
MIN(SeqDate) FirstDate,
MAX(SeqDate) LastDate,
SUM(CASE WHEN DATEPART(dw, SeqDate) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) WorkDays
FROM dbo.fnSeqDates('1/1/2006', '12/31/2006')
GROUP BY MONTH(SeqDate)
-------------------------------------------------------------------------
Read about how my function works in my latest article at
How many more mondays until I retire?
-------------------------------------------------------------------------
CREATE FUNCTION dbo.fnSeqDates
(
@LowLimit DATETIME,
@HighLimit DATETIME
)
RETURNS @Values TABLE
(
SeqDate DATETIME
)
AS
-- Copyright 2006 Peter Larsson
BEGIN
DECLARE @Temp DATETIME
IF @LowLimit > @HighLimit
SELECT @Temp = @LowLimit,
@LowLimit = @HighLimit,
@HighLimit = @Temp
INSERT @Values VALUES (@LowLimit)
WHILE @@ROWCOUNT > 0
INSERT @Values
SELECT DATEADD(dd, t.Items, d.SeqDate)
FROM @Values d
CROSS JOIN (
SELECT COUNT(*) Items
FROM @Values
) t
WHERE DATEADD(dd, t.Items, d.SeqDate) <= @HighLimit
RETURN
END
N 56°04'39.16"
E 12°55'05.25" -
If you do not want the versatility of my function fnSeqDates, this is a rewrite only of your VBA script.
CREATE FUNCTION dbo.fnWorkingDays
(
@msDate1 DATETIME,
@msDate2 DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Days INT
WHILE @msDate1 <= @msDate2
SELECT @Days = ISNULL(@Days, 0) + CASE WHEN DATEPART(dw, @msDate1) BETWEEN 2 AND 6 THEN 1 ELSE 0 END,
@msDate1 = DATEADD(dd, 1, @msDate1)
RETURN @Days -- If @msDate1 is later than @msDate2, then return null
END
Call with SELECT dbo.fnWorkingDays('8/1/2006', '8/31/2006').
N 56°04'39.16"
E 12°55'05.25" -
Thanks for your help Peter & excellent advice,
I'm new to SQL Server and not long graduated so i've tried to write the Stored Procedure called upSys_AllCellSites and and a User Defined Function called SiteVisitWorkingDays.
The idea is to test two date columns for every record from a table in the database and return the number of working days between the two dates. The syntax is checking successfully in SQL Server but when I try to preview it in the web page it just seems to timeout.
I'm trying to figure out how to debug Stored Procedures in SQL Server, as I said I am new to SQL server and appreciate your help and advice. I have listed my Stored Procedure and User defined Function below and any help would be appreciated.
CREATE PROCEDURE [dbo].[upSys_AllCellSites]
AS
BEGIN
DECLARE @cellSQLString nvarchar(4000)
SET NOCOUNT ON
SET DATEFORMAT dmy
-- Defaults
SET @cellSQLString=''
SET @cellSQLString=' SELECT tblCellSite.ID,
tblCellSite.CellID, tblCellSite.CRSName,
tblCellSite.SiteName, tblCellSite.SiteAddress,
tblCellSite.Status,
dbo.SiteVisitWorkingDays(tblCellSite.SiteVisitDate, tblCellSite.SurveyReportIssued) AS WorkingDays
FROM dbo.tblCellSite '
EXECUTE (@cellSQLString)
END
GO
SiteVisitWorkingDays:
CREATE FUNCTION [dbo].[SiteVisitWorkingDays]
(
@SiteVisitDate datetime,
@SurveyReportIssued datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @WorkingDays int
DECLARE @msFirstDate datetime
DECLARE @msSecondDate datetime
DECLARE @mlWeekdays int
DECLARE @mlWorkingdays int
DECLARE @cnt int
DECLARE @days int
set @msFirstDate = @SiteVisitDate
set @msSecondDate = @SurveyReportIssued
set @days = 0
set @days = DateDiff(d,@msFirstDate ,@msSecondDate)
set @mlWorkingdays=0
set @cnt=0
while @cnt<=@days
begin
If @days0
BEGIN
set @mlWeekdays=DATEPART(WEEKDAY,DateAdd(d,@cnt,@msFirstDate))
If (@mlWeekdays>1) AND (@mlWeekdays<7)
BEGIN
Set @mlWorkingdays=@mlWorkingdays+1
END
END
END
RETURN @mlWorkingdays
END
Cheers
Mark
-
Of course you will experience a time-out! Your variable @cnt in the function never increments and thus will WHILE loop never exit. Please use a proper algorithm. Use this one, please. Don't be afraid to use them. Parameters are passed ByVal as default, not ByRef as in VBScript, in SQL Server.
ALTER FUNCTION dbo.SiteVisitWorkingDays
(
@SiteVisitDate DATETIME,
@SurveyReportIssued DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Days INT
WHILE @SiteVisitDate <= @SurveyReportIssued
SELECT @Days = ISNULL(@Days, 0) + CASE WHEN DATEPART(dw, @SiteVisitDate) BETWEEN 2 AND 6 THEN 1 ELSE 0 END,
@SiteVisitDate = DATEADD(dd, 1, @SiteVisitDate)
RETURN @Days
END
Also, there is no really need for dynamic SQL in a stored procedure like yours. Use this one instead, please.
ALTER PROCEDURE dbo.upSys_AllCellSites
AS
SELECT ID,
CellID,
CRSName,
SiteName,
SiteAddress,
Status,
dbo.SiteVisitWorkingDays(SiteVisitDate, SurveyReportIssued) WorkingDays
FROM dbo.tblCellSite
GO
What did that have for impact on speed?
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply