December 8, 2009 at 10:03 am
Hello experts,
I’m trying to write a dynamic SQL within cursor & having a little challenge. The basic idea is to compare columnType Column between today’s date and yesterday’s date. I’ve captured today’s columnType and other columns which I need (within cursor). To get yesterday’s columnType I’m tying to write a select statement. Furthermore I’m using a few variables to hold the value of curser. Now when I try to write SQL query using these variables and hold yesterday’s columnType value within variable @ColTypeYesterday I don’t see any value. However all the variables which I’m using in SQL to capture yesterday date contains value. Can anybody see what the problem is and how I can fix the mistake?
For reference here is my code
-----------------------------------------------------------------------------------------------
--Variables to hold cursor's data FOR TODAY
Declare @ColNumber bigint,
@CapturedDate datetime,
@ColName varchar(300),
@TbName varchar(500),
@DbName varchar(60),
@ColId int,
@ColTypeToday varchar(20),
@ColLastModifiedDate datetime,
@ColLengthToday varchar(20),
--Hold yesterday's ColumnType & ColumnLength
@ColTypeYesterday varchar(20),
@ColLengthYesterday varchar(20)
--Cusror to hold Modified Data info FOR TODAY
Declare ColumnModified_Cur Cursor
For
select ColNumber, CapturedDate, ColName, TbName, DbName, ColId, ColType, ColLastModifiedDate, ColLength
from TotalColumn
where CapturedDate = (select top 1 capturedDate from TotalDatabase order by capturedDate desc)
and ColLastModifiedDate >= (select top 1 capturedDate from TotalDatabase order by capturedDate desc)
Open ColumnModified_Cur
Fetch Next from ColumnModified_Cur
Into @ColNumber, @CapturedDate, @ColName, @TbName, @DbName, @ColId, @ColTypeToday, @ColLastModifiedDate, @ColLengthToday
while @@FETCH_STATUS = 0
begin
print @ColNumber
print @CapturedDate
print @ColName
print @TbName
print @DbName
print @ColId
print @ColTypeToday
print @ColLastModifiedDate
print @ColLengthToday
print '---------------------------------'
--Captured Yesterday's ColType
--‘THIS IS WHERE I’M NOT GETTING VALUE. PLEASE HELP’
select @ColTypeYesterday = ColType
from TotalColumn
where captureddate = (select top 1 capturedDate from TotalDatabase where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc) order by capturedDate desc)
and ColId = @ColId
and ColName = @ColName
and TbName = @TbName
and DbName = @DbName
print '************************************************'
print @ColTypeToday
print @ColTypeYesterday
print '************************************************'
Fetch Next from ColumnModified_Cur
Into @ColNumber, @CapturedDate, @ColName, @TbName, @DbName, @ColId, @ColTypeToday, @ColLastModifiedDate, @ColLengthToday
end
CLOSE ColumnModified_Cur
Deallocate ColumnModified_Cur
-----------------------------------------------------------------------------------------------
Thanks in advance
December 8, 2009 at 1:50 pm
Hey folks,
So seems like I found the problem, when I write the following SQL I get value
select @ColTypeYesterday = (ColType)
from TotalColumn
where captureddate = (select top 1 capturedDate from TotalDatabase
where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc)
order by capturedDate desc)
and ColId = @ColId
But then I execute the following SQL I don’t get any value in @ColTypeYesterday variable.
select @ColTypeYesterday = (ColType)
from TotalColumn
where captureddate = (select top 1 capturedDate from TotalDatabase
where capturedDate != (select top 1 capturedDate from TotalDatabase order by capturedDate desc)
order by capturedDate desc)
and ColId = @ColId
and ColName = @ColName
Can’t I use two AND in query like this? Seems like it doesn’t work here. How else can I use it because I’ve checked all these variables () has the value
Thanks a lot in advance
December 10, 2009 at 2:50 am
Hi,
do not exactly what output you need. The statement below returns all columns from table TotalColumn order by CapturedDate from new to old. If the statement covers not your needs you can extent this also by bilding a statement around of this like
SELECT * FROM (SELECT * FROM) AS tmp
SELECT tc.ColNumber, tc.CapturedDate, tc.ColName, tc.TbName, DbName, tc.ColId, tc.ColType,
tc.ColLastModifiedDate, tc.ColLength
FROM TotalColumn tc
INNER JOIN TotalDatabase tb ON tc.CapturedDate = tb.CapturedDate
AND tc.ColLastModifiedDate >= tb.CapturedDate
ORDER BY tc.CapturedDate DESC
I think a cursor is in that case not necessary because you can get all data with simple SQL statements. Avoid using cursor if you can get the same by using SQL statements.
What you are using is not dynamic SQL. Dynamic SQL is a string containing a SQL statement and will be run with sp_executesql procedure.
Regards,
Thomas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply