creating a view inside while loop

Question

DECLARE @TEMP INT,@COUNT INT
SET @TEMP=1
SET @COUNT= (select COUNT(NsepAcc) from tblNsepAccount) 
WHILE (@COUNT > @TEMP)
BEGIN
create view topview as 
(select Top (@TEMP) NsepAcc,CommittedAmount,DepositedAmount from (select B.NsepAcc,A.CommittedAmount,C.DepositedAmount from tblStudentBankAccountDetail A join tblNsepAccount B on A.UserName=B.StudentUserName join tblBankTransaction C on A.AccountNumber=C.BAccount) as rowtable
except
select Top (@TEMP -1) NsepAcc,CommittedAmount,DepositedAmount from (select B.NsepAcc,A.CommittedAmount,C.DepositedAmount from tblStudentBankAccountDetail A join tblNsepAccount B on A.UserName=B.StudentUserName join tblBankTransaction C on A.AccountNumber=C.BAccount) as rowtable2)
IF ( (select DepositedAmount from topview) >= (select CommittedAmount from topview)AND((select DATEPART(DAY, GETDATE())) = 7))
begin
insert into tblNsepTopup values((select NsepAcc from topview),.75*(select CommittedAmount from topview),GETDATE() )
print 'top up'
end
else
begin
print 'no top up'
end
drop view topview
SET @TEMP =@TEMP +1
END

i  am unable to create this view.pl suggest

Answer 1

What is the reason that you need to keep on changing the definition of the view based on the loop? I suggest you keep all needed columns in the view and avoid changing its definition so frequently.

---

Many Thanks & Best Regards, Hua Min

Answer 2

If you want this view to be partially dynamic, don't create it as a view, create it as a table-valued function with parameter(s).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Hi,

You cannot put top as a variable , what you can do is create a dynamic query inside the while loops. But again is it necessary to create a view for each iteration in the loop? There can be other methods to derive the same result instead of explicitly creating a view.

Regards

asit