CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user4912134
user4912134

Reputation: 1043

Stored Procedure for insert/update and select results in error multi-part identifier could not be found

I am trying to create the StoredProcedure which Insert/Updates the table also select the record that was inserted or updated

ALTER PROCEDURE [dbo].[usp_InserUpadte] 
@account_TT AS account_TT READONLY

 AS
 BEGIN
 SET NOCOUNT ON;

 BEGIN TRANSACTION;

 MERGE dbo.[clarity_Account] prj
 USING @account_TT tt
 ON prj.AccountID = tt.AccountID
 WHEN MATCHED THEN UPDATE SET prj.CounterSeq = prj.CounterSeq+1
 WHEN NOT MATCHED THEN INSERT (AccountID,CounterSeq)
 VALUES (tt.AccountID, 1);

 select * from dbo.[clarity_Account] where AccountID = tt.AccountID;

 COMMIT TRANSACTION;

 END;

Here account_TT is the Table Type with AccountID nvarchar(50). Here I get error in the 

  select * from dbo.[clarity_Account] where AccountID = tt.AccountID;

Msg 4104, Level 16, State 1, Procedure usp_InserUpadte, Line 28 The multi-part identifier "tt.AccountID" could not be bound.

Upvotes: 0

Views: 66

Answers (1)

Siyual
Siyual

Reputation: 16917

You're trying to use the alias of the table used in the MERGE statement in your second query, where it isn't known. You just need to do a JOIN to your table variable:

 Select  CA.*
 From    dbo.[clarity_Account]  CA
 Join    @account_TT            TT  On  CA.AccountID = TT.AccountID;

Upvotes: 3

Related Questions