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
Solved

Sql Syntax

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

860 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