• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

SQL Syntax Error

Hi there,

I am trying to iterate thru an existing table using CURSOR FOR and checking if the current row being processed has a similar row in another newly created table.  If there isnt a similar row in the new table, then i would want to insert that row which is being processed into the new table.  The following is a sample of my code.

transaction_unique is the name of my new table.

I am getting syntax error with my CASE statement.  Could someone please point out the correct syntax? thks

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @count = @count + 1
 --check if the current transaction exist
 CASE
  WHEN @member_deduct > 0 THEN
    IF NOT EXISTS (SELECT TOP 1 * FROM transaction_unique WHERE product_code = @product_code   member_deduct > 0) THEN
INSERT INTO transaction_unique VALUES(@product_code,'member_deduct')
END

FETCH currentRow INTO @product_code,@member_deduct
END
0
singhch
Asked:
singhch
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CASE is for a SQL statement inline.
what you need is IF...

I am getting syntax error with my CASE statement.  Could someone please point out the correct syntax? thks

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @count = @count + 1
 --check if the current transaction exist
 IF @member_deduct > 0
   IF NOT EXISTS (SELECT TOP 1 * FROM transaction_unique WHERE product_code = @product_code   member_deduct > 0)
     INSERT INTO transaction_unique VALUES(@product_code,'member_deduct')

 FETCH currentRow INTO @product_code,@member_deduct
END
0
 
patriktCommented:
You not posted on which SQL server is this running.

But the CASE is not code branch statement. Use IF instead.
0
 
patriktCommented:
But generally this aproach is nto good for performance.
Better to use INSERT INTO ... SELECT construction with correct WHERE clause and not to use cursor.
Cursor is not fast solution.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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