Multiple Operation in Single SP SQLSEVER

Posted by Self-innovator under Sql Server on 1/20/2012 | Points: 10 | Views : 1081 | Status : [Member] | Replies : 3
Hi ,
How to Perform multiple operations like Ins,Delte,Update using Single Stored Procedure.

Join Hands Change lives
Thanks & Regards
Straight Edge Society



Responses

Posted by: Prabhakar on: 1/20/2012 [Member] [MVP] Starter | Points: 25

Up
0
Down
hi Self-innovator ,,

if u want insert,update,delete, in one single store-procedure . use if condition with style .. and write ur queries in style

if @style='1'

begin
insert...
end


if @style='2'
begin
Update...
end


Best Regard's
Prabhakar

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-innovator on: 1/20/2012 [Member] Bronze | Points: 25

Up
0
Down
Pls go through this if i insert It asks for Id which is Auto Increment but i've declared the Id in My sp which is needed while Updating record
create proc [dbo].[WH_DunnageMstInsertCust]

@ID int,
@Action varchar(25),
@CustomerName varchar(50),
@DunnageType varchar(10),
@Status VARCHAR(10)
as
if @Action='Ins'
begin
Insert into WH_DunnageMst values(@CustomerName,@DunnageType,@Status)
end
else if @Action='Update'
begin
Update WH_DunnagMst set CustomerName=@CustomerName,DunnageType=@DunnageType,Status=@Status where ID=@ID
end
else if @Action='Delete'
begin
Delete from WH_DunnageMst where Id=@ID
end
else if @Action='Search'
begin
select * from WH_DunnageMst where CustomerName=@CustomerName
end
else if @Action='@BindGrid'
begin
select * from WH_DunnageMst
end

My ClassFile

Public Shared Function InsertDunnage(ByVal CustomerName As String, ByVal DunnageType As String, ByVal Status As String) As DataSet
Return DataLayer.ExecuteDataset("WH_DunnageMstInsertCust", New SqlParameter("@Action", "Ins"), New SqlParameter("@CustomerName", CustomerName), New SqlParameter("@DunnageType", DunnageType), New SqlParameter("@Status", Status))
End Function
Public Shared Function UpdateDunnage(ByVal CustomerName As String, ByVal DunnageType As String, ByVal Status As String, ByVal ID As Integer) As DataSet
Return DataLayer.ExecuteDataset("WH_DunnageMstInsertCust", New SqlParameter("@Action", "Update"), New SqlParameter("@CustomerName", CustomerName), New SqlParameter("@DunnageType", DunnageType), New SqlParameter("@Status", Status), New SqlParameter("@ID", ID))
End Function
Public Shared Function DeleteDunnage(ByVal ID As Integer) As DataSet
Return DataLayer.ExecuteDataset("WH_DunnageMstInsert", New SqlParameter("@Action", "Delete"), New SqlParameter("@ID", ID))
End Function


Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 1/23/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

If you using SQL Server 2008 onwards then, you can use MERGE Statement

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Self-innovator, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response