Create a variable type TABLE

I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TESTE]

ON[dbo].[TABTESTE]

FOR INSERT

AS

DECLARE

@.YearTablenvarchar(4),

@.IdClientINT,

@.MyTableTABLE

(

IdClientINT,

SituNVARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED

SET @.MyTable = 'TABLE' & @.YearTable

UPDATE@.MyTable

SET

Situ= 'X'

WHEREIdClient = @.IdClient

END

GO

Erros:

Msg 156, Level 15, State 1, Procedure TESTE, Line 9

Incorrect syntax near the keyword 'TABLE'.

Msg 137, Level 15, State 1, Procedure TESTE, Line 17

Must declare the scalar variable "@.MyTable".

Msg 1087, Level 15, State 2, Procedure TESTE, Line 18

Must declare the table variable "@.MyTable".

I don't have much experience working with triggers but here's a shot...

-Try changing the @.MyTable to varchar(100)

-Declare another variable @.sql varchar(500)

set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient

exec (@.sql)

You may need to play with the number of single quotes around X to get the string to build correctly.

|||

can you explain what are you trying to do with this statement.

SET @.MyTable = 'TABLE' & @.YearTable

Are you trying to concatenate the string?

|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.
I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.

Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?

|||

hi MauricioBogo,

declare @.MyTable Table

(

IdClient int,

Suit nvarchar(50)

)

after you declare @.MyTable as a "Table"

in this Transaction, the @.MyTable is a "local database object" Already.

not a variable value.

this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "

and update @.MyTable .

you can try this, as below:

declare @.sSQL varchar(Max)

declare @.Mytable varchar(255)

select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted

set @.MyTable = 'TABLE' + @.Yeartable

set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)

--or--

or add any script you need.

try it.

hoping this can help.

Best Regrads,

Hunt.

|||

Yes, I'm trying to concatenate the string, but I already changed to + . Right ?

Thanks

|||

Hunt

Ok it works, thanks

Mauricio

|||

Table variables need to be declare alone:

DECLARE

@.YearTable nvarchar(4),

@.IdClient INT

DECLARE

@.MyTable TABLE

(

IdClient INT,

Situ NVARCHAR(50)

)

sql