error Must declare the scalar variable

Question

Post reply

error Must declare the scalar variable

andy.aloys

SSC Veteran

Points: 233
More actions
August 5, 2012 at 10:42 pm

#279233

ALTER PROCEDURE [dbo].[sp_createdate2]
@sdate AS date ,
@edate AS date,
@userid AS INT
AS
BEGIN
DECLARE @FirstTable TABLE (id INT IDENTITY NOT NULL PRIMARY KEY,userid INT,tgl DATE,masuk VARCHAR(5),pulang VARCHAR(5),shift varchar(10),status varchar(20))
INSERT INTO @FirstTable
SELECT @userid,tdate,'','','','' from temp_date where tdate >= @sdate and tdate <= @edate
UPDATE @FirstTable
SET masuk = b.datang
from
(select userid,convert(date,CHECKTIME,103) tgl,convert(VARCHAR(5),min(CHECKTIME),14) datang,convert(VARCHAR(5),MAX(CHECKTIME),14) selesai from CHECKINOUT
WHERE convert(date,CHECKTIME,103) >= @sdate and convert(date,CHECKTIME,103) <= @edate
group by userid,convert(date,CHECKTIME,103)) as b
where @FirstTable.userid = b.userid
select * from @FirstTable
END
gives the following error
Must declare the scalar variable "@FirstTable".
can any one help me on this?
tks&Regrads

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mahmood.BilalB Ten Centuries Points: 1303 More actions · Answer 1

Hi,

I could not understand the update query within that stored procedure.

UPDATE @FirstTable

SET masuk = b.datang

from

(select userid,

convert(date,CHECKTIME,103) tgl,

convert(VARCHAR(5),min(CHECKTIME),14) datang,

convert(VARCHAR(5),MAX(CHECKTIME),14) selesai

from CHECKINOUT

WHERE convert(date,CHECKTIME,103) >= @sdate and convert(date,CHECKTIME,103) <= @edate

group by userid,convert(date,CHECKTIME,103)) as b

That line is actually miss placed and cause the error.

--where @FirstTable.userid = b.userid

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

Try this:

UPDATE a

SET masuk = b.datang

from @FirstTable a

inner join

(select userid,convert(date,CHECKTIME,103) tgl,convert(VARCHAR(5),min(CHECKTIME),14) datang,convert(VARCHAR(5),MAX(CHECKTIME),14) selesai from CHECKINOUT

WHERE convert(date,CHECKTIME,103) >= @sdate and convert(date,CHECKTIME,103) <= @edate

group by userid,convert(date,CHECKTIME,103)) as b

on a.userid = b.userid

andy.aloys SSC Veteran Points: 233 More actions · Answer 3

Suresh B. (8/6/2012)
Try this:
UPDATE a
SET masuk = b.datang
from @FirstTable a
inner join
(select userid,convert(date,CHECKTIME,103) tgl,convert(VARCHAR(5),min(CHECKTIME),14) datang,convert(VARCHAR(5),MAX(CHECKTIME),14) selesai from CHECKINOUT
WHERE convert(date,CHECKTIME,103) >= @sdate and convert(date,CHECKTIME,103) <= @edate
group by userid,convert(date,CHECKTIME,103)) as b
on a.userid = b.userid

Thank You for help, Problem Solve..

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Welcome. Happy to know that your problem is solved. Thanks for updating.