asked on

Multiple condtional SQL statements: IF NOT EXISTS

Hi there,

I'm trying to insert/update a record, if it meets two conditions. I have two variables which get a querystring and an ip number. First I want to check if the querystring is already in the database. If not, insert the record. If the querystring is there, check if the ip number is already in the database. If not, insert the record into the database. I now have the following code: (MS-SQL / ASP.net 2.0 VB.net)

' Conditional INSERT command
getStatCmd = _
"IF NOT EXISTS (SELECT * FROM tblReferers WHERE refQueryString = '" & myQueryString & "') " & _
"IF NOT EXISTS (SELECT * FROM tblReferers WHERE refIpAddress = '" & myRemoteAddr & "') " & _
"INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer) " & _
"VALUES(" & _
"'" + myUrl + "'," & _
"'" + myQueryString + "'," & _
"'" + myRemoteAddr + "'," & _
"'" + myHttpReferer + "')"

' Conditional UPDATE command
updStatCmd = _
"UPDATE tblReferers " & _
"SET refCountIn = refCountIn + 1" & _
"WHERE refQueryString = '" & myRef & "'"

I keep getting syntax errors when playing with the insert command. Any ideas?

Thanks!

ASKER CERTIFIED SOLUTION

satish_nagdev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rineau

ASKER

Thanks Satish,

I've never worked with stored procedures before. I guess it has some advantages when it comes down to performance and security, though I heard that you only need to write them when you actually need them. I do not know if that is the case here?

Anyway, here's my sp:

ALTER PROCEDURE sdstats.spGetReferers

      (
      @myUrl varchar(75),
      @myQueryString varchar(50),
      @myRemoteAddr varchar(12),
      @myHttpReferer varchar(155)
      )

AS
      BEGIN
      SET NOCOUNT ON

      IF NOT EXISTS
            (SELECT * FROM tblReferers WHERE refQueryString = @myQueryString or refIpAddress = @myRemoteAddr)
             INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer)
             VALUES(@myUrl ,@myQueryString ,@myRemoteAddr ,@myHttpReferer)

      END
      RETURN

I am not sure where to declare the variables though... Am I on the right track?

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

satish_nagdev

yea procedures have edge in terms of performance as far as i've heard and felt also practically in my last project.
well i prefer to use them wherever possible one reason is
software/apps change over time in some cases say you've same work to do at many places wouldnt it be good to have a SP and reduce the work.

happy coding.

cheers,
satish.