Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

Converting some Sql server syntax to Sybase Syntax.

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
brgdotnet
Asked:
brgdotnet
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
chaauCommented:
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
 
brgdotnetAuthor Commented:
I swear I tried that, and it did not work? I will have to try it out at work tomorrow.
0
 
wilcoxonCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
brgdotnetAuthor Commented:
Thanks gentlemen.  I remember, I removed the " top"  and the recieved the error message, incorrect syntax near from? Any ideas?
0
 
chaauCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
 
wilcoxonCommented:
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
 
brgdotnetAuthor Commented:
Thank you. Actually one other problem is that you are suppose to query syscolumns not sys.columns, on Sybase.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now