CODEWITHSUNDEEP
sql-servert-sql
user2548018
user2548018

Reputation: 21

Reading Rows into Variable in SQL Server

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.

Upvotes: 0

Views: 2719

Answers (3)

Dan Field
Dan Field

Reputation: 21641

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.

Upvotes: 0

Eric Siodmak
Eric Siodmak

Reputation: 151

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

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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]

Upvotes: 2

Related Questions