|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
September 17th, 2004, 05:23 AM
|
|||
|
|||
|
Trouble passing parameters from query to UDF
I'm not sure if this is the right way to get the required results but here we go....
I've got a table (in it's simplest form) with 3 fields, ID number, User Name, Status. Some values in the user field are null but the there are only six users I'm after (see the WHERE in the code below). I want four values from this table for each user. (also using a date range) 1) Total number of records with the User value being any of six values. (See above) 2) Same as above but having any of three statuses. 3) Total number of records for that user. 4) Same as above but having any of three statuses. Now because I want to use the same table four times with different criteria I thought I could use UDF for each value. I'm not 100% sure about crosstab queries so I'm not sure if this is the right method to use. I'm passing the function 3 parameters, date from, date to, FRFTier. FRFTier is the field where the username comes from (could have been named better originally I know). dbo.fnReport_MonthlyBusinessReviewDealCount(@Date_From, @Date_To, FRFTier) AS IndivCount Stored procedure code... Code:
SELECT @Date_From AS dtmFrom, @Date_To AS dtmTo, @Total_Approved_Target AS numApprovedTarget, @Total_Dealt_With_Target AS numDealtTarget,
FRFTier, dbo.fnReport_MonthlyBusinessReviewChoiceCount(@Date_From, @Date_To) AS AppCount,
dbo.fnReport_MonthlyBusinessReviewChoiceCountDealt(@Date_From, @Date_To) AS DealtCount,
dbo.fnReport_MonthlyBusinessReviewDealCount(@Date_From, @Date_To, FRFTier) AS IndivCount,
dbo.fnReport_MonthlyBusinessReviewDealtCount(@Date_From, @Date_To, FRFTier) AS IndivDealtCount
FROM dbo.tblApplications
WHERE ({ fn LENGTH(FRFTier) } > 3) AND (FRFTier <> 'None') AND (FRFTier <> 'Failed') AND (FRFTier <> '040704')
GROUP BY FRFTier
UDF Code... Code:
ALTER FUNCTION dbo.fnReport_MonthlyBusinessReviewDealCount
(
@Date_From datetime,
@Date_To datetime,
@Name nvarchar
)
RETURNS int
AS
BEGIN
DECLARE @COUNT int
SET @COUNT = (SELECT COUNT(*) AS Expr1
FROM dbo.tblApplications
WHERE (dtmApplicationDate >= @Date_From) AND (dtmApplicationDate <= @Date_To + 1) AND (FRFTier = @Name))
RETURN @COUNT
END
These are the results I'm getting... the users values are all 0 which are wrong. From - To - Target 1 - Target 2 - User - Total - Total Dealt - User Total - User Total Dealt 01/08/2004 17/08/2004 50 2 Kate S 378 52 0 0 01/08/2004 17/08/2004 50 2 Mel W 378 52 0 0 01/08/2004 17/08/2004 50 2 Paul H 378 52 0 0 01/08/2004 17/08/2004 50 2 Zoe S 378 52 0 0 01/08/2004 17/08/2004 50 2 Mark S 378 52 0 0 01/08/2004 17/08/2004 50 2 Carole B 378 52 0 0 Is there are better way to go about this or can anyone see an error in the SP or UDF? Thanks.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Trouble passing parameters from query to UDF |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
