Loop through sql server table using values derived from temp table

Question

I need to use values derived from a temporary table and use it as where criteria. Please see my code:

declare @laneNum int
declare @startDate date = '2019-02-07'
declare @class int = 1
declare @id int 

if OBJECT_ID('tempdb..#tempLaneNumber') IS NOT NULL
drop table [#tempLaneNumber]

create table #tempLaneNumber
(
    LANE_NUMBER INT NULL
)


INSERT INTO #tempLaneNumber (LANE_NUMBER)
SELECT DISTINCT EXIT_LANE
FROM [dbo].[TOLL] 
ORDER BY EXIT_LANE ASC

select * from #tempLaneNumber

set @laneNum = (select * from #tempLaneNumber)

begin
    select COUNT(*)
    from [dbo].[TOLL]
    where convert(date, TRXN_DTIME) = @startDate and EXIT_LANE = @laneNum
end

If I run up to select * from #tempLaneNumber I get this result:

But if I use this values as a criteria on a where statement, on the begin statement, I don't get my expected result.

A variable in SQL Server can only contain a single value so set @laneNum = (select * from #tempLaneNumber) will contain one of the values in your temp table. Maybe you want where EXIT_LANE in (select LANE_NUMBER from #tempLaneNumber) — Dale K
– Dale K, Commented Feb 18, 2019 at 10:11

PSK · Accepted Answer · 2019-02-18 10:17:52Z

1

You need to change your query like following.

select l.LANE_NUMBER, COUNT(*)
    from [dbo].[TOLL] t
    inner join  #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
    where convert(date, TRXN_DTIME) = @startDate 
    GROUP BY l.LANE_NUMBER

There are multiple issues in your query, i have commented those and modified where ever required.

--declare @laneNum int [NOT REQUIRED]
declare @startDate date = '2019-02-07'
declare @class int = 1
declare @id int 

if OBJECT_ID('tempdb..#tempLaneNumber') IS NOT NULL
drop table [#tempLaneNumber]

create table #tempLaneNumber
(
    LANE_NUMBER INT NULL
)


INSERT INTO #tempLaneNumber (LANE_NUMBER)
SELECT DISTINCT EXIT_LANE
FROM [dbo].[TOLL] 
--ORDER BY EXIT_LANE ASC [NOT REQUIRED FOR INSERT]

select * from #tempLaneNumber

--set @laneNum = (select * from #tempLaneNumber) [YOU CAN'T ASSING A TABLE OUTPUT TO A INT VARIABLE]

--begin [NOT REQUIRED]
/*
    select COUNT(*)
    from [dbo].[TOLL]
    where convert(date, TRXN_DTIME) = @startDate and EXIT_LANE = @laneNum
*/
--CHANGED
select l.LANE_NUMBER, COUNT(*)
    from [dbo].[TOLL] t
    inner join  #tempLaneNumber l on t.EXIT_LANE = l.LANE_NUMBER
    where convert(date, TRXN_DTIME) = @startDate 
    GROUP BY l.LANE_NUMBER
--end [NOT REQUIRED]

edited Feb 18, 2019 at 10:17

answered Feb 18, 2019 at 10:12

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ibanez1408 Over a year ago

Sir I need to have a count for each LANE_NUMBER. Your suggestion seems to add everything.

Ibanez1408 Over a year ago

Sir can you answer another one for me? stackoverflow.com/questions/54745094/…

Collectives™ on Stack Overflow

Loop through sql server table using values derived from temp table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related