bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
HenriYou have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
--
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
--
http://www.aspfaq.com/
(Reverse address to reply.)
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a écrit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
Henri
You have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
http://www.aspfaq.com/
(Reverse address to reply.)

> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a crit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>