Function returns NULL ..?
-
I am using the following function at the end of a stored procedure:SET @LastIssueText = dbo.f_IssueText('ADM', 461)but the problem is it returns a NULL value.However when I use the same line of code within SQL Query Analyser or another stored procedure that contains no other code it returns the correct value of 'May 2005'. I've looked at this over and over and cannot see any reason why it is returning a NULL in the original stored procedure.Any ideas would be very much appreciated.Thanks -
No - looks OK from what you've said. Can you post more info (eg the stored proc definition) so that we can the help further?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here's the code. Not too easy to read here but I'm sure if you cut and paste it'll be easier. The function call in question is near the end of the main code after the deallocation of the cursor.
You'll probably think the rest of my coding is awful (it is) but I do not profess to be that proficient with SQL having only been using for a month or so.
Thanks
----------
CREATE FUNCTION [dbo].[f_IssueText] (@PubCode nvarchar(10), @Issue int)
RETURNS nvarchar(20) AS
BEGIN
DECLARE@ReturnValue nvarchar(20),
@YP nvarchar(10),
@Vol nvarchar(10),
@Preferred nvarchar(10)
SELECT @Preferred = Preferred FROM Titles WHERE PubCode = @PubCode
IF @Preferred = 'YM'
BEGIN
SET @YP = dbo.f_IssueToYearPeriod(@PubCode, @Issue)
SET @ReturnValue = LEFT(@YP, 4) + 'Mnth: ' + SUBSTRING(@YP, 5, 2)
IF SUBSTRING(@YP,5,2) = '01'
BEGIN
SET @ReturnValue = 'Jan ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '02'
BEGIN
SET @ReturnValue = 'Feb ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '03'
BEGIN
SET @ReturnValue = 'Mar ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '04'
BEGIN
SET @ReturnValue = 'Apr ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '05'
BEGIN
SET @ReturnValue = 'May ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '06'
BEGIN
SET @ReturnValue = 'Jun ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '07'
BEGIN
IF @PubCode = 'ADM'
BEGIN
SET @ReturnValue = 'Jul/Aug ' + LEFT(@YP, 4)
END
ELSE
BEGIN
SET @ReturnValue = 'Jul ' + LEFT(@YP, 4)
END
END
IF SUBSTRING(@YP,5,2) = '08'
BEGIN
SET @ReturnValue = 'Aug ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '09'
BEGIN
SET @ReturnValue = 'Sep ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '10'
BEGIN
SET @ReturnValue = 'Oct ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '11'
BEGIN
SET @ReturnValue = 'Nov ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP,5,2) = '12'
BEGIN
SET @ReturnValue = 'Dec ' + LEFT(@YP, 4)
END
END
IF @Preferred = 'YQ'
BEGIN
SET @YP = dbo.f_IssueToYearPeriod(@PubCode, @Issue)
SET @ReturnValue = LEFT(@YP, 4) + ' Q' + SUBSTRING(@YP, 6, 1)
IF @PubCode = 'GPT'
BEGIN
SET @ReturnValue = LEFT(@YP, 4) + '/' + (LEFT(@YP, 4) + 1)
END
IF @PubCode = 'MKTL'
BEGIN
IF SUBSTRING(@YP, 5, 2) = '01'
BEGIN
SET @ReturnValue = 'Spring ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP, 5, 2) = '02'
BEGIN
SET @ReturnValue = 'Summer ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP, 5, 2) = '03'
BEGIN
SET @ReturnValue = 'Autumn ' + LEFT(@YP, 4)
END
IF SUBSTRING(@YP, 5, 2) = '04'
BEGIN
SET @ReturnValue = 'Winter ' + LEFT(@YP, 4)
END
IF @YP = '199802Q'
BEGIN
SET @ReturnValue = 'Spring 1998'
END
IF @YP = '199904Q'
BEGIN
SET @ReturnValue = 'Winter 1999/2000'
END
IF @YP = '200004Q'
BEGIN
SET @ReturnValue = 'Winter 2000/2001'
END
END
END
IF @Preferred = 'V'
BEGIN
SET @Vol = dbo.f_IssueToVolNo(@PubCode, @Issue)
SET @ReturnValue = 'Vol ' + LEFT(@Vol, LEN(@Vol) - 3) + ' No ' +
LEFT(RIGHT(@Vol, 3), 2)
END
IF @Preferred = 'VY'
BEGIN
SET @Vol = dbo.f_IssueToVolNo(@PubCode, @Issue)
SET @ReturnValue = 'Vol ' + LEFT(@Vol, LEN(@Vol) - 3) + ' No ' +
LEFT(RIGHT(@Vol, 3), 2)
END
IF @PubCode = 'JMRS'
BEGIN
IF @Issue <= 184
BEGIN
SET @YP = dbo.f_IssueToYearPeriod(@PubCode, @Issue)
SET @Vol = dbo.f_IssueToVolNo(@PubCode, @Issue)
SET @ReturnValue = LEFT(@YP, 4) + ' Q' + LEFT(RIGHT(@Vol, 2), 1)
END
ELSE
BEGIN
SET @YP = dbo.f_IssueToYearPeriod(@PubCode, @Issue)
SET @Vol = dbo.f_IssueToVolNo(@PubCode, @Issue)
SET @ReturnValue = LEFT(@YP, 4) + ' No ' + LEFT(RIGHT(@Vol, 3), 2)
END
END
return @ReturnValue
END
----------
CREATE PROCEDURE [dbo].sp_DefRev
@PubCode nvarchar(10),
@PubYear int,
@PubMonth int,
@Type int,
@WorkstationId nvarchar(10)
AS
DECLARE@NewDeferredRevenue nvarchar(50),
@NewDeferredRevenueSummary nvarchar(50)
DECLARE@SqlString nvarchar(4000),
@params nvarchar(500)
DECLARE@LastDay smalldatetime,
@FirstDay smalldatetime,
@LastIssue float,
@IssuesInMonth float,
@Num int,
@RecptDate nvarchar(20),
@Amount nvarchar(20),
@PubTitle nvarchar(30),
@LastIssueText nvarchar(50),
@NumRecs nvarchar(20),
@FirstInMonth float
DECLARE@Date smalldatetime,
@StartIssue float,
@EndIssue float,
@InvCurrency nvarchar(20),
@GOODS float,
@vat float,
@TotalReceived float,
@RecvdThisMonth float,
@VATThisMonth float,
@IncomeThisMonth float,
@IncomeCfwd float,
@Royalty float,
@RoyaltyOnRecvd float,
@RoyaltyCfwd float,
@RoyaltyThisMonth float,
@Issues int,
@SubRef nvarchar(10),
@Company nvarchar(50),
@RateCode nvarchar(10)
SET @NewDeferredRevenue = 'DeferredRevenue_' + @WorkstationId
SET @NewDeferredRevenueSummary = 'DeferredRevenueSummary_' + @WorkstationId
BEGIN
SET @FirstDay = convert(nvarchar(3), '01/') + convert(nvarchar(2),
@PubMonth) + '/' + convert(nvarchar(4), @PubYear)
SET @LastDay = dateadd(month, 1, @FirstDay)
SET @LastDay = convert(nvarchar(10), dateadd(day, -1, @LastDay), 103)
END
BEGIN
SET @SqlString = N'SELECT @OUT = MAX(IssueNo) FROM [Publication Dates]
WHERE (PubCode = ' + char(39) + @PubCode + char(39) + ') AND
(Convert(char(8), [Date], 112) <= ' + Convert(char(8), @LastDay, 112) + ')'
SET @params = N'@OUT int OUTPUT'
EXECUTE sp_executesql @SqlString, @params, @OUT= @LastIssue OUTPUT
IF @LastIssue IS NULL
SET @LastIssue = 0
--print @LastIssue
END
BEGIN
SET @SqlString = N'SELECT @OUT = COUNT(IssueNo) FROM [Publication Dates]
WHERE (PubCode = ' + char(39) + @PubCode + char(39) + ') AND
(Convert(char(8), [Date], 112) >= ' + Convert(char(8), @FirstDay, 112) + ')
AND (Convert(char(8), [Date], 112) <= ' + Convert(char(8), @LastDay, 112) +
')'
SET @params = N'@OUT int OUTPUT'
EXECUTE sp_executesql @SqlString, @params, @OUT= @IssuesInMonth OUTPUT
IF @IssuesInMonth IS NULL
SET @IssuesInMonth = 0
--print @IssuesInMonth
END
BEGIN
SET @FirstInMonth = @LastIssue - @IssuesInMonth + 1
--print @FirstInMonth
END
BEGIN
SET @SqlString = N'INSERT INTO ' + @NewDeferredRevenue +
' (SubRef, Company, Date, [Total£Received], Goods,
VAT, InvCurrency, StartIssue, EndIssue, RateCode, Royalty, PubCode)
SELECT DISTINCT
Transactions.SubRef, Companies.Company,
Transactions.[Date], Transactions.[Total£Received], Transactions.Goods,
Transactions.VAT,
Transactions.InvCurrency, Transactions.StartIssue,
Transactions.EndIssue, Transactions.RateCode, Transactions.Royalty,
Transactions.PubCode
FROM Transactions INNER JOIN
Subscriptions ON Transactions.SubRef =
Subscriptions.SubRef INNER JOIN
People INNER JOIN
Companies ON People.CoRef = Companies.CoRef ON
Subscriptions.InvPersRef = People.PersRef
WHERE (Convert(char(8), Transactions.[Date], 112) >= ' +
Convert(char(8), @FirstDay, 112) + ') AND (Convert(char(8),
Transactions.[Date], 112) <= ' + Convert(char(8), @LastDay, 112) + ') AND
(Transactions.[Total£Received] <> 0) AND (Transactions.PubCode = ' + char(39)
+ @PubCode + char(39) + ') AND (Transactions.TranType = ' + char(39) +
'PAYMENT' + char(39) + ' OR
Transactions.TranType = ' + char(39) + 'REFUND' +
char(39) + ') OR
(Convert(char(8), Transactions.[Date], 112) < ' +
Convert(char(8), @FirstDay, 112) + ') AND (Transactions.[Total£Received] <>
0) AND
(Transactions.PubCode = ' + char(39) + @PubCode +
char(39) + ') AND (Transactions.TranType = ' + char(39) + 'PAYMENT' +
char(39) + ' OR
Transactions.TranType = ' + char(39) + 'REFUND' +
char(39) + ') AND (Convert(char(8), Transactions.EndIssue) >= ' +
Convert(nvarchar(8), @FirstInMonth) + ')
ORDER BY Transactions.[Date], Transactions.SubRef'
--print @sqlstring
EXECUTE sp_executesql @SqlString
END
BEGIN
IF @Type <> -1
EXEC ('UPDATE ' + @NewDeferredRevenue +
' SET [Recvd This Month] = 0, [VAT This Month] = 0, [Income
This Month] = 0, [Income cfwd] = 0, [Royalty on Recvd] = 0, [Royalty This
Month] = 0,
[Royalty cfwd] = 0')
END
--Loop start
BEGIN
SET @SqlString = N'DECLARE curDefRev CURSOR FOR SELECT SubRef, Company,
[Date], [Total£Received], Goods, VAT, InvCurrency, StartIssue, EndIssue,
RateCode, Royalty, PubCode, [Recvd This Month], [VAT this Month], [Income
This Month], [Income Cfwd], [Royalty On Recvd], [Royalty This Month],
[Royalty Cfwd] FROM ' + @NewDeferredRevenue
EXECUTE sp_executesql @SqlString
OPEN curDefRev
FETCH NEXT FROM curDefRev INTO @SubRef, @Company, @Date, @TotalReceived,
@GOODS, @vat, @InvCurrency, @StartIssue, @EndIssue, @RateCode, @Royalty,
@PubCode, @RecvdThisMonth, @VATThisMonth, @IncomeThisMonth, @IncomeCfwd,
@RoyaltyOnRecvd, @RoyaltyThisMonth, @RoyaltyCfwd
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @Issues = @EndIssue - @StartIssue + 1
SET @RecptDate = @Date
END
BEGIN
IF @InvCurrency <> '£'
SET @vat = @vat / @GOODS * @TotalReceived
END
BEGIN
SET @Amount = @TotalReceived - @vat
END
BEGIN
IF @RecptDate >= @FirstDay AND @RecptDate <= @LastDay
BEGIN
SET @RecvdThisMonth = @Amount
SET @VATThisMonth = @vat
IF @StartIssue <= @LastIssue
BEGIN
IF @EndIssue > @LastIssue
BEGIN
SET @IncomeThisMonth = (@LastIssue - @StartIssue + 1) / @Issues *
@Amount
SET @IncomeCfwd = (@EndIssue - @LastIssue) / @Issues * @Amount
END
ELSE
BEGIN
SET @IncomeThisMonth = @Amount
END
END
ELSE
BEGIN
SET @IncomeCfwd = @Amount
END
END
ELSE
BEGIN
IF @StartIssue <= @LastIssue AND @EndIssue >= @LastIssue -
@IssuesInMonth + 1
BEGIN
IF @LastIssue - @StartIssue + 1 < @IssuesInMonth
BEGIN
SET @IncomeThisMonth = (@LastIssue - @StartIssue + 1) / @Issues *
@Amount
BEGIN
IF @EndIssue > @LastIssue
BEGIN
SET @IncomeCfwd = (@EndIssue - @LastIssue) / @Issues * @Amount
END
END
END
IF @EndIssue - (@LastIssue - @IssuesInMonth + 1) + 1 < @IssuesInMonth
BEGIN
SET @IncomeThisMonth = (@EndIssue - (@LastIssue - @IssuesInMonth +
1) + 1) / @Issues * @Amount
END
IF @LastIssue - @StartIssue + 1 >= @IssuesInMonth AND @EndIssue -
(@LastIssue - @IssuesInMonth + 1) + 1 >= @IssuesInMonth
BEGIN
SET @IncomeThisMonth = @IssuesInMonth / @Issues * @Amount
IF @EndIssue > @LastIssue
BEGIN
SET @IncomeCfwd = (@EndIssue - @LastIssue) / @Issues * @Amount
END
END
END
IF @StartIssue > @LastIssue
SET @IncomeCfwd = @Amount
END
END
BEGIN
IF @IncomeThisMonth IS NOT NULL AND @Royalty <> 0
SET @RoyaltyThisMonth = @IncomeThisMonth * @Royalty / 100
END
BEGIN
IF @IncomeCfwd IS NOT NULL AND @Royalty <> 0
SET @RoyaltyCfwd = @IncomeCfwd * @Royalty / 100
END
BEGIN
IF @RecvdThisMonth IS NOT NULL AND @Royalty <> 0
SET @RoyaltyOnRecvd = @RecvdThisMonth * @Royalty / 100
END
BEGIN
SET @SqlString = N'UPDATE ' + @NewDeferredRevenue +
' SET SubRef = ' + char(39) + @SubRef + char(39) + ', Company = ' +
char(39) + dbo.f_AccomApost(@Company) + char(39) + ', [Date] = ' +
convert(char(10), @Date, 103) + ', [Total£Received] = ' +
convert(nvarchar(10), @TotalReceived) + ', Goods = ' + convert(nvarchar(10),
@GOODS) + ', VAT = ' + convert(nvarchar(10), @vat) + ', InvCurrency = ' +
char(39) + @InvCurrency + char(39) + ', StartIssue = ' +
convert(nvarchar(10), @StartIssue) + ', EndIssue = ' + convert(nvarchar(10),
@EndIssue) + ', RateCode = ' + char(39) + @RateCode + char(39) + ', Royalty =
' + convert(nvarchar(10), @Royalty) + ', PubCode = ' + char(39) + @PubCode +
char(39) + ', [Recvd This Month] = ' + convert(nvarchar(10), @RecvdThisMonth)
+ ', [VAT this Month] = ' + convert(nvarchar(10), @VATThisMonth) + ', [Income
This Month] = ' + convert(nvarchar(10), @IncomeThisMonth) + ', [Income Cfwd]
= ' + convert(nvarchar(10), @IncomeCfwd) + ', [Royalty On Recvd] = ' +
convert(nvarchar(10), @RoyaltyOnRecvd) + ', [Royalty This Month] = ' +
convert(nvarchar(10), @RoyaltyThisMonth) + ', [Royalty Cfwd] = ' +
convert(nvarchar(10), @RoyaltyCfwd) +
' WHERE CURRENT OF curDefRev'
--PRINT @SqlString
EXECUTE sp_executesql @SqlString
END
--Leave at the end
BEGIN
FETCH NEXT FROM curDefRev INTO @SubRef, @Company, @Date, @TotalReceived,
@GOODS, @vat, @InvCurrency, @StartIssue, @EndIssue, @RateCode, @Royalty,
@PubCode, @RecvdThisMonth, @VATThisMonth, @IncomeThisMonth, @IncomeCfwd,
@RoyaltyOnRecvd, @RoyaltyThisMonth, @RoyaltyCfwd
END
END
CLOSE curDefRev
DEALLOCATE curDefRev
END
--Loop End
BEGIN
IF @Type <> -1
BEGIN
BEGIN
SET @LastIssueText = dbo.f_IssueText(@PubCode, @LastIssue)
SELECT @PubTitle = Title FROM Titles WHERE PubCode = @PubCode
/*
SET @SqlString = N'SELECT @OUT = dbo.f_IssueText(' + char(39) + 'ADM' +
char(39) + ', 461)'
SET @params = N'@OUT nvarchar(50) OUTPUT'
PRINT @SqlString
EXECUTE sp_executesql @SqlString, @params, @OUT= @LastIssueText OUTPUT
*/
select @PubCode
select @LastIssue
Select @LastIssueText
SET @SqlString = N'INSERT INTO ' + @NewDeferredRevenueSummary +
' ([Recvd This Month], [VAT This Month], [Total
This Month], [Income This Month], [Income cfwd], [Royalty on Recvd],
[Royalty This Month], [Royalty cfwd])
SELECT SUM([Recvd This Month]) AS [SumOfRecvd This Month],
SUM([VAT This Month]) AS [SumOfVAT This Month],
SUM([Recvd This Month] + [VAT This Month]) AS [Total This Month], SUM([Income
This Month])
AS [SumOfIncome This Month], SUM([Income cfwd]) AS
[SumOfIncome cfwd], SUM([Royalty on Recvd]) AS [SumOfRoyalty on Recvd],
SUM([Royalty This Month]) AS [SumOfRoyalty This
Month], SUM([Royalty cfwd]) AS [SumOfRoyalty cfwd]
FROM ' + @NewDeferredRevenue
PRINT @SqlString
EXECUTE sp_executesql @SqlString
SET @SqlString = N'UPDATE ' + @NewDeferredRevenueSummary +
' SET PubCode = ' + char(39) + @PubCode + char(39)
+ ', Title = ' + char(39) + @PubTitle + char(39) + ', LastPublishedIssue = '
+ char(39) + @LastIssueText + char(39)
PRINT @SqlString
EXECUTE sp_executesql @SqlString
END
END
END
GO
-
Yikes, way too much code for me to trawl through while doing my day job!
Leaving the function as it is, are you able to put the function call (only) in a separate stored proc and still reproduce the problem?
What I'm trying to do here is work out where the problem is occuring - stored proc or function or SQL Server 'feature'?
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I suspect a 'feature' Phil.
The function does appear to work correctly as it returns the correct value if called from a store procedure that contains no other code. It also returns the correct value if called SQL Query Analyzer.
I have tried recreating the sp from scratch but that don't work. I have also moved the function call into another sp completely and that also does not work. Kind of at a dead end now really.
Pete
-
Did you use the debugger to track down the NULL problem? If not, running the stored proc step by step in debug mode might point you in the right direction. I do not really believe the 'feature' idea
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
No, I assigned the return value of the function to a variable and then selected the variable.
Is there a SQL debugger.?
My version of Visual Studio does not support debugging of sp.? When we move to 2005 this will be a prerequisite of the version we move to.
I do appear to be closing in on the problem however. When I comment out a large chunk of my code (to do with the cursor) this function works. I'll keep looking.
Pete
-
Sounds promising ...
Debugging: right-click the stored proc in the QA object browser. 'Debug' should be one of the options available to you.
Regards.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hmm, still trying to get debugging to work. I right click on my 'sp' and select debug. This shows my code in the right pane. However I seem unable to step though. The only buttons highlighted are 'Go' and the 2 breakpoint buttons. Only when I click 'go' do the step through, step over, etc buttons become highlighted by which time it is too late as my sp is already processing.
However have narrowed my prob down to this one line of code :
WHILE @@FETCH_STATUS = 0
If I comment this out all works fine although only on 1 record instead of looping though my dataset.
-
OK - the code is in the right pane and there should be a yellow arrow pointing to the first line of executable code (after any declarations).
Just use the step into, step over etc buttons as you would in Visual Studio to step through the code line by line.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
No yellow arrow.
I suspect the problem must be that when I click 'execute' to debug, I get the following msg "SP debugging may not work properly if you log on as 'Local System Account' while SQL Server is configured to run as a service."
I appreciate your help and time Phil but cannot expect you to keep trying to sort out my probs.
-
I think you're right - I tend to do dev and debugging on my PC and so it all works fine for me.
No problem about the help - people in this forum have put plenty of effort into helping me in the past & I'm happy to put something back if I can.
Good luck with your problem solving - sounds like you're narrowing it down slowly.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks a lot Phil. Got the debugger working (that could have saved me so much time over the last 2 months) and sorted my code problem with a few minutes.
-
Ace.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply