Dynamic SQL and Cursors - 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)

Dynamic SQL and Cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-02-07 : 10:18:08

Joe writes "I am trying to put together a SP that processes one table "A", and for each record on the table "A", it will process table "B", being the where clause a filed from table "A".

bellow is the code that I put together. The error occurs on the declare cursor with an exec(@sql).

Your help in achieving this is much appreciated.

Thanks

Joe.
set nocount on

DECLARE @Customerid decimal(9)
DECLARE @AlertNumber decimal(9)
DECLARE @EffectiveDate Datetime
DECLARE @ExpiryDate Datetime
DECLARE @AlertDescription char(50)
DECLARE @EmailText VarChar
DECLARE @SQLText VarChar
DECLARE @EmailtoMember char(1)
DECLARE @CustomerLastName char(30)
DECLARE @CustomerName char(30)
DECLARE @Email char(30)
DECLARE @PhoneNumber char(15)
DECLARE @PhoneMobile char(15)
DECLARE @BusinessName char(50)
DECLARE @BusAddress1 char(30)
DECLARE @BusAddress2 char(30)
DECLARE @BusCity char(20)
DECLARE @BusProvince char(20)
DECLARE @WebPage char(50)
DECLARE @Email1 char(50)
DECLARE @Email2 char(50)
DECLARE @Email3 char(50)
DECLARE @Email4 char(50)
DECLARE @Email5 char(50)

DECLARE @AlertLinkId char(50)
DECLARE @TodaysDate DateTime
DECLARE @AlertFound Char(1)
DECLARE @SQL VarChar(1000)

DECLARE @ListingNumber decimal(15)

/* Select data to be processed */

SELECT @TodaysDate = CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),112))

DECLARE Acursor CURSOR READ_ONLY FOR
Select
Customerid,
AlertNumber,
EffectiveDate,
ExpiryDate,
AlertDescription,
CAST(EmailText as varchar),
CAST(SQLText as varchar),
EmailtoMember,
CustomerLastName,
CustomerName,
Email,
PhoneNumber,
PhoneMobile,
BusinessName,
BusAddress1,
BusAddress2,
BusCity,
BusProvince,
WebPage,
Email1,
Email2,
Email3,
Email4,
Email5
from v_Alerts_01 where EffectiveDate <= @TodaysDate and ExpiryDate >= @TodaysDate

OPEN Acursor
FETCH NEXT FROM Acursor
INTO
@Customerid,
@AlertNumber,
@EffectiveDate,
@ExpiryDate,
@AlertDescription,
@EmailText,
@SQLText,
@EmailtoMember,
@CustomerLastName,
@CustomerName,
@Email,
@PhoneNumber,
@PhoneMobile,
@BusinessName,
@BusAddress1,
@BusAddress2,
@BusCity,
@BusProvince,
@WebPage,
@Email1,
@Email2,
@Email3,
@Email4,
@Email5

WHILE @@FETCH_STATUS = 0
BEGIN

Select @AlertFound = '0'
Select @AlertLinkId = RTrim(CAST(@CustomerId AS Nchar)) + RTrim(CAST(@AlertNumber AS Nchar)) + convert(char, @TodaysDate, 112)
Select @SQL = 'Select ListingNumber from v_ListingLog_01 where ' + @SQLText
DECLARE Lcursor CURSOR READ_ONLY FOR
Exec (@SQL)

OPEN Lcursor
FETCH NEXT FROM Lcursor
INTO
@ListingNumber
IF @@FETCH_STATUS = 0
BEGIN
Select @AlertFound = '1'

END

WHILE @@FETCH_STATUS = 0

BEGIN
if NOT EXISTS (select * from AlertListings where CustomerId = @CustomerId and Alertnumber = @AlertNumber and AlertDate = @TodaysDate and listingNumber = @listingNumber)
Insert Into AlertListings Values(@CustomerId,@AlertNumber,@TodaysDate,@listingNumber,@AlertLinkId)

FETCH NEXT FROM Lcursor
INTO @ListingNumber
END
CLOSE Lcursor
DEALLOCATE Lcursor

/* If @AlertFound = '1'
BEGIN
* Generate Email *
END */
FETCH NEXT FROM Acursor
INTO
@Customerid,
@AlertNumber,
@EffectiveDate,
@ExpiryDate,
@AlertDescription,
@EmailText,
@SQLText,
@EmailtoMember,
@CustomerLastName,
@CustomerName,
@Email,
@PhoneNumber,
@PhoneMobile,
@BusinessName,
@BusAddress1,
@BusAddress2,
@BusCity,
@BusProvince,
@WebPage,
@Email1,
@Email2,
@Email3,
@Email4,
@Email5

lfmn
Posting Yak Master

141 Posts

Posted - 2003-02-07 : 11:36:46

are you sure you have a value for every instance of @SQLText?

SQL is useful if you don't know cursors :-)

lfmn
Posting Yak Master

141 Posts

Posted - 2003-02-07 : 11:46:30

that's what I get for answering without testing!

You can not use this code:

DECLARE Lcursor CURSOR READ_ONLY FOR
Exec (@SQL)

You would need to make the entire inside cursor dynamic.

Although I haven't looked at the code closely enough to give you a solution, I would be very suprised if there is not a better way than a cursor within a cursor.

Good Luck.

Also, Don't let nr catch you creating a cursor within a cursor or he'll "Master Smack Fu Cursor" you into next week :-)

SQL is useful if you don't know cursors :-)

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-07 : 12:49:06

quote:

that's what I get for answering without testing!

You can not use this code:

DECLARE Lcursor CURSOR READ_ONLY FOR
Exec (@SQL)

You would need to make the entire inside cursor dynamic.

Although I haven't looked at the code closely enough to give you a solution, I would be very suprised if there is not a better way than a cursor within a cursor.

Good Luck.

Also, Don't let nr catch you creating a cursor within a cursor or he'll "Master Smack Fu Cursor" you into next week :-)

SQL is useful if you don't know cursors :-)

He'd love some of my code then ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources