July 22, 2005 at 9:51 am
I am having problems setting a variable.
In the code below, I am opening a cursor, changing some values for the current row, updating that row and then moving to the next row.
Becuase I am using dynamically assigned table names I am using the sp_executesql command to run a @SqlString variable. However I am unable to assign this variable a value. I use the debugger to step through and the variable just gets set to NULL.
Any ideas please.
Actual code below...
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),
@New_v_Temp 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),
@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)
DECLARE @LastIssueText nvarchar(50)
SET @NewDeferredRevenue = 'DeferredRevenue_' + @WorkstationId
SET @NewDeferredRevenueSummary = 'DeferredRevenueSummary_' + @WorkstationId
SET @New_v_Temp = 'v_Temp_' + @WorkstationId
BEGIN
IF OBJECT_ID(@New_v_Temp) IS NOT NULL
EXEC ('DROP VIEW ' + @New_v_Temp)
END
BEGIN
SET @FirstDay = convert(nvarchar(2), @PubMonth) + '/' + convert(nvarchar(3), '01/') + convert(nvarchar(4), @PubYear)
SET @LastDay = dateadd(month, 1, @FirstDay)
SET @LastDay = dateadd(day, -1, @LastDay)
--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 = 'CREATE VIEW ' + @New_v_Temp +
' AS
SELECT DISTINCT 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) + ')'
--PRINT @SqlString
EXECUTE sp_executesql @SqlString
END
BEGIN
SET @SqlString = N'SELECT @OUT = COUNT(IssueNo) FROM ' + @New_v_Temp
SET @params = N'@OUT int OUTPUT'
--PRINT @SqlString
EXECUTE sp_executesql @SqlString, @params, @OUT= @IssuesInMonth OUTPUT
IF @IssuesInMonth IS NULL
SET @IssuesInMonth = 0
--print @IssuesInMonth
END
BEGIN
IF OBJECT_ID(@New_v_Temp) IS NOT NULL
EXEC ('DROP VIEW ' + @New_v_Temp)
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 @Num = 0
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 @Num = @Num + 1
select @Num
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) + REPLACE(@Company, char(39), char(39) + char(39)) + 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'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
July 22, 2005 at 10:47 am
That's cool cheers Ray. Will look at that.
July 22, 2005 at 10:49 am
Err, I deleted my post, Because I'm not sure that was the problem,
But start out by setting @sqlstring = ''
But in Debugger, check the line that it gets set to null, and check that any fields or parameters your concatenating together are not null. If they are it will set your string to null.
July 22, 2005 at 10:54 am
Thanks Ray
Was indeed NULL field values.
July 22, 2005 at 10:56 am
Cool, So just wrap that field in a coalesce(fieldName,'') and you should be all good.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply