Using a Cursor in SELECT statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a Cursor in SELECT statement

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-18 : 03:44:40

Can anyone help with the following? I'm trying to use a Cursor within a select statement, can it be done?

My statement


Declare @i1 int
Declare @i2 int
Declare @d1 DATETIME
Declare @t1 VARCHAR(10)

SELECT 
a.persno,a.date1,a.code
,daycount =
(
DECLARE Dcursor CURSOR FOR
SELECT DISTINCT b.persno,b.date1,b.code from table1 b
WHERE b.persno = a.persno
AND b.date1 = a.date1
AND b.code = a.code
ORDER BY b.persno
OPEN Dcursor
FETCH NEXT FROM Dcursor INTO @i1,@d1,@t1
WHILE @@FETCH_STATUS = 0
BEGIN
IF @t1 = 'Y' 
@i2 = DATEDIFF(d,@d1,GETDATE())
FETCH NEXT FROM Dcursor INTO @i1,@d1,@t1  
END  
CLOSE Dcursor  
DEALLOCATE Dcursor
    RETURN @i2

)
into table2
FROM table1

I'm getting an error :-
Incorrect syntax near the keyword 'DECLARE'

Any ideas as to how to get this working?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 03:48:54

you dont need cursor here at all.
use something like


SELECT 
a.persno,a.date1,a.code
,daycount =CASE WHEN a.code='Y' THEN DATEDIFF(d,a.date1,GETDATE()) END
into table2
FROM table1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-02-18 : 03:55:35

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 03:58:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources