Cursor with Dynamic Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor with Dynamic Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-23 : 07:28:55

Waseem writes "I am trying the following two statement but failed please help me ?

Problem #1
@MDay = Month(getdate())
@MYer = Month(getdate())

Declare EmpTran Cursor for Select Distinct TrDate, Empno, TrNo From Trans Where Month(TrDate) = @MDay And Year(TrDate) = @MYer Order By Empno, TrDate

Problem #2
@FNm = DateName(mm,GetDate()) + Year(GetDate()) + "Tr"
Insert @FNm (TrDate, EmpNo, TrNo) values (@TDt, @TEmp, @TrCod)

Both of the above statement are not working can any one help me to sort out the issue.

Thanks in Advance"

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 09:44:04

WHERE to begin?

How about, what are you really trying to do...in business terms..not tech terms...

(ALSO, you know you're missing a SET or SELECT, right?)

Brett

8-)

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-23 : 12:04:39

As Brett suggested, it would help us greatly if you could provide an explanation of what you are trying to achieve here.

Anyway, your code will produce many syntax errors.

quote:

@MDay = Month(getdate())
@MYer = Month(getdate())

You must declare your variables before attempting to assign values to them.

DECLARE @MDay SMALLINT
DECLARE @MYer SMALLINT

SET @MDay = Month(getdate())
SET @MYer = Month(getdate())

Also the code for your cursor is incomplete.


DECLARE EmpTran CURSOR
FOR
Select Distinct TrDate, Empno, TrNo 
From Trans 
Where Month(TrDate) = @MDay 
And Year(TrDate) = @MYer 
Order By Empno, TrDate

OPEN EmpTran
FETCH NEXT FROM EmpTran
INTO @TrDate, @Empno, @TrNo

WHILE (@@FETCH_STATUS = 0 )
BEGIN

.... whatever you want here
END

FETCH NEXT FROM EmpTran
INTO @TrDate, @Empno, @TrNo

CLOSE EmpTran
DEALLOCATE EmpTran

quote:

@FNm = DateName(mm,GetDate()) + Year(GetDate()) + "Tr"
Insert @FNm (TrDate, EmpNo, TrNo) values (@TDt, @TEmp, @TrCod)

I don't understand what you are trying to achieve here.
What is @FNm? more precisely, what data type is it? Looks like a table to me but what does the following mean ?!

@FNm = DateName(mm,GetDate()) + Year(GetDate()) + "Tr"

---------------
Shadow to Light

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-23 : 12:13:45

Also, why do you need a cursor?

what are you trying to acheive? copying data from one table to another?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources