SP to return last ID and insert into a column using a trigger I have written an SP to do the below:

i have a table called T1:
AS below:

CREATE TABLE [dbo].[T1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [QNo] [nvarchar](50) NULL DEFAULT ('0'),
PRIMARY KEY CLUSTERED 


T1 stores a number value in the Qno Column. AS so:
ID: 1            Qno: 0

When i insert into table QUDF, the last number (in this case currently is '0'), needs to be inserted into the QUDF column in the QUDF table. (it needs to fetch last number from T1 when inserting in QUDF)

columns in QUDF are:
Client_id, Contact_number, Code_ID, Type_id, QUDF

Once inserted, the Qno column in T1 needs to update to 1 (increment by 1 each time) 

I have been on the forums but still nothing has resolved this. Please take note, only 1 row is being inserted at a time through the GUI

I have then created a trigger on the Opportunities table as this all needs to happen when an Opportunity is created by the user via the GUI.

SP:

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id varchar (24), @Opp_Number int  
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @Qno varchar (50)
    Declare @NewQno varchar (50)

    SET @Qno= '0';

SELECT @NewQno =@Qno + 1 FROM T1 where ID = 1; 

Insert into O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_Id,@Opp_Number,1,0,@Qno)


Update T1 set Qno = @NewQno where ID = 1


END

GO


Trigger:
Trigger must only fire when Opp_type = 0

create TRIGGER [dbo].[Trg] ON  [dbo].[AMGR_Opportunity_Tbl] AFTER INSERT
AS 
BEGIN

Declare @Opp_type varchar(24)
Declare @Opp_id int
Declare @Opp_number int

Select @Opp_type = Opp_Type from inserted
select @Opp_id = Opp_id from inserted
Select @Opp_number = Opp_Number from inserted

If @Opp_type = '0'

 BEGIN

Execute dbo.SP1_Test @Opp_id,@Opp_number

END


If someone can assist me in getting my scenario right - i am very new at this logic. I would greatly appreciate any suggestions.

SOLUTION to my issue was to simply add the following:

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;


To the code example below:

BEGIN
Declare @Opp_id varchar (24)
Declare @OppNo int
Declare @Opp_type int
DECLARE @NewQno VARCHAR(750)
declare @Qno varchar (50)

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

If @Opp_type = '0'

 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    values(@Opp_id,@OppNo,1,0,@NewQno)

UPDATE T1 SET Qno = @NewQno WHERE ID = 1

    End
Based on the limited information in your question, I made a few changes to your stored procedure.  If I've misunderstood something, let me know.  This code was not fully tested on my side.

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id VARCHAR(24)
    ,@Opp_Number INT
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @NewQno VARCHAR(50)

    --The Qno column will contain the current value, whatever that may be
    INSERT INTO O_QUDF (Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    SELECT @Opp_Id,@Opp_Number,1,0,Qno
    FROM T1 WHERE ID = 1;

    --While you can probably get away with doing simple arithmetic on NVARCHAR columns, 
    --I think it's better to implicitly convert it to a numeric data type to do the arithmetic.  
    --I chose Integer.  
    --Then, you need to convert it back to nvarchar for the upcoming UPDATE
    SELECT @NewQno = Convert(NVARCHAR(50), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    UPDATE T1 SET Qno = @NewQno WHERE ID = 1
END
GO