Is it Possible to insert an "IF EXISTS" statement within a Cursor

Question

Post reply

Is it Possible to insert an "IF EXISTS" statement within a Cursor

John-79567

SSCommitted

Points: 1892
More actions
December 13, 2005 at 8:33 am

#166257

My Cursor keeps closing once the IF EXISTS condition is met. Thanks in advance.

Sample Code:
DECLARE @stuff INT
Declare getStuffCursor CURSOR FOR
     Select ID from tableA
OPEN getStuffCursor
FETCH NEXT FROM getStuffCursor
INTO @stuff
WHILE @@Fetch_Status = 0
BEGIN
     IF EXISTS (SELECT ID FROM tableB where ID = @stuff)
          BEGIN
                 SELECT A.ID, B.ID, CASE WHEN A.ID > 1 THEN 100 ELSE 200 END
                 FROM tableA A, tableB B
                 WHERE A.ID = B.ID
          END
     ELSE
            BEGIN
                   PRINT 'This ID doesnt not exist in tableB' + convert(varchar(10), @Stuff)
            END
FETCH NEXT FROM getStuffCursor
INTO @stuff
END
CLOSE getStuffCursor
DEALLOCATE getStuffCursor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Darwin Hatheway SSC Veteran Points: 242 More actions · Answer 1

I'm not sure if you can use "IF EXISTS" in a cursor or not but in your case, you could perhaps use

TableA A left outer join TableB B on A.ID = B.ID

If there's no B record, B.ID will be null. You coul then use a CASE epression on B.ID. If the sole purpose of the cursor is to handle the EXISTS conditional, maybe you can use the outer join to eliminate the cursor altogether and accomplish your task in set-oriented fashion?

Caution: I didn't think deeply about your problem - this idea just jumped out at me - and this suggestion may not work out for you at all.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

John - if what you've posted is the full extent of your procedure, why not use a left outer join instead ?! why use a cursor at all ?!

edited - notice that a similar suggestion has just been posted.

**ASCII stupid question, get a stupid ANSI !!!**

deepforest SSCommitted Points: 1780 More actions · Answer 3

yeah, left join the tables, and handle the nulls, ok, I see...well, everyone obviously sees...

John-79567 SSCommitted Points: 1892 More actions · Answer 4

I wasnt sure how to do this any other way. Im relatively new to TSQL. The problem i have is that i will need to perform 2 different functions based on whether or not a record exist in tableB. If the record exist I need to Insert different values based on different criteria.

Example.

If record exist in tableB. Then insert ID, Case CurrentAmount - Transaction > 10 then 5 Else 25

If record doesnt exist in tableB. Then insert ID, 25

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

john - why don't you post the ddl & sample rows from your 2 tables...can't test this but if you try something like this, it should work...

SELECT A.ID, B.ID, CASE 
                        WHEN (A.CurrentAmount - A.[Transaction] > 10) THEN 5
                        WHEN B.ID IS NULL THEN 25
ELSE 25
                   END AS additionalColumn
FROM tableA A
LEFT OUTER JOIN  tableB B
ON A.ID = B.ID
ORDER BY A.ID

btw - how will you distinguish between the '25' that is being set for an ID that doesn't exist and the '25' that is being set for 'CurrentAmount - Transaction < 10'

**ASCII stupid question, get a stupid ANSI !!!**

John-79567 SSCommitted Points: 1892 More actions · Answer 6

Bank Table:

CREATE TABLE [dbo].[tblCompanyStoreBankDetail] (

[TransactionID] [int] IDENTITY (1, 1) ,

[TMID] [int] ,

[TransactionTypeID] [int] ,

[TransactionAmount] [money] ,

[AccountBalance] [money] ,

[TransactionDate] [datetime]

) ON [PRIMARY]

Employee Table:

CREATE TABLE [dbo].[Team_Members] (

[TM_Identity] [int] IDENTITY (1, 1) NOT NULL ,

[TM_Last_Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TM_First_Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TM_Middle_Initial] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TM_Position] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

&nbsp ON [PRIMARY]

Note: Team_Members.tm_identity = tblcompanystorebankdetail.tmid

I need to insert a certain amount into bank table based on current employees current balance. Then I need to insert a set amount - 25 dollars into the bank if the employee is new and has no transactions in the bank table. I was sure how to do this using a solution set. I guess i still think like a procedural developer.

John-79567 SSCommitted Points: 1892 More actions · Answer 7

Ah the wonders of Set Base solutions... Yes the left join worked wonders. I used case to designate the different pay amounts. So far everything works great. Thanks for all the help.

Papillon Hall of Fame Points: 3303 More actions · Answer 8

Hi!!!

Chk for this....

declare @id as int

if EXISTS (SELECT 1 FROM TABLEb WHERE ID = @id )

BEGIN

select a.id , b.id,case when a.id > 1 then 100 else 200 end

from tableA a , tableB b

WHERE a.id = b.id

END

ELSE

BEGIN

PRINT 'This ID ' + convert(varchar(10), @id) + ' doesnt not exist in tableB' END

Regards,

Papillon