Solved

Converting some Sql server syntax to Sybase Syntax.

Posted on 2016-10-03
8
22 Views
Last Modified: 2016-10-05
I need to convert this Sql server sql to Sybase Sql. I am getting an error message : "Incorrect syntax near TOP'. How do I change the code below so that it will run on Sybase?


IF NOT EXISTS (SELECT TOP 1 * FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT
0
Comment
Question by:brgdotnet
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 250 total points
ID: 41827371
Just get rid of TOP 1 and it should work:
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT 

Open in new window

0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41827373
I swear I tried that, and it did not work? I will have to try it out at work tomorrow.
0
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 125 total points
ID: 41827375
As chaau said, I think it should work if you remove the "top 1".  Chaau's mod of "select 1" rather than "select *" should make it more efficient.
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41827383
Thanks gentlemen.  I remember, I removed the " top"  and the recieved the error message, incorrect syntax near from? Any ideas?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 24

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 41827385
When you remove the TOP keyword you also need to remove the number that follows it. The correct way would be to remove TOP 1
0
 
LVL 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
ID: 41827392
@Author - Is it done ? If not use below.

Just to summarize what other experts are saying.

IF NOT EXISTS (SELECT * FROM sys.columns WHERE NAME = @columnName AND Object_ID = Object_ID (@TableName))
BEGIN
  SELECT @sqlStatement = 'ALTER TABLE ' + @tableName + ' ADD ' + @newAddCountColumn + ' ' + VARCHAR(50) NULL '
  PRINT 'Creating a new column '
  EXEC (@sqlStatement)
END ELSE PRINT 

Open in new window

0
 
LVL 26

Assisted Solution

by:wilcoxon
wilcoxon earned 125 total points
ID: 41827434
Depending on your product/version of Sybase, doing:
IF NOT EXISTS (SELECT 1 ...

Open in new window


may be more efficient than:
IF NOT EXISTS (SELECT * ...

Open in new window


I'm pretty sure this was true in ASE 11 (really old) but I think it became no longer true in ASE 12-15 (not sure which version exactly).  I don't know the other Sybase engines nearly as well (so can't say for them).
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 41831023
Thank you. Actually one other problem is that you are suppose to query syscolumns not sys.columns, on Sybase.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now