Solved

Sql Syntax

Posted on 2008-09-30
3
341 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
[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
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

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

635 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