Can i use CASE or IF statement within a curser? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Can i use CASE or IF statement within a curser?

Author

Topic

naush83
Starting Member

2 Posts

Posted - 2009-08-12 : 10:12:10

Hi,

I have a curser in my stored procedure which looks like below.
DECLARE db_cursor CURSOR FOR
SELECT FName,LName
FROM employee
WHERE EmpID > 100

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @FName, @LName.
.....................
....................
But I want the select statement to be changed based on some condition like given below.

Assume @mode is an input parameter.

DECLARE db_cursor CURSOR FOR

case @mode when 'A' then
SELECT FName,LName
FROM employee
WHERE EmpID > 100

case @mode when 'B' then
SELECT FName,LName
FROM employee
WHERE EmpID > 100 and Age < 50

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @FName, @LName.
.....................
....................
But the above query doesn't work. How can i use conditional statement (case or if) within a curser?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 11:01:33

Well you probably don't want to be using a cursor in the first place... However, you can do 1 of two things

You could do this:


DECLARE db_cursor CURSOR FOR 

SELECT
	[fName]
	, [Lname]
FROM
	employee
WHERE
	[empId] > 100
	AND (
		[age] < CASE @mode
			WHEN 'A' THEN [age] + 1
			ELSE 50
		END
		OR
		[age] IS NULL
	)

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @FName, @LName.

OR you could have a simple IF condition and then declare the cursor differently depending on whether the @mode is A or B.

Like


IF @mode = 'A' BEGIN
    DECLARE db_cursor CURSOR FOR......
    .....
END
ELSE BEGIN
    DECLARE db_Cursor CURSOR FOR.......
    .....
END
...
...

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

naush83
Starting Member

2 Posts

Posted - 2009-08-13 : 10:21:57

Thanks dear. It works fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources