May 28, 2013 at 7:42 am
Hey guys -
I'm running into an issue where SQL Server is barking at me over a syntax issue when testing a stored procedure. The SP simply Inserts a record into a table which contains some date fields.
Here's a clip of my SP:
ALTER PROCEDURE [dbo].[sp_InsertDealerLocation]
...fields...more fields...
@dteLocationLicenseEstDate SmallDateTime,
@dteLocationLicenseIssueDate SmallDateTime,
...fields...more fields...
AS
BEGIN TRANSACTION
INSERT INTO DealerLocations
(....fields....more fields..., [LocationLicenseEstDate], [LocationLicenseIssueDate],...etc)
VALUES
(......@dteLocationLicenseEstDate, @dteLocationLicenseIssueDate, ...)
When I test the SP and key in test values into the "Execute Stored Procedure" Window, it fails and informs me of a Syntax error.
Incorrect syntax near '-'.
or
Incorrect syntax near '/'.
It is referencing the test value/date I key in. Whether it's 5/28/2013 or 5-28-2013.
I know dates should be enclosed in a single set of quotes but I thought by defiining the parameter as a Date time, it would not be needed.
Any help? Suggestions? Thanks again!!
May 28, 2013 at 7:55 am
I was able to make it work by keying in for example, "20130528". Thanks for looking though.
Any other pointers or tips to practice by would be appreciated.
May 28, 2013 at 8:01 am
The values you enter will be passed to the procedure as you enter them and they need to be enclosed in single quotes in the EXECUTE statement. Once they're received by the procedure, the local variables will contain the values themselves and you won't have to mess with the quotes. They're required to specify the values passed.
Did I explain this well at all?
May 28, 2013 at 8:06 am
Sure. I would have expected SQL Server to do this when testing - especially when I defined the param as a Date type.
When I right click on the SP in SSMS, I can select "Execute SP" and start entering in test values.
I suppose I could have entered:
'05/1/2013'
Single quotes and all....let me try that as well.....
May 28, 2013 at 8:23 am
RedBirdOBX (5/28/2013)
Sure. I would have expected SQL Server to do this when testing - especially when I defined the param as a Date type.When I right click on the SP in SSMS, I can select "Execute SP" and start entering in test values.
I suppose I could have entered:
'05/1/2013'
Single quotes and all....let me try that as well.....
One more thing regarding dates. You really should use this format: '20130501'. The reason is that the example date will always be interpreted as May 1, 2013 regardless of language or date format setting.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply