Declare Table - 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 2005 Forums

Transact-SQL (2005)

Declare Table

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-29 : 12:40:22

When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help.

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id]

from property_char

Go

SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM

property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id

WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 12:47:03

quote:
Originally posted by nguyenl

When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help.

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id]

from property_char

Go

SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM

property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id

WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')

You have tried to use @pc2 as an alias. Remove it and give some other name as alias. Or were you trying to join onto this table variable? dont using @ symbol for aliases

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-29 : 13:04:47

Hi,

Thanks for the reply. In the begiining I declared a table @pc2 because if I don't declare it then it won't work in a SSIS package.

I am trying to do a join to thi variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 13:09:09

quote:
Originally posted by nguyenl

Hi,

Thanks for the reply. In the begiining I declared a table @pc2 because if I don't declare it then it won't work in a SSIS package.

I am trying to do a join to thi variable.

then wats property_char?
why have you used
INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id

rather than something like

INNER JOIN @pc2 tmp ON property.id = tmp.property_id

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-29 : 13:26:46

Tried using:

SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM
property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
@pc2 pc2 ON property.id = pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id

But still getting: Must declare the table variable "@pc2".

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-05-29 : 13:45:02

I'm tryng to make it a little simpler. I ran the simple "Select Statement" after my "Declaration" and I get the same error.

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id]

from property_char

go

select *

from @pc2 pc2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 13:47:42

quote:
Originally posted by nguyenl

I'm tryng to make it a little simpler. I ran the simple "Select Statement" after my "Declaration" and I get the same error.

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id]

from property_char

go

select *

from @pc2 pc2

Nope you need to run the entire batch each time. @pc2 is just a table variable. It will be destroyed after you run the batch so that next time when you run select alone, it cant find it. always need to run declare along with batch.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 13:48:35

When you run your SELECT * query, the @pc2 no longer exists due to your GO. GO is used to indicate the end of a batch, so you lose all of the variables and local temp tables. 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-29 : 14:06:36

Yes removing the "GO" works with that script. However, going back to orginal script, when I remove the "Go" I get: Msg 137, Level 15, State 2, Line 35 Must declare the scalar variable "@pc2".

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[value],
[prop_segment_id]

from property_char

SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM

property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
@pc2 pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id

WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
pc2.prop_char_typ_code = 'USECD' AND
(pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
pc2.tax_year = '2008' AND
pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 14:10:58

You can't use @pc2.property_id. You must use the alias instead: pc2.property_id.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 14:15:05

That was the same thing i told him in post 05/29/2008 : 12:47:03

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 14:15:43

Yes I see that, so I'm not sure why he wants to try doing it again.

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-29 : 14:23:05

Thankyou so much for all of your help. It is working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 14:34:08

quote:
Originally posted by nguyenl

Thankyou so much for all of your help. It is working.

Glad that you sorted it out in the end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources