Need help with IF and EXISTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with IF and EXISTS

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-15 : 14:01:19

I'm trying to search my table for a url, and if it in the table, increment the number of hits of the url. Here's my code:

tblLinks
-----------------------
URL | NumOfHits
-----------------------


ALTER PROCEDURE sproc_UpdateLink
	
	(
		@url varchar
	)
	
AS
	
	IF EXISTS(SELECT LinkURL
		FROM tblLinks
		WHERE @url = LinkURL)
	BEGIN
	        UPDATE tblLinks
	        SET NumOfHits = NumOfHits + 1
	        WHERE @url = LinkURL
	END

RETURN

I've also tried:


ALTER PROCEDURE sproc_UpdateLink
	
	(
		@url varchar
	)
	
AS
	
	IF (SELECT LinkURL
		FROM tblLinks
		WHERE @url = LinkURL) IS NOT NULL
	BEGIN
	        UPDATE tblLinks
	        SET NumOfHits = NumOfHits + 1
	        WHERE @url = LinkURL
	END

RETURN

but that didn't work either.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-15 : 14:08:15

>> but that didn't work either.

And by "didn't work" you mean .... ?

- Jeff
http://weblogs.sqlteam.com/JeffS

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-15 : 14:09:30

It didn't increment NumOfHits

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-15 : 14:19:28

Maybe nothing matched the search criteria. What does this return?
SELECT Matches = count(*) FROM tblLinks WHERE @url = LinkURL

Also, why is your @url only a single character?

CODO ERGO SUM

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-15 : 14:21:57

The url that I'm testing is in there

I ran:
SELECT LinkURL FROM tblLinks
WHERE @url = LinkURL
and that returned the url I entered.

I also ran:
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
and that incremented NumOfHits for the url that I entered.

The problem is with the If statement

Apples
Posting Yak Master

146 Posts

Posted - 2008-01-15 : 14:26:18

Nevermind, I figured it out. For some reason I had to specify the size of @url in the stored procedure.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-15 : 14:28:30

quote:
Originally posted by Apples

Nevermind, I figured it out. For some reason I had to specify the size of @url in the stored procedure.

You only have to specify it if your url is longer than the default of one character for a varchar variable.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 01:12:05

You should always specify the size of varchar,char,etc. Otherwise you may get unexpected result which is sometimes difficult to debug

1
Declare @v varchar
set @v='test'
select @v

2
Select cast('This has more than thirty characters' as varchar)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources