Create view from cursor in SQL?
I have a table (CheckNumber) with this data:
ID NumberFrom NumberTo StateID
1 1 10 1
2 2 3 2
These numbers represent physical documents and their states (1 can be used and 2 is unusual) So now I want to create a document and I want to know what number the document will have and what nummbers can be used.
So I created this cursor.
Declare @numberUsage as table(accountID int,Number Int)
Declare @bankID int
Declare @stateID int
Declare @beginNumber int
Declare @endNumber int
Declare cCursor cursor for Select accountID, stateID, beginNumber,endNumber From Finances.CheckNumber
Open cCursor
Fetch cCursor Into @bankID,@stateID,@beginNumber,@endNumber
While @@FETCH_STATUS = 0
Begin
Declare @actual int
Set @actual = @beginNumber
If @stateID = 1
Begin
While @actual <= @endNumber
Begin
if not exists(Select Number From Finances.CheckNPayment Where accountID = @bankID and Number = @actual)
Begin
Insert Into @numberUsage values(@bankID,@actual)
End
Set @actual = @actual + 1
End
End
Else
Begin
While @actual <= @endNumber
Begin
Delete From @numberUsage Where accountID = @bankID And Number = @actual
Set @actual = @actual + 1
End
End
Fetch cCursor Into @bankID,@stateID,@beginNumber,@endNumber
End
Close cCursor
Deallocate cCursor
Select * From @numberUsage
The result will be the following:
accountID Number
1 1
1 4
1 5
1 6
1 7
1 8
1 9
1 10
Can the result of a table be converted to a view?
source to share
It will take a few steps, but it is possible. I'll try to explain each part:
Get a list of sequential numbers:
select row_number() over (order by name) as RN from master..spt_values
If you need more, you can cross yourself and you will see a much wider range. Now you can use this to get your usage range.
;with Nums as (
select row_number() over (order by name) as RN from master..spt_values)
select *
from Nums n
inner join CheckNumber cn
on n.RN between cn.NumberFrom and cn.NumberTo
"with as ()" is called Common Table Expression (CTE) . The link contains more information on how it works.
Now we just need to exclude unused elements. We can use the same idea to create a list of unused numbers and then use EXCEPT
.
;with Nums as (
select row_number() over (order by name) as RN from master..spt_values)
select n.RN
from Nums n
inner join CheckNumber cn
on n.RN between cn.NumberFrom and cn.NumberTo
where cn.StateId = 1
EXCEPT
select n.RN
from Nums n
inner join CheckNumber cn
on n.RN between cn.NumberFrom and cn.NumberTo
where cn.StateId = 2
You should easily adapt this query to your actual data structure and data.
source to share