Solved

Multiple condtional SQL statements: IF NOT EXISTS

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

23 Experts available now in Live!

Get 1:1 Help Now