1st time using a cursor, can you have an IF inside?

[JanetH]

I am trying to loop through all records in a coloumn and determine if a record has a file extension or not. If it does I want to run select statement A if it doesn't then select statement B. But I can't seem to make it work in my cursor. I've never used a cursor before and I assume my syntax for the IF statement must be wrong. Is this the best way to try and do it? This is what I have so far:

DECLARE @extension int

DECLARE extension CURSOR for
SELECT charindex('.', reverse(tblImportObject.URI))
FROM tblImportObject
WHERE SiteID<>1

OPEN extension

FETCH NEXT FROM extension INTO @extension
WHILE @@FETCH_STATUS = 0

BEGIN
IF(@extension < 1)
SELECT Title
FROM tblImportObject
WHERE SiteID <>1
FETCH NEXT FROM EXTENSION INTO @extension
ELSE
SELECT SiteID
FROM tblImportObject
WHERE SiteID<>1
FETCH NEXT FROM EXTENSION INTO @extension
END


CLOSE extension
DEALLOCATE extension

Thanks in advance for any advice.

 

[jballum]

You could always remove the 'charindex('.', reverse(tblImportObject.URI))' from the cursor definition and do the 'charindex' in the if statement which might make the cursor more readable. Why are you reversing the field?

I am not sure if cursor names are case sensitive or not but your fetch statements in the loop refer to cursor EXTENSION and not extension.

What exactly are your errors you are getting when you say it doesn't work?

JB

 

[Andel]

Hi Janet, I'm not sure but what you're trying to do is kinda weird to me-- executing just a select statement inside a cursor. If you have for example 100 records in your cursor, that's how many times your select statement is executed. Are you sure you wanna do that?

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[Qaroven]

Also you may find it running endlessly unless you put begin/end around the select + fetch statements Ben
+61 403 395 052

 

[JH123]

You are right it is running the select statement 193 times. I just want it to run once for each record. How do I do that? I tried to put Begin and End around the select and fetch and got an error saying incorrect syntax near BEGIN. When it does run 193 times I do get the results that I want. Andel if I don't put just a select in the cursor what else do I put in there?
What I want to do is have it find the length of the extension of the first record and then if it is greater than 1, run select statement A on that record. If it's less than 1, then run a different select statement on that record. Then move to the next record and do the same thing. Perhaps the way I'm going about it is wrong. Is there a better way to do this?

 

[Cagliostro]

the correct syntax is
if @x <> 0
begin
-- do something
end else
begin
-- do other thing
end John Fill

 

[JH123]

Ok. That's what I have now in my code and that part is fine. It just runs it 193 times because I have 193 records. So now that is the only part I have to fix and don't know how.

 

[Cagliostro]

I can't understand, I think you should remove the while loop, or you want to do other things?
Look below:


DECLARE @extension int

DECLARE extension CURSOR for
SELECT charindex('.', reverse(tblImportObject.URI))
FROM tblImportObject
WHERE SiteID<>1

OPEN extension


FETCH NEXT FROM extension INTO @extension
IF(@extension < 1)
begin
SELECT Title
FROM tblImportObject
WHERE SiteID <>1
end ELSE
begin
SELECT SiteID
FROM tblImportObject
WHERE SiteID<>1
end
END
John Fill

 

[Andel]

I may be wrong but I think what you're trying to accomplish is to select the value in the title column if there's no extension of the value in the URI column, or select the siteid if there's an extension.

If I'm right, then read on.... otherwise, I'm sorry

Ok, your select statement in your cursor may return multiple result. So, if that select statement also returns 193, then you multiply 193 x 193 (result from cursor x result from select inside cursor). That's how many records you get from running your query.

If you're just trying to get either the title or siteid of each record, you don't need a cursor and here's what you have to do...


SELECT CASE
WHEN CHARINDEX('.', REVERSE(URI)) < 1 THEN siteid
WHEN CHARINDEX('.', REVERSE(URI)) >=1 THEN title
END
FROM tblImportObject


Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[JH123]

Andel
That's it thank you. I thought about a Case but then didn't think it would work. Thanks for your help.