Solved

Sql Syntax

Posted on 2008-09-30
3
332 Views
Last Modified: 2012-08-13
Hello, I cannot get the syntax of a sql stored procedure correct.  Here are the errors I get:

Msg 156, Level 15, State 1, Procedure sp_CheckRefferingID, Line 9
Incorrect syntax near the keyword 'Insert'.
Msg 1046, Level 15, State 1, Procedure sp_CheckRefferingID, Line 10
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Msg 156, Level 15, State 1, Procedure sp_CheckRefferingID, Line 15
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure sp_CheckRefferingID, Line 18
Incorrect syntax near the keyword 'Else'.
Msg 156, Level 15, State 1, Procedure sp_CheckRefferingID, Line 21
Incorrect syntax near the keyword 'Insert'.
Msg 156, Level 15, State 1, Procedure sp_CheckRefferingID, Line 25
Incorrect syntax near the keyword 'Else'.
Msg 102, Level 15, State 1, Procedure sp_CheckRefferingID, Line 26
Incorrect syntax near ')'.

Thanks!
create procedure sp_CheckRefferingID

	@RefferingID int,

	@RefferingDomain VarChar(100),

	@ClientIP VarChar(15)

As

	If Exists(Select * From UserDomains Where UserID = @RefferingID and DomainName = @RefferingDomain)

		If Not Exists(Select * From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

			--Insert New Visitor

			Insert Into InboundTraffic(DomainID, VisitStart, IP)

			Values ((Select DomainID From UserDomains Where DomainName = @RefferingDomain), GetDate(), @ClientIP)

			--Update User Credit Balance

			Update Users Set PixelCredits = PixelCredits + 1 Where UserID = RefferingID

			--Select QueryData

			Select 1, (Select Top 1 VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

		Else

			--Visiotor Already Registered

			Select 2, (Select Top 1 VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

	Else

		If Not Exists(Select * From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

			--Insert New Visitor

			Insert Into InboundTraffic(DomainID, VisitStart, IP)

			Values (null, GetDate(), @ClientIP)

			--Select QueryData

			Select 3, (Select Top 1 VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

		Else

			Select 4, (Select Top 1 VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

Open in new window

0
Comment
Question by:grogo21
  • 2
3 Comments
 
LVL 11

Assisted Solution

by:aaronakin
aaronakin earned 200 total points
ID: 22606980
The following line is incorrect syntax.

Insert Into InboundTraffic(DomainID, VisitStart, IP)
Values ((Select DomainID From UserDomains Where DomainName = @RefferingDomain), GetDate(), @ClientIP)

Change to:

Insert Into InboundTraffic(DomainID, VisitStart, IP)
Select DomainID, GetDate(), @ClientIP From UserDomains Where DomainName = @RefferingDomain
0
 
LVL 11

Assisted Solution

by:aaronakin
aaronakin earned 200 total points
ID: 22607021
You also need to use BEGIN and END in any of your IF/ELSE conditions if they contain more than one line...

IF @x =1
BEGIN
...
END
ELSE
BEGIN
...
END
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 300 total points
ID: 22607029
Here you go...

Although you really shouldn't name your procedures sp_.  It should be up_ or something else:

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.

The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Source:
http://www.eggheadcafe.com/community/aspnet/13/10042734/i-just-went-through-this.aspx



create procedure sp_CheckRefferingID

        @RefferingID int,

        @RefferingDomain VarChar(100),

        @ClientIP VarChar(15)

As

        If Exists(Select 1 From UserDomains Where UserID = @RefferingID and DomainName = @RefferingDomain)

          begin

                If Not Exists(Select 1 From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate()))

                    begin

                        --Insert New Visitor

                        Insert Into InboundTraffic(DomainID, VisitStart, IP)

                        Select DomainID,GetDate(), @ClientIP From UserDomains Where DomainName = @RefferingDomain
 

                        --Update User Credit Balance

                        Update Users 

                        Set PixelCredits = PixelCredits + 1 

                        Where UserID = RefferingID

                        --Select QueryData

                        Select Top 1 1,VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

                    end

                Else

                        --Visiotor Already Registered

                        Select Top 1 2,VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

          end

        Else

                If Not Exists(Select * From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate()))

                    begin

                        --Insert New Visitor

                        Insert Into InboundTraffic(DomainID, VisitStart, IP)

                        Values (null, GetDate(), @ClientIP)

                        --Select QueryData

                        Select Top 1 3,VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

                    end

                Else

                        Select Top 1 4,VisitorID From InboundTraffic Where IP = @ClientIP and VisitStart > DateAdd(Minute, -60, GetDate())

Open in new window

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

747 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

12 Experts available now in Live!

Get 1:1 Help Now