SQL, Error message of syntax

Hi Experts,

I got this error msg :
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sp_rept_titleloan_DuplicateVINReport'.

And I did not see any red underscore line to indicate issue in query screen. Do you know how to fix this error? Thank you.


use SMRLoanAppLive
go


/******************************************************************
Created by:		Joy Tan
Created on:		11/9/2015
Description:	for the report Duplicate VIN Report
Testing:	
modified on		modified by		desc
-----------		-----------		-----

*******************************************************************/




alter procedure [dbo].[sp_rept_titleloan_DuplicateVINReport]
       @VPID int
as
begin
 
  
SELECT tblVP.UserFirstName+' '+tblVP.UserLastName AS VPName,LoanStoreID,AutomobileVIN,AutomobileMake,AutomobileModel,AutomobileYear,AutomobileTitleNumber,
	ContractString,LoanStatusText,LoanAmountFinanced,LoanDate,tblUsers.UserFirstName,tblUsers.UserLastName,CustomerFirstName,CustomerLastName,
	(SELECT COUNT(VoidLoanRequestID) FROM tblLoanVoidRequests WHERE tblLoans.LoanID=tblLoanVoidRequests.VoidLoanID AND ISNULL(VoidLoanRequestIsProcessed,0)=0) AS PendingVoids
FROM tblLoans INNER JOIN
	tblStores ON tblLoans.LoanStoreID=tblStores.StoreID INNER JOIN
	tblAutomobiles ON tblLoans.AssetID=tblAutomobiles.AssetID INNER JOIN
	tblCustomers ON tblLoans.CustomerID=tblCustomers.CustomerID INNER JOIN
	tblLoanStatus ON tblLoans.LoanStatusID=tblLoanStatus.LoanStatusID INNER JOIN
	tblUsers ON tblLoans.LoanUserID=tblUsers.UserID INNER JOIN
	tblUsers tblVP ON tblVP.UserID=tblStores.VPID
WHERE AutomobileVIN IN (
	SELECT AutomobileVIN
	FROM tblLoans INNER JOIN
		tblAutomobiles ON tblLoans.AssetID=tblAutomobiles.AssetID
	WHERE LoanStatusID NOT IN (2,8)
		AND LoanStoreID IN (SELECT StoreID FROM tblStores WHERE StoreCompany IN (1,2,3,6))
		AND AssetTypeID=1
		AND ISNULL(IsFraud,0)=0
		AND LoanStoreID IN (SELECT StoreID FROM tblStores WHERE VPID = @VPID OR @VPID IS NULL)
	GROUP BY AutomobileVIN
	HAVING COUNT(AutomobileVIN)>1
	)
	AND tblLoans.LoanStatusID NOT IN (2,8)
	AND AssetTypeID=1
    AND ISNULL(IsFraud,0)=0
	AND LoanStoreID IN (SELECT StoreID FROM tblStores WHERE VPID = @VPID OR @VPID IS NULL)
ORDER BY tblVP.UserFirstName+' '+tblVP.UserLastName,AutomobileVIN,LoanStoreID

End

go

grant execute sp_rept_titleloan_DuplicateVINReport to public

go

Open in new window

tanj1035Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
For starters, on line 19 above you need to surround parameters in parentheses..

alter procedure [dbo].[sp_rept_titleloan_DuplicateVINReport]
   (@VPID int)
as

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
>grant execute sp_rept_titleloan_DuplicateVINReport to public
Not sure, but you may need to include the schema dbo. before the SP name in a GRANT statement.
Kyle AbrahamsSenior .Net DeveloperCommented:
I believe you're missing the 'ON'

GRANT EXECUTE ON dbo.sp_rept_titleloan_DuplicateVINReport TO public

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Aneesh RetnakaranDatabase AdministratorCommented:
Why in the world your stored procedure starts with 'sp_'  , system procedures starts with sp_
Jim HornMicrosoft SQL Server Data DudeCommented:
tanj - So, how's it going?
tanj1035Author Commented:
Hi Jim, thanks for your reply.

I tried your suggestions of (  ) and dbo. and they do not make any difference.
The error message indicates "grant execute on sp_rept_titleloan_DuplicateVINReport to public", which I am missing "on".

Thanks for your comment again!
tanj1035Author Commented:
HI Annesh,

In our database, the name of all SPs start with sp_. So, that is why I follow the format. I know that confused people.
Jim HornMicrosoft SQL Server Data DudeCommented:
>In our database, the name of all SPs start with sp_
The reason Aneesh made that point is that the query compiler interprets sp_ as a system stored procedure, so when T-SQL is compiled with a Stored Procedure (SP) that begins with sp_, that forces the compiler to first search the system stored procedures for that SP name, and when that completes it searches user-defined SP's, so your queries are taking that much longer to execute.

Among experienced developers this would also be an acceptable answer to the question 'How can you identify amateur developers?'.
tanj1035Author Commented:
Thanks for your detailed explanation, Jim. I am a beginner in SQL field. Thanks for everyone's support and help. I will try to give credit to every expert which made the contributions to my questions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.