Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple condtional SQL statements: IF NOT EXISTS

Posted on 2007-04-04
4
Medium Priority
?
2,388 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 1000 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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

704 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