Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Converting some Sql server syntax to Sybase Syntax.

Posted on 2016-10-03
8
Medium Priority
?
108 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 25

Accepted Solution

by:
chaau earned 1000 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 27

Assisted Solution

by:wilcoxon
wilcoxon earned 500 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
LVL 25

Assisted Solution

by:chaau
chaau earned 1000 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 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 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 27

Assisted Solution

by:wilcoxon
wilcoxon earned 500 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

972 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