Solved

Multiple condtional SQL statements: IF NOT EXISTS

Posted on 2007-04-04
4
2,383 Views
Last Modified: 2012-06-21
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!

0
Comment
Question by:Rineau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
satish_nagdev earned 250 total points
ID: 18849045
first advise dont use queries like that i.e. write queries in code(vb.net/c# here)
second i advise you to write a stored procedure to do the job pass the parameters then do something like following

IF NOT EXISTS (SELECT * FROM tblReferers WHERE refQueryString = @QueryString or refIpAddress = @IPAddres)
INSERT INTO tblReferers(refPageAccessed,refQueryString,refIpAddress,refReferer)
VALUES(@myUrl ,@myQueryString ,@myRemoteAddr ,@myHttpReferer ')
else write the update part.

thanks,
satish.
0
 
LVL 4

Author Comment

by:Rineau
ID: 18849260
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?
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 18849593
With the procedure-declaration you're OK. The parameter-list is the declaration of the variables.

Calling your procedure can be done with variables or constants. First try to test it in SQL, afterwarts call it from your code

exec  sdstats.spGetReferers    @myUrl ,    @myQueryString,      @myRemoteAddr,     @myHttpReferer
OR
exec  sdstats.spGetReferers    "my URL HERE" ,    "my Query String...",     "my Remote Addr.....",     "HTTP:..myHttpReferer"


0
 
LVL 4

Expert Comment

by:satish_nagdev
ID: 18851335
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.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question