Improve company productivity with a Business Account.Sign Up

x
?
Solved

Sql Syntax

Posted on 2008-09-30
3
Medium Priority
?
360 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 800 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 800 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 1200 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

585 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