Reading Rows into Variable in SQL Server

Question

I have the following T-SQL in MSSQL,

Declare @S table(StartAc bigint)
Declare @E table(EndAc bigint)
Declare @B table(BNum varchar(20))

Select StartAc=[Start] from [dbo].[CFC] 
Select EndAc=[EndCode] from [dbo].[CFC]
Select ENum=[ENum] from [dbo].[CFC]

;with CFile as (
    Select StartAc as AcNum 
    from @S 
    union all 
    Select AcNum+1 
    from CFile 
    where AcNum+1 <=EndAc
)

Insert into dbo.list 
Select ENum,* 
from CFile Option(Maxrecursion 32676)

Actually I have a table with EmployeeID,StartingNumber and EndingNumber (lets say Ranges),I need to generate all Numbers between those Starting and EndingNumbers for all Employees.I created using Single Employee but not working for all Employees.

No, you don't need a loop, but you might need a table variable instead of a scalar variable. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 26, 2015 at 13:26

Giorgos Betsos · Accepted Answer · 2015-03-26 13:26:17Z

2

You can't use a scalar variable to store multiple values. You have to use a table variable instead:

DECLARE @data TABLE(EmpID bigint)

INSERT INTO @data(EmpID)
SELECT [EID] FROM [dbo].[EmplyeeFC]

answered Mar 26, 2015 at 13:26

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Comments

Dan Field · Accepted Answer · 2015-03-26 14:29:57Z

0

Your actual desire is much different than your iginal question.

What you need to do is this:

select 'asdf' as empid, 1 as StartAc, 100 as EndAc into #emptbl;

insert #emptbl Values ('blah', 203, 400)

;with nums(Number, EmpID, StartAc, EndAc) as
(SELECT
        StartAc as Number,
        empid,
        StartAc,
        EndAc
    FROM #emptbl
    UNION ALL
    SELECT Number + 1, empid, StartAc, EndAc
    FROM Nums 
    WHERE Number < EndAc
)
SELECT EmpID, Number from Nums ORDER BY EmpID Option(Maxrecursion 32676);

drop table #emptbl

(To original question)

If you know the data set will be small and only need to be held in memory for a little bit, won't require any additional indexes/column modifications, use a table variable.

DECLARE @data TABLE(EmpID bigint)

INSERT INTO @data(EmpID)
SELECT [EID] FROM [dbo].[EmplyeeFC]

Otherwise, use a temp table.

SELECT [eid] INTO #data FROM [dbo].[EmplyeeFC]
ALTER #data add AnotherCol int null;
ALTER #data ADD CONSTRAINT ...
...  other code ...
DROP TABLE #data

It would be possible to use a cursor to loop through your data, but this is defeating the purpose of using SQL in the first place - set based operations (using tables) will be far more performant and maintainable.

edited Mar 26, 2015 at 14:29

answered Mar 26, 2015 at 13:31

Dan Field

21.8k6 gold badges60 silver badges74 bronze badges

3 Comments

user2548018 Over a year ago

Declare @S table(StartAc bigint) Declare @E table(EndAc bigint) Declare @B table(BNum varchar(20)) Select StartAc=[Start] from [dbo].[CFC] Select EndAc=[EndCode] from [dbo].[CFC] Select ENum=[ENum] from [dbo].[CFC] ; with CFile as (Select StartAc as AcNum from @S union all Select AcNum+1 from CFile where AcNum+1 <=EndAc) Insert into dbo.list Select ENum,* from CFile Option(Maxrecursion 32676)

Dan Field Over a year ago

It's really ahrd to read your code like that.. Try editing your question with the actual code and expected result.

user2548018 Over a year ago

Edited main Question,please check

Eric Siodmak · Accepted Answer · 2015-03-26 13:36:38Z

0

You can use curor to loop for all row in query

DECLARE @EmpID Bigint
DECLARE CurEmplyeeFC  CURSOR FOR 
SELECT EID  
FROM EmplyeeFC  

OPEN CurEmplyeeFC
FETCH NEXT FROM CurEmplyeeFC INTO @EmpID 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- loop traitement here
    FETCH NEXT FROM CurEmplyeeFC INTO @EmpID
END

CLOSE CurEmplyeeFC

answered Mar 26, 2015 at 13:36

Eric Siodmak

1515 bronze badges

3 Comments

Dan Field Over a year ago

While this is technically possible, it should not be used. It will perform horribly and is not necessary (Table Variables and/or temp tables can solve his problem)

Eric Siodmak Over a year ago

@DanField Okay it comes a technical choice

Dan Field Over a year ago

Sure, and there can be cases where a cursor is justified so it doesn't hurt to know it.

Collectives™ on Stack Overflow

Reading Rows into Variable in SQL Server

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related