Must declare the table variable

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

General SQL Server Forums

New to SQL Server Programming

Must declare the table variable

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-09 : 10:14:54

anyone who whats wrong with is sql getting this error
Msg 1087, Level 15, State 2, Line 12
Must declare the table variable "@IRBT2".

DECLARe @IRBT2 int



SELECT
	ACRT.*, 
	x.MIDIRB
FROM ACRT
INNER JOIN IRBT 
	ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

	SELECT MIDIRB = MIN(@IRBT2.IRBRATING) 
	FROM @IRBT
	WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-09 : 10:20:13

I think you have to declare @TRDT2 as follows

DECLARE @@TRDT2 TABLE(IRBRATING int)

And also that red marked part is typo......

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x

--
Chandu

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-09 : 10:24:01

hi
thanks for replying
still getting error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-09 : 10:26:38

show us the FULL query that you just tried and the error messages

KH
[spoiler]Time is always against us[/spoiler]

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-09 : 10:29:28

DECLARE @IRBT2 TABLE(IRBT int)

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x

error message

Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@IRBT2".

tried it the same was as code from bandi same error message

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 23:20:16

quote:
Originally posted by rjhe22

DECLARE @IRBT2 TABLE(IRBT int)

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(@IRBT2.IRBRATING)
FROM @IRBT2
WHERE @IRBT2.IRBRATING > IRBT.IRBRATING
) x

error message

Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@IRBT2".

tried it the same was as code from bandi same error message

this code doesnt make any sense to me

can i ask what you're trying to achieve?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-10 : 00:24:52

DECLARE @IRBT2 TABLE(IRBT int)

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 01:57:16

quote:
Originally posted by bandi

DECLARE @IRBT2 TABLE(IRBT int)

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x

--
Chandu

Even then this code makes no sense as table is empty

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-10 : 02:07:44

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

DECLARE @IRBT2 TABLE(IRBT int)

SELECT
ACRT.*,
x.MIDIRB
FROM ACRT
INNER JOIN IRBT
ON IRBT.IRBRATING = ACRT.MIDIRB
CROSS APPLY (

SELECT MIDIRB = MIN(T2.IRBT)
FROM @IRBT2 T2
WHERE T2.IRBT > IRBT.IRBRATING
) x

--
Chandu

Even then this code makes no sense as table is empty

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi vishak,

I provided solution for that error message only...

--
Chandu

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-10 : 05:12:53

o right thought i was on the right line but ovhbiously not.

so what im trying to do is
if in the acrt table NEGWATCH =1 then for MIDIRB in the acrt table i want to add the next value from irbt
so
say negwatch =1 and midird before this update is 16 then when update is done and from the irtb table or table 1 the midirb = 20 as the next number in the irbt table after 16 is 20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-10 : 23:49:05

sorry can you illustrate this with some sample data? i'm not quite getting this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-11 : 05:10:52

ya no problem iv actually changed they way im going to do it so will put up sample data and explain how im doing it now

table 1


id  mo  fi  sp value y/n
1   a    a   a       y
2   b    b   b       n
3   c    c    c      y
4    d   d   d       n

table 2

mo fi  sp  value
a   a   a   1
b   b   b   2
c   c   c  3
d   d   d 4

so what i want to do is update the value in table1 with the value in table 2
but if any of the data in table 1 is y then i want it to update table 1 with the next value in table 2

so my table 1 after the update should look like this


id  mo  fi  sp value y/n
1   a    a   a    2   y
2   b    b   b   2    n
3   c    c    c    4  y
4    d   d   d    4   n

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-11 : 07:24:24

DECLARE @table1 TABLE (id int, mo varchar(10), fi varchar(10), sp varchar(10), value int, [y/n] char(1))
INSERT INTO @table1
SELECT 1, 'a', 'a', 'a', null, 'y' UNION ALL
SELECT 2, 'b', 'b', 'b', null ,'n' UNION ALL
SELECT 3, 'c', 'c', 'c', null, 'y' UNION ALL
SELECT 4, 'd', 'd', 'd', null ,'n'

DECLARE @table2 TABLE (mo varchar(10), fi varchar(10), sp varchar(10), value int)
INSERT INTO @table2
SELECT 'a', 'a', 'a', 1 UNION ALL
SELECT 'b', 'b', 'b', 2 UNION ALL
SELECT 'c', 'c', 'c', 3 UNION ALL
SELECT 'd', 'd', 'd', 4

;with cte
as
(select *, ROW_NUMBER() over(order by mo) AS rn FROM @table2),
cte1
as (SELECT mo,value, rn-1 as nextValue FROM cte)

update t1 SET t1.value = case when [y/n] = 'y' then c2.value else c1.value end
FROM @table1 t1
JOIN cte c1 ON t1.mo = c1.mo
join cte1 c2 on (c1.rn = c2.nextValue OR c1.rn = (SELECT MAX(rn) from cte))

SELECT * FROM @table1

--
Chandu

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2012-10-11 : 07:28:28

thanks for reply
just out of interest is there any other way of doing it.
like just a simple enough update without variable tables etc

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-11 : 07:52:17

quote:
Originally posted by rjhe22

thanks for reply
just out of interest is there any other way of doing it.
like just a simple enough update without variable tables etc

LAG() and LEAD() functions are available in SQL Server2012 to perform this kind of operations, but not in MSSQL 2008

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-11 : 15:24:21

if you dont want to use CTE you can use a logic like below


UPDATE t1
SET t1.value = CASE WHEN [y/n] = 'y' THEN NextVal ELSE t2.value END
FROM table1 t1
INNER JOIN table2 t2
ON t2.mo  = t1.mo  
and t2.fi  = t1. fi 
and t2.sp = t1.sp
OUTER APPLY (SELECT MIN(value) AS NextVal
      FROM table2
      WHERE value> t2.value
      )t3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources