hi everyone
i have 3 stored procedures one is the main sp i'm using the method output inserted.id in the insertion Stored Procedure
like this :
ALTER PROCEDURE [dbo].[sp_INSERT_REG_DETAILS]
@SER_NO NVARCHAR (50),
@REG_DATE DATE,
@NAME Varchar(50),
@RPF NVARCHAR (50),
@ADDRESS VARCHAR(250),
@COLG VARCHAR(50),
@USR INT
AS
Set NoCount On
Begin
Insert into REG_DETAILS
(
Ser_no,Reg_date,Name,Prf,Address,Golg,User
)
output inserted.ID
Values
(
@SER_NO,@REG_DATE,@NAME,@RPF,@ADDRESS,@COLG,@USR
)
End
Second stored procedure:
ALTER PROCEDURE [dbo].[sp_INSERT_TYPE]
@ID int
@TYPE varchar (50)
AS
BEGIN
SET NOCOUNT ON;
Insert into [dbo].[TYPE]
(ID,TYPE)values(@ID,@TYPE)
END
Third stored procedure :
ALTER PROCEDURE [dbo].[sp_Insert_ST]
@ID int
@ST varchar (50)= NULL
AS
BEGIN
SET NOCOUNT ON;
Insert into tblST
(ID,stu)values(@ID,@STU)
END
when I put it in my update stored procedure it will give syntax error where to put it in the update stored procedure ? after the values ? before the last statment GO or what should i do ?
Loading
Kiresh GangariyaPosted Jun 3, 2013, 8:17 AM
@ID int
@ST varchar (50)= NULL
AS
BEGIN
SET NOCOUNT ON;
IF( SELECT ID FROM tblST WHER ID = @ID )
BEGIN
UPDATE tblST SET stu=@stu
ELSE
Insert into tblST
(ID,stu)values(@ID,@STU)
END
END
ToBePosted Jun 3, 2013, 8:22 AM
ToBePosted Jun 3, 2013, 8:08 AM
Jignesh TrivediPosted Jun 3, 2013, 4:40 AM
hi,
i think here variable assignment is missing.
try...
declare @ID table (ID int)
Insert into REG_DETAILS ( Ser_no,Reg_date,Name,Prf,Address,Golg,User)
output inserted.ID INTO @ID
Values (@SER_NO,@REG_DATE,@NAME,@RPF,@ADDRESS,@COLG,@USR)
I guess Id is field is Identity.
so you can retrieve this Id by using @@identity or @@scope_identity
Insert into REG_DETAILS ( Ser_no,Reg_date,Name,Prf,Address,Golg,User)
Values (@SER_NO,@REG_DATE,@NAME,@RPF,@ADDRESS,@COLG,@USR)
Declare @myId int = @@identity
Declare @myId1 int = @@scope_identity
hope this will help you.