use dynamic sql to get a cursor

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 2005 Forums

Transact-SQL (2005)

use dynamic sql to get a cursor

Author

Topic

zizhang
Starting Member

3 Posts

Posted - 2011-05-22 : 16:53:30

I am trying to use dynamic sql to get a cursor. But I got some funny syntax problems.

Hope someone could explain to me.

if I run the following code, I got the following error:
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'select * from MISTY_OWNER.MISTY_F_STC_EXECUTIONS where transactiondateid=20110509 AND DistributionId='121028286'.


DECLARE @EXTRACTDATE VARCHAR(30);
SET @EXTRACTDATE='20110509'; --2011-05-09
DECLARE @OPENQUERY VARCHAR(500);

--  
--	Portfolio,
SET @OPENQUERY = ' 
DECLARE RECORD_CURSOR CURSOR FOR 
SELECT
	TransactionDateId,
	TransactionType,
	TradeDate,
	ExecutionId,
	DistributionId,
	product,
	Moniker,
	SettlementDate,
	Symbol,
	Cusip,
	Side,
	Price,
	Quantity,
	ExecutingBroker,
	ExecutionTime,
	Source,
	Destination,
	Service,
	OrderType,
	HardCommission,
	SoftCommission,
	GrossCredit
	from openquery( DBWH_US_PROD, ''select * from MISTY_OWNER.MISTY_F_STC_EXECUTIONS where transactiondateid=' 
    + @EXTRACTDATE + ' AND DistributionId=''''121028286'''' '')';
    --from openquery( DBWH_US_PROD, ''select * from MISTY_OWNER.MISTY_F_STC_EXECUTIONS where DistributionId=''''121028286'''''')';
EXEC(@OPENQUERY)
DEALLOCATE RECORD_CURSOR

But if I just Move 'DECLARE RECORD_CURSOR CURSOR FOR ' from the @OPENQUERY into the EXEC, it works fine....


DECLARE @EXTRACTDATE VARCHAR(30);
SET @EXTRACTDATE='20110509'; --2011-05-09
DECLARE @OPENQUERY VARCHAR(500);

--  
--	Portfolio,
SET @OPENQUERY = ' 
SELECT
	TransactionDateId,
	TransactionType,
	TradeDate,
	ExecutionId,
	DistributionId,
	product,
	Moniker,
	SettlementDate,
	Symbol,
	Cusip,
	Side,
	Price,
	Quantity,
	ExecutingBroker,
	ExecutionTime,
	Source,
	Destination,
	Service,
	OrderType,
	HardCommission,
	SoftCommission,
	GrossCredit
	from openquery( DBWH_US_PROD, ''select * from MISTY_OWNER.MISTY_F_STC_EXECUTIONS where transactiondateid=' 
    + @EXTRACTDATE + ' AND DistributionId=''''121028286'''' '')';
    --from openquery( DBWH_US_PROD, ''select * from MISTY_OWNER.MISTY_F_STC_EXECUTIONS where DistributionId=''''121028286'''''')';
EXEC('DECLARE RECORD_CURSOR CURSOR FOR ' + @OPENQUERY)
DEALLOCATE RECORD_CURSOR

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-22 : 17:49:59

Why do you even need a cursor for this? You are not doing anything with the cursor, no fetch - no loop...doesn't really make sense.

zizhang
Starting Member

3 Posts

Posted - 2011-05-22 : 18:09:33

This is because
DECLARE @OPENQUERY VARCHAR(500);

if I have the declare cursor in @openquery, it will be over 500... so that's why there will be error.

zizhang
Starting Member

3 Posts

Posted - 2011-05-22 : 18:11:11

I just copied a piece of the code.
Thanks for your response. Please see my reply... It is really a stupid mistake. But I really think the error message in sql is usually not helpful

quote:
Originally posted by jeffw8713

Why do you even need a cursor for this? You are not doing anything with the cursor, no fetch - no loop...doesn't really make sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources