checking for a column and printing a message

Question

Post reply

checking for a column and printing a message

SQLTestUser

Hall of Fame

Points: 3997
More actions
July 21, 2010 at 12:46 pm

#221504

This is what i am trying to do I akm trying to update a department as inactive and if the department doesnot exist i am trying to print a message as doesnot exist as
If exists
(Select * from departments )
Update Dept Set IsActive = 0
where deptID = '106' and areaID = '20'
Else
print 'department Doesnot Exist dude'
please let me know what am i doin wrong, for some reason it either updates or shows me the message saying that no rows updated

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 1

i didnot have the where clause in select which is why it was not working

If exists

(Select * from departments where XXID = 106 and XXID = 20)

Update Departments Set IsActive = 0

where XXXID = 106 and XXXID = 20

Else

print 'department and plant Doesnot Exist '

if someone has a better way to do this let me knwo coz i have to use this frequently

MAY BE HAVE A TEMP TABLE STORE IT THERE and then update it if everyhitng is cool

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

declare @deptid int

SEt @deptid = 24

if not exists ( select * from department where deptid = @deptid )

print 'department Doesnot Exist dude'

ELSE

update .....

Where deptid = @deptid

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #Departments

CREATE TABLE #Departments (deptID INT, areaID INT, IsActive TINYINT)

INSERT INTO #Departments (deptID, areaID, IsActive)

SELECT 102, 20, 1 UNION ALL

SELECT 103, 20, 1 UNION ALL

SELECT 104, 20, 1 UNION ALL

SELECT 105, 20, 1 UNION ALL

SELECT 106, 20, 1

UPDATE #Departments SET IsActive = 0 WHERE deptID = 106 and areaID = 20

IF @@ROWCOUNT > 0 PRINT 'UPDATED'

DELETE FROM #Departments WHERE deptID = 106 and areaID = 20

UPDATE #Departments SET IsActive = 0 WHERE deptID = 106 and areaID = 20

IF @@ROWCOUNT = 0 PRINT 'NOT FOUND'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden