September 17, 2006 at 6:54 am
I have a stored proc defined below and wanted to know how should i display the data (data is being displayed when debugged) but no recordset data is generated while executing it. How to overcome that.
Pls help. Following is the proc for your appraisal how should i do it to make data visible while reporting.
CREATE PROCEDURE xp_TabDepr
AS
declare @id bigint
declare @Period int
declare @MnthName varchar(10)
declare @Amount numeric(19,2)
declare @tempCursor as Cursor
declare @sql as varchar(8000)
declare @sql1 as varchar(8000)
BEGIN TRANSACTION
delete from deprTest
--CREATE TABLE #deprTest(id bigint, Period int, Apr numeric(19, 2), May numeric(19, 2), Jun numeric(19, 2), Jul numeric(19, 2), Aug numeric(19, 2), Sep numeric(19,
--2), Oct numeric(19, 2), Nov numeric(19, 2), Dec numeric(19, 2), Jan numeric(19, 2), Feb numeric(19, 2), Mar numeric(19, 2))
SET @tempcursor = CURSOR FAST_FORWARD FOR select id,Period,MnthName,Amount from vwDeprMonthly
open @tempCursor
fetch next from @tempCursor into @id,@Period,@MnthName,@Amount
while (@@fetch_status <> -1)
Begin
select * from deprTest where id=@id and eriod=@Period">Period=@Period
IF @@ROWCOUNT = 0
Begin
insert into deprTest(id,Period) values (@id, @Period)
End
set @sql =''
set @sql = 'update deprTest set [' + @MnthName + ']=' + cast(@Amount as varchar(10)) + ' where id=' + cast(@id as varchar(10))+ ' and period=' + cast(@Period as varchar(10))
--set @sql = 'update deprTest set ' + @MnthName + '=5 where id=1 and period=1'
print @sql
exec(@sql)
IF @@ERROR <> 0 GOTO dberror
fetch next from @tempCursor into @id,@Period,@MnthName,@Amount
End
Close @tempCursor
Deallocate @tempCursor
COMMIT TRANSACTION
set @sql ='select * from deprTest'
exec(@sql)
--Truncate table #deprTest
/*
set @sql1 = 'SELECT d.id, f.REG_NO, f.GROUP1 AS [Group], f.REG_DATE, f.MAKE, f.TYPE_VEHICLE, p.FY, f.Cost, d.Apr, d.May, d.Jun, d.Jul, d.Aug, d.Sep, d.Oct, d.Nov,
d.[Dec], d.Jan, d.Feb, d.Mar, ISNULL(d.Apr, 0) + ISNULL(d.May, 0) + ISNULL(d.Jun, 0) + ISNULL(d.Jul, 0) + ISNULL(d.Aug, 0) + ISNULL(d.Sep, 0)
+ ISNULL(d.Oct, 0) + ISNULL(d.Nov, 0) + ISNULL(d.[Dec], 0) + ISNULL(d.Jan, 0) + ISNULL(d.Feb, 0) + ISNULL(d.Mar, 0) AS Total
FROM #deprTest d INNER JOIN
FLEET f ON d.id = f.ID INNER JOIN
vwFYPeriod p ON d.Period = p.Period
ORDER BY f.type,f.REG_DATE, f.REG_NO, d.Period'
exec(@sql1)
*/
RETURN
dberror:
ROLLBACK TRANSACTION
RETURN
GO
September 17, 2006 at 8:42 am
1.Does the user that is used while calling sp have appropriate rights to delete /add/Update data in tables? It could be when u excute it from Query Analyer that u are running under security context of your login and so runs under a different login that doesn't have enough rights.
2. After print @sql have u pasted the results from Query analyser and ran it "manually" from Query Analyser.It could be one of the Column Name in update statement is incorrect and hence rolls back all the data.
Hope this helps
Thanks
Sreejith
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
