Syntax error with dynamic SQL used in 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 2000 Forums

Transact-SQL (2000)

Syntax error with dynamic SQL used in cursor

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 17:59:17

Hi

I am using SQL 12.x and I am unable to figure out the foll syntax error for the below dynamic sql command.
How do I get around the error?

CREATE TABLE #QC
( TableName VARCHAR(100)
, Priority INTEGER
, LastUpdate DATETIME
, rowcnt INTEGER
)

DECLARE
@SQL VARCHAR(MAX)
, @TableName VARCHAR(100)
, @Priority INTEGER
, @LastUpdate DATETIME
, @DBName VARCHAR(100)

where @TableName has the value of real table names residing on the database like example PKG_QCRSShortCurrPositions_Glb and there are 27 such tables.

Below is the dynamic sql.

SELECT @SQL = ' INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = ''' + @TableName + ''', Priority = ' + CONVERT(VARCHAR, @Priority) + ' , select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR,LastUpdate,112) from' + @TableName + ', rowcnt = Count(*) FROM ' + @DBName + '.dbo.' + @TableName +
' WHERE Priority = ' + CONVERT(VARCHAR, @Priority) +
' and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - ' + CONVERT(VARCHAR,@Days)
EXEC (@SQL)

error msg occurs for the following statement

' , select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR,LastUpdate,112) from' + @TableName

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'fromPKG_AM_Audit_Rpt'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'fromPKG_CashDebPerCcyWPenSpotsTab'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'fromPKG_QCRSAccountsCashBucketsTab'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 18:02:27

Run this: PRINT @SQL

Do you see the issue now? If not, please post the output of it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 18:13:40

I put an extra space after " from". I got the output for @SQL but I am sure how to work around the issue which I believe is still in the subquery ?

INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = 'PKG_AM_Audit_Rpt', Priority = 1 , select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR,LastUpdate,112) from PKG_AM_Audit_Rpt, rowcnt = Count(*) FROM ghdb.dbo.PKG_AM_Audit_Rpt WHERE Priority = 1 and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - 2

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 18:24:18

Why cannot I do this?

DECLARE

@LastUpdate DATETIME

select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR,LastUpdate,112) from PKG_AM_Audit_Rpt

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 18:27:21

Because you don't have enough right parenthesis. Also, make sure to specify a size for your varchar, otherwise you'll be back asking why only 1 character is in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 18:41:18

But why do I keep getting the msg even though I put in a length for VARCHAR as 20 and fixed the parenthesis?

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@LastUpdate".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 18:45:38

Please repost the PRINT @SQL after you've made the changes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 18:53:23

Incorrect syntax near the keyword 'select'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@LastUpdate".
INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = 'PKG_AM_Audit_Rpt', Priority = 1 , select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR(30),LastUpdate,112)) from PKG_AM_Audit_Rpt, rowcnt = Count(*) FROM ghdb.dbo.PKG_AM_Audit_Rpt WHERE Priority = 1 and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 18:56:53

I don't understand what you are trying to do with that as that isn't valid syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-07 : 19:28:28

I need to get the LastUpdate value which lies in the variable @TableName In the above sample query the table name is PKG_AM_Audit_Rpt

When I run the below query it runs fine but when I run the entire SQL it gives the above error.

DECLARE

@LastUpdate DATETIME

select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR(12),LastUpdate,112)) from ghdb..PKG_AM_Audit_Rpt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 19:38:48

Get rid of the SELECT statement inside the query. Just put an aggregate function around LastUpdate.

I'm not sure why you are doing all of that convert business though, especially in the where clause.

Here's how the non-dynamic SQL would look like:


SELECT 
	TableName = 'PKG_AM_Audit_Rpt', 
	Priority = 1 , 
	CONVERT(INT,CONVERT(VARCHAR(30),MAX(LastUpdate),112)), 
	rowcnt = Count(*) 
FROM ghdb.dbo.PKG_AM_Audit_Rpt 
WHERE Priority = 1 and convert(INT, CONVERT(VARCHAR(30), LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR(30), getdate(), 112)) - 2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-07 : 23:07:19

quote:
Originally posted by sqlnovice123

Hi

I am using SQL 12.x

That's Sybase Adaptive Server ?

Are you aware that you are posting in SQL Server 2000 (Microsoft SQL Server) forum ?

KH
[spoiler]Time is always against us[/spoiler]

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-08 : 09:04:12

It is SQL. I will try to explain what I really want and what I did to get the column value, this may seem convoluted .

Below is the entire Query and output with LastUpdate currently as as NULL because I did not include it in the dynamic SQL.
I want to get the value of LastUpdate from each of the tables in the cursor.

For doing this I thought all I need to do is
declare LastUpdate as Datetime
Add the following select to the dynamic SQL.
', select @LastUpdate = CONVERT(INT,CONVERT(VARCHAR(30),LastUpdate,112)) from ' + @TableName
where @TableName will display the value of the actual TableName which store the column called LastUpdate .

There are 27 such tables and I need to get the LastUpdate column value from each of these tables.

example assume the work tables that contain the LastUpdate are called aaa and bbb in the below example
create table a(DBName varchar(10),TableName varchar(10),Priority int)
go

create table b(TableName varchar(10),LastUpdate datetime)
go

--Populate Table a
insert into a(DBName,TableName,Priority)Values('intldb','aaa',1)

create table aaa(descr varchar(10),lastupdate datetime)
create table bbb(descr varchar(10),lastupdate datetime)

insert into aaa select 'xyz',getdate() +1
insert into bbb select 'xyz',getdate() +2

select * from aaa

descr LastUpdate
xyz 2008-08-09 08:57:11.493

select * from a

DBNName TableName Priority
intldb aaa 1

select * from bbb

descr LastUpdate
xyz 2008-08-10 08:57:11.540

DECLARE
@SQL VARCHAR(MAX)
, @TableName VARCHAR(100)
, @ServerAlias VARCHAR(200)
, @ServerType VARCHAR(10)
, @BusinessID INTEGER
, @APM VARCHAR(20)
, @TeamIDCur INTEGER
, @ErrorNum INTEGER
, @ProcName VARCHAR(100)
, @DomainCode CHAR(2)
, @m VARCHAR(255)
, @Debug BIT
, @ReportTitle VARCHAR(MAX)
, @Days INTEGER
, @DBName VARCHAR(20)
, @rowcnt INTEGER
, @LastUpdate DATETIME
, @Priority INTEGER
, @TeamID INTEGER

SELECT @Priority = 1
SELECT @TeamID = 5171

SELECT @Debug = 1
SELECT @ServerAlias = AliasName
, @ServerType = CASE
WHEN ServerType = 'P' THEN 'PROD'
WHEN ServerType = 'T' THEN 'TEST'
WHEN ServerType = 'D' THEN 'DEV'
ELSE 'X'
END
FROM intldb.dbo.DatabaseServer
WHERE ServerName = @@SERVERNAME
IF @ServerType = 'PROD'
BEGIN
SELECT @Days = 1
END
Else
BEGIN
SELECT @Days = 2
END

CREATE TABLE #QC
( TableName VARCHAR(100)
, Priority INTEGER
, LastUpdate DATETIME
, rowcnt INTEGER
)

DECLARE PacketCursor CURSOR
FOR

SELECT DISTINCT DBName
, TableName
, Priority

FROM ghdb.dbo.vQCRS_GlobalPackets
WHERE Priority = @Priority
AND TeamID = @TeamID
AND TableName IS NOT NULL

OPEN PacketCursor

FETCH NEXT FROM PacketCursor INTO @DBName, @TableName, @Priority

WHILE @@FETCH_STATUS = 0
BEGIN

--SELECT @SQL = ' INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT @SQL = ' INSERT INTO #QC ( TableName, Priority, rowcnt)
SELECT TableName = ''' + @TableName + ''', Priority = ' + CONVERT(VARCHAR, @Priority) + ', rowcnt = Count(*) FROM ' + @DBName + '.dbo.' + @TableName +
' WHERE Priority = ' + CONVERT(VARCHAR, @Priority) +
' and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - ' + CONVERT(VARCHAR,@Days)
EXEC (@SQL)
PRINT @SQL
FETCH NEXT FROM PacketCursor INTO @DBName, @TableName, @Priority
END
CLOSE PacketCursor
DEALLOCATE PacketCursor
SELECT * FROM #QC

PKG_AM_Audit_Rpt 1 NULL 0
PKG_CashDebPerCcyWPenSpotsTab 1 NULL 0
PKG_QCRSAccountsCashBucketsTab 1 NULL 0
PKG_QCRSAcctsExSpotBalIntraday 1 NULL 0
PKG_QCRSCashDebPerCurrIntraday 1 NULL 0
PKG_QCRSCashDebtWithCashBktGlb 1 NULL 0
PKG_QCRSCurrTargetsLastUpdated 1 NULL 0
PKG_QCRSForwardsSettleDatesDeb 1 NULL 0
PKG_QCRSLatestFMCCashflowsTab 1 NULL 0
PKG_QCRSPortCurrAwayFromTarget 1 NULL 0
PKG_QCRSShortCurrPositions_Glb 1 NULL 0
PKG_QCRS_NonEmergingCashTable 1 NULL 0

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-08 : 11:11:17

appears the dynamic SQL does not like the @TableName which is showing twice so I may need another variable like
Declare @SQL1 VARCHAR(MAX)
SELECT @SQL1 = 'SELECT @LastUpdate=LastUpdate from' + @TableName
EXEC @SQL1

SELECT @SQL = ' INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = ''' + @TableName + ''', Priority = ' + CONVERT(VARCHAR, @Priority) + ',' + @SQL1 + ', rowcnt = Count(*) FROM ' + @DBName + '.dbo.' + @TableName +
' WHERE Priority = ' + CONVERT(VARCHAR, @Priority) +
' and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - ' + CONVERT(VARCHAR,@Days)
EXEC (@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-08 : 12:47:59

And what does the PRINT statement show with your last revision?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-08 : 13:20:33

It is printing the output for Last Update as SELECT LastUpdate from ghdb.dbo.PKG_AM_Audit_Rpt but I want the value of LastUpdate EXEC(@SQL1) output value

INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = 'PKG_AM_Audit_Rpt', Priority = 1, LastUpdate = SELECT LastUpdate from ghdb.dbo.PKG_AM_Audit_Rpt, rowcnt = Count(*) FROM ghdb.dbo.PKG_AM_Audit_Rpt WHERE Priority = 1 and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 13:28:15

You need to use sp_executesql if you want to return value through variable in dynamic sql. look for syntax in books online.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-08 : 14:11:28

It worked when I put parenthesis but the subquery expression below when using distinct gives the below error as there are multiple rows with the same value. using DISTINCT does not help. I tried using TOP 1 and that eliminated the error but I need to be able to use DISTINCT in the sub query. Why is DISTINCT not working?

error msg:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

', (select LastUpdate from ' + @DBName + '.dbo.' + @TableName + ')as LastUpdate '

SELECT @SQL = ' INSERT INTO #QC ( TableName, Priority, LastUpdate, rowcnt)
SELECT TableName = ''' + @TableName + ''', Priority = ' + CONVERT(VARCHAR, @Priority) + ', (select DISTINCT LastUpdate from ' + @DBName + '.dbo.' + @TableName + ')as LastUpdate ' + ', rowcnt = Count(*) FROM ' + @DBName + '.dbo.' + @TableName +
' WHERE Priority = ' + CONVERT(VARCHAR, @Priority) +
' and convert(INT, CONVERT(VARCHAR, LastUpdate, 112)) < CONVERT(INT, CONVERT(VARCHAR, getdate(), 112)) - ' + CONVERT(VARCHAR,@Days)
EXEC (@SQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-08 : 14:19:03

the subquery will return you a full resultset rather than a single value. you can only use a subquery that returns a single value in select list.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2008-08-08 : 14:24:41

Oh no. For example I may have 2 rows of data for the same table with different LastUpdate adn different Priority. How can I do this? using the subquery will not help then?

example

select distinct Priority,LastUpdate from PKG_QCRSShortCurrPositions_Glb

Priority LastUpdate
1 2008-08-07 03:40:47.807
2 2008-08-07 08:57:24.897
2 2008-08-07 16:08:22.513

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-08 : 14:25:53

Use a GROUP BY with an aggregate function, such as MAX or MIN.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources