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

x
?
Solved

Sql Syntax

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

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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