Stored Procedure bproblem

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

0	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

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

0	DELETE FROM DEPT WHERE DEPTID NOT IN (SELECT DISTINCT DEPTID FROM EMPLYEE) Bhanubysani, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts