CODEWITHSUNDEEP
sql-server-2008procedure
Nitesh Kataria
Nitesh Kataria

Reputation: 341

How to store the result of join query in single variable in stored procedure

CREATE PROCEDURE Test12 
     @varwi_name nvarchar(50)
AS
BEGIN 
    DECLARE @newbalance nvarchar(400)

    BEGIN
        SELECT 
            @newbalance = (SELECT 
                               (CAST(a.currentBal as INT) * CAST( b.monthlyInterestRate AS INT)) / 100  
                           FROM 
                               USR_0_CLOS_TransDet_TXN a, USR_0_CLOS_LoanReg_TXN b ON a.wi_name = b.wi_name)
       WHERE 
           wi_name = @varwi_name 
END
GO

I am getting ERROR

Incorrect syntax near the keyword 'ON'. Severity 15 Test12

Upvotes: 0

Views: 1094

Answers (1)

gbn
gbn

Reputation: 432541

  • You need the JOIN keyword for an ON clause
  • Wrong placement of ( and )
  • An extra BEGIN

As it should look:

CREATE PROCEDURE Test12
    @varwi_name nvarchar(50)
AS
BEGIN 

    DECLARE @newbalance nvarchar(400);

    SELECT
        @newbalance = (
                       SELECT
                        (CAST(a.currentBal AS int) * CAST(b.monthlyInterestRate AS int)) / 100
                       FROM
                        USR_0_CLOS_TransDet_TXN a
                        JOIN USR_0_CLOS_LoanReg_TXN b ON a.wi_name = b.wi_name
                       WHERE
                        wi_name = @varwi_name
                      );

END;
GO

Upvotes: 1

Related Questions