Variable Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Variable Tables

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 16:10:27

Hi,

I am triying to select data from the Variable table "@state" I created but it gives me an error saying I need to "Declare the Scalar Variable".

Declare @state TABLE(
[id] [int] IDENTITY(1,1) NOT NULL,
[code_category_id] [int] NOT NULL,
[code_table_cd] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[code_description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[chg_allowed_ind] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[print_order] [decimal](10, 2) NULL,
[mod_date] [datetime] NULL,
[state] [varchar](2))

INSERT INTO @state
([code_category_id]
,[code_table_cd]
,[code_description]
,[chg_allowed_ind]
,[print_order]
,[mod_date]
,[state])
SELECT
[code_category_id]
,[code_table_cd]
,[code_description]
,[chg_allowed_ind]
,[print_order]
,[mod_date]
,[state]

FROM code_table

go

SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
@state.code_table_cd,
address.zip_postal_code,
--@country.code_description,
address.line_care_of
--@prop_role.code_table_cd

FROM property,
address,
ppi,
@state
--@country,
--@prop_role

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND @state.id = address.province_state_cd
--AND @country.id = address.country_cd
--AND @prop_role.id = ppi.prop_role_cd
--AND @prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

Please help.

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 16:19:18

Alias @state, then use the alias instead of @state.columnName.

Like this:

SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
s.code_table_cd,
address.zip_postal_code,
--@country.code_description,
address.line_care_of
--@prop_role.code_table_cd

FROM property,
address,
ppi,
@state s
--@country,
--@prop_role

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND s.id = address.province_state_cd
--AND @country.id = address.country_cd
--AND @prop_role.id = ppi.prop_role_cd
--AND @prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 16:29:33

Thanks for the Reply. I still got the error after I tried your suggestion. The error is:

(1082 row(s) affected)
Msg 1087, Level 15, State 2, Line 21
Must declare the table variable "@state".

Declare @state TABLE(
[id] [int] IDENTITY(1,1) NOT NULL,
[code_category_id] [int] NOT NULL,
[code_table_cd] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[code_description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[chg_allowed_ind] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[print_order] [decimal](10, 2) NULL,
[mod_date] [datetime] NULL,
[state] [varchar](2))

INSERT INTO @state
([code_category_id]
,[code_table_cd]
,[code_description]
,[chg_allowed_ind]
,[print_order]
,[mod_date]
,[state])
SELECT
[code_category_id]
,[code_table_cd]
,[code_description]
,[chg_allowed_ind]
,[print_order]
,[mod_date]
,[state]

FROM code_table

go

SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
s.code_table_cd,
address.zip_postal_code,
--@country.code_description,
address.line_care_of
--@prop_role.code_table_cd

FROM property,
address,
ppi,
@state s
--@country,
--@prop_role

WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND s.id = address.province_state_cd
--AND @country.id = address.country_cd
--AND @prop_role.id = ppi.prop_role_cd
--AND @prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-07 : 16:35:21

That's because you've got a GO command in there. The table variable no longer exists after the GO as you are now in another batch. Remove the GO.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-07 : 16:37:00

Thanks it worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources