Stored Procedure bproblem

Posted by Bhanubysani under Sql Server on 8/8/2011 | Points: 10 | Views : 1438 | Status : [Member] | Replies : 5
hi

i have 2 tables empoloyee with columns empname,empid,deptid and department deptname,deptid..

i want to write a stored procedure for delete the department when there is no employees existing in taht department other wise the department is not deleted..

for that iam writing 2 sored procedure one for checking the employees exist and 2nd one for deleeting the department..

i want to write single stored procedure for that..plz help me in writing the procedure..

Regards,
Bhanu Prakash Bysani



Responses

Posted by: Hmanjarawala on: 8/8/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi bahnu,
Here is a store procedure.

CREATE OR REPLACE PROCEDURE SP_DELETE_DEPT
AS
@p_dept_id int
BEGIN
IF NOT EXISTS(SELECT 'Y' FROM employee WHERE deptid=@p_dept_id) THEN
BEGIN
DELETE FROM department WHERE deptid=@p_dept_id
END
ELSE
BEGIN
raiserror('This department contains employees, so it can\'t be delete',16,1)
END
END

Hope this will helps you.

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhanubysani on: 8/8/2011 [Member] Starter | Points: 25

Up
0
Down
hi
iam returning int value how can return string value when iam using update,delete statements..i wnat to dispaly the errors raised by sql...

hi iam used count instaed of if not exists is there any difference in both

i wrote like this

select @c=COUNT(deptid) from Employee where DeptId=@DepartmentID
if(@c=0)
begin
update Department set IsActive=0,Ipaddress=@IpAddress where Deptid=@DepartmentID
end

Regards,
Bhanu Prakash Bysani

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 8/8/2011 [Member] Bronze | Points: 25

Up
0
Down
Hi,
1st of all procedure doesn't return any thing.
so if you want to give some string message as output then use out parameter.

n ya watever u've written for update is fine, you can use both Count or If not exists.
no major difference is there.

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/8/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Normally a Stored procedure can return a ONLY NUMERIC DATA (Using RETURN)

Create Proc Proc1

As
Begin
Return (100)
End
Go
Declare @RetValue Int

exec @RetValue = Proc1
select @RetValue [RetValue]
If you try to return any string using (RETURN) then , you will get the following err..
"Conversion failed when converting the varchar value '...' data type int."

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amitverma80 on: 8/9/2011 [Member] Starter | Points: 25

Up
0
Down
DELETE FROM DEPT WHERE DEPTID NOT IN (SELECT DISTINCT DEPTID FROM EMPLYEE)

Bhanubysani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response