Solved

Multiple condtional SQL statements: IF NOT EXISTS

Posted on 2007-04-04
4
2,379 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
  • 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now