CODEWITHSUNDEEP
.netsql-server-2008-r2
user1110790
user1110790

Reputation: 787

Passing table variable to a stored procedure and getting Invalid object name '@TableVar'

I am rewriting a stored procedure using a table variable . Basically I have a list which I am passing to a DataTable and then passing the datatable as table variable to the stored procedure. The stored procedure compiles correctly , but when I execute it in SSMS or in .Net am getting Invalid object name '@TableVar'.I am new to this, so I appreciate your help.

 Alter PROCEDURE [dbo].[proc]
    (
        @TableVar [dbo].[TableVar] READONLY
    )
    AS
BEGIN
Set nocount on
Declare @MYID INT
Declare @SOMEDate DateTime
Declare @NEWDate Date


select @MYID = some_id,
@SOMEDate = convert(date, db_some_date) from tbl_some
where someval = (select tb.val1 from [@TableVar] as tb);


Merge OneTabl as target
  using (Select x.val1, x.val2, x.val3,x.val4,
                        x.val5,x.val6, x.val7 from [@TableVar] as x)
  as Source

  on val_id = @MYID
  when matched and  @MYID !=NULL then

 update set MDN= Source.[MDN],
                  val1= Source.[val1],
                  val2=Source.[val2],
                  val3=Source.[val3],
                  val4=Source.[val4],
                  val5=Source.[val5],
                  val6=Source.[val6]

  when not matched and @MYID !=NULL then  
   insert  
   values (Source.[val1],
           Source.[val2],
           Source.[val3],
               Source.[val4],
               Source.[val5],
               Source.[val6],
               Source.[val7],
               [@MYID]
      )      ;
end

Upvotes: 0

Views: 950

Answers (1)

user1110790
user1110790

Reputation: 787

I removed the [] brackets from tablevar ref as suggested and it works thank you. Thanks @Habo

Upvotes: 1

Related Questions