Create Procedure Error?

Hello All,
I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
Access to SQL Server" published by Apress. Unfortunately the code for
the chapter is not included in the download file from
http://www.apress.com/book/suppleme...?bID=70&sID=309 as
stated in the second paragraph of chapter 16. I've attempted to contact
the publisher without luck. I am attempting to create the procedure in
the box quote below. Unfortunately when attempting to save the
procedure I receive an error message that reads "ADO error: Incorrect
syntax near the keyword 'IF'".
I believe I've found one of the problems which was a space between '@.'
and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
correction eliminated several other errors which left me with the above
error message. I'm searching through 'Books On-Line' per
http://msdn.microsoft.com/library/d...r />
_4fht.asp
without luck. I've found a few other errors in the books code which
also relates to the 'IF' statement. It could be that I'm using SP4
while the book was probably written with the first release. I don't
know, but I'm just guessing.
Any ideals? Thanks!
,-- [ ]
| CREATE PROCEDURE dbo.usp_InsertProd
| (@.ProductName [varchar](40), @.Unit_Price[Money])
| AS
| BEGIN
| If @.Unit_Price > 100
| RAISERROR(50001,16,1)
| ELSE
| BEGIN
| BEGIN TRANSACTION
| INSERT INTO dbo.Products
| (ProductName, UnitPrice)
| IF @.@.ERROR <> 0
| Rollback TRANSACTION
| ELSE
| COMMIT TRANSACTION
| END
| END
`--
Regards,
Greg StrongThe syntax problem is that the INSERT
| INSERT INTO dbo.Products
| (ProductName, UnitPrice)
has neither a VALUES clause nor a SELECT to provide WHAT is being
inserted.
I expect it should read:
INSERT INTO dbo.Products
(ProductName, UnitPrice)
(@.ProductName, @.Unit_Price)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 20:08:53 GMT, Greg Strong <NoJunk@.NoJunk4U.com>
wrote:

>Hello All,
>I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
>Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
>Access to SQL Server" published by Apress. Unfortunately the code for
>the chapter is not included in the download file from
>http://www.apress.com/book/suppleme...?bID=70&sID=309 as
>stated in the second paragraph of chapter 16. I've attempted to contact
>the publisher without luck. I am attempting to create the procedure in
>the box quote below. Unfortunately when attempting to save the
>procedure I receive an error message that reads "ADO error: Incorrect
>syntax near the keyword 'IF'".
>I believe I've found one of the problems which was a space between '@.'
>and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
>correction eliminated several other errors which left me with the above
>error message. I'm searching through 'Books On-Line' per
>http://msdn.microsoft.com/library/d... />
t_4fht.asp
>without luck. I've found a few other errors in the books code which
>also relates to the 'IF' statement. It could be that I'm using SP4
>while the book was probably written with the first release. I don't
>know, but I'm just guessing.
>Any ideals? Thanks!
>,-- [ ]
>| CREATE PROCEDURE dbo.usp_InsertProd
>| (@.ProductName [varchar](40), @.Unit_Price[Money])
>| AS
>| BEGIN
>| If @.Unit_Price > 100
>| RAISERROR(50001,16,1)
>| ELSE
>| BEGIN
>| BEGIN TRANSACTION
>| INSERT INTO dbo.Products
>| (ProductName, UnitPrice)
>| IF @.@.ERROR <> 0
>| Rollback TRANSACTION
>| ELSE
>| COMMIT TRANSACTION
>| END
>| END
>`--|||On Wed, 19 Apr 2006 20:08:53 GMT, Greg Strong wrote:
(snip)
> I am attempting to create the procedure in
>the box quote below. Unfortunately when attempting to save the
>procedure I receive an error message that reads "ADO error: Incorrect
>syntax near the keyword 'IF'".
Hi Greg,
I see several problems in the stored procedure. Comments inline.

>,-- [ ]
>| CREATE PROCEDURE dbo.usp_InsertProd
Style preference - I've never seen the use of those silly prefixes.
Surely, if the name comes directly after CREATE PROCEDURE or EXECUTE,
you don't need the usp_ prefix to know it's a stored procedure, do you?

>| (@.ProductName [varchar](40), @.Unit_Price[Money])
Add a space between "@.Unit_Price" and "[Money]". Maybe not an error, but
definitely confusing!
Also, no need to escape the datatypes with brackets. And using lower
case on one datatype and mixed case on another is confusing too. Same
goes for using PascalCase for one variable name and Under_Scores for the
other - choose one style and stick to it!

>| AS
>| BEGIN
This BEGIN (and the corresponding END) is not strictly necessary. But it
doesn't hurt either.

>| If @.Unit_Price > 100
>| RAISERROR(50001,16,1)
A single statement in an IF or ELSE clause is permitted, but can be
confusing. I tend to prefer to always use a BEGIN / END block, unless
BOTH branches of the IF statement are one statement only (like the IF
@.@.ERROR <> 0 below)

>| ELSE
>| BEGIN
>| BEGIN TRANSACTION
>| INSERT INTO dbo.Products
>| (ProductName, UnitPrice)
Here's the source of your error. This needs either a VALUES or a SELECT
clause to become a complete statement. In this case, I'd hazard a guess
and use VALUES.

>| IF @.@.ERROR <> 0
>| Rollback TRANSACTION
>| ELSE
>| COMMIT TRANSACTION
>| END
>| END
>`--
Finally, the formatting is very bad. Matching BEGIN and END statements
should line up; statements between BEGIN and END should be indented by
the same amount.
Here's how I would write it:
CREATE PROCEDURE dbo.InsertProd
(@.ProductName varchar(40),
@.UnitPrice money)
AS
BEGIN
IF @.UnitPrice < 100
BEGIN
RAISERROR (50001, 16, 1)
END
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO dbo.Products (ProductName, UnitPrice)
VALUES (@.ProductName, @.UnitPrice)
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END
END
Hugo Kornelis, SQL Server MVP|||On Wed, 19 Apr 2006 17:42:20 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>has neither a VALUES clause nor a SELECT to provide WHAT is being
>inserted.
>I expect it should read:
>INSERT INTO dbo.Products
>(ProductName, UnitPrice)
>(@.ProductName, @.Unit_Price)
Yes, I see what your saying. So shouldn't it really be:
INSERT INTO dbo.Products (ProductName, UnitPrice)
VALUES (@.ProductName, @.Unit_Price)
Unless I'm missing something here, I believe so. Anyhow I've changed
the procedure to what is in the box quote below. On save I now receive
the following error:
ADO error: Must declare the variable ".
Incorrect syntax near the keyword 'ELSE'
I'm not sure, but I believe the second line may have something to do
with the number of 'BEGIN' and 'END' keywords. There are 4 'BEGIN'
keywords, and only 3 'END' keywords. I've made some changes, but no
luck. I do not have a clue as to why the first line exists in the error
message.
Any ideals? Thanks!
,-- [ ]
| CREATE PROCEDURE dbo.usp_InsertProd
| (@.ProductName [varchar](40), @.Unit_Price[Money])
| AS
| BEGIN
| If @. Unit_Price > 100
| BEGIN
| RAISERROR(50001,16,1)
| END
| ELSE
| BEGIN
| BEGIN TRANSACTION
| INSERT INTO dbo.Products (Pr
oductName, UnitPrice)
| VALUES (@.ProductName, @.Unit_
Price)
| IF @.@.ERROR <> 0
| Rollback TRANSACTION
| ELSE
| COMMIT TRANSACTION
| END
| END
`--
Regards,
Greg Strong|||On Wed, 19 Apr 2006 22:29:21 GMT, Greg Strong wrote:
(snip)
> Anyhow I've changed
>the procedure to what is in the box quote below. On save I now receive
>the following error:
>ADO error: Must declare the variable ".
>Incorrect syntax near the keyword 'ELSE'
(snip)
>| If @. Unit_Price > 100
Hi Greg,
There's a space between @. and Unit_Price. Remove it.

>I'm not sure, but I believe the second line may have something to do
>with the number of 'BEGIN' and 'END' keywords. There are 4 'BEGIN'
>keywords, and only 3 'END' keywords.
Wrong. There are three BEGIN and three END keywords.
BEGIN TRANSACTION is not a BEGIN keyword. BEGIN TRANSACTION starts a
transaction, which is later ended with either COMMIT TRANSACTION or
ROLLBACK TRANSACTION.
BEGIN is the start of a block of statements that can be used where the
syntax allows a single statement; the block should be ended with END.
Hugo Kornelis, SQL Server MVP|||If Seems that th e"INSERT INTO..." is not completed. It should be:
BEGIN
If @.Unit_Price > 100
RAISERROR(50001,16,1)
ELSE
BEGIN
BEGIN TRANSACTION
INSERT INTO dbo.Products
(ProductName, UnitPrice)
/*-- You missed following part--*/
VALUES
(@.ProductName,@.Unit_Price)
IF @.@.ERROR <> 0
Rollback TRANSACTION
ELSE
COMMIT TRANSACTION
END
END
"Greg Strong" <NoJunk@.NoJunk4U.com> wrote in message
news:du4d425nta4uguv347e55mt6tve8kfh3lh@.
4ax.com...
> Hello All,
> I'm in the process of learning T-SQL while using MSDE 2000 SP4 with
> Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL:
> Access to SQL Server" published by Apress. Unfortunately the code for
> the chapter is not included in the download file from
> http://www.apress.com/book/suppleme...?bID=70&sID=309 as
> stated in the second paragraph of chapter 16. I've attempted to contact
> the publisher without luck. I am attempting to create the procedure in
> the box quote below. Unfortunately when attempting to save the
> procedure I receive an error message that reads "ADO error: Incorrect
> syntax near the keyword 'IF'".
> I believe I've found one of the problems which was a space between '@.'
> and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
> correction eliminated several other errors which left me with the above
> error message. I'm searching through 'Books On-Line' per
> http://msdn.microsoft.com/library/d.../>
rt_4fht.asp
> without luck. I've found a few other errors in the books code which
> also relates to the 'IF' statement. It could be that I'm using SP4
> while the book was probably written with the first release. I don't
> know, but I'm just guessing.
> Any ideals? Thanks!
> ,-- [ ]
> | CREATE PROCEDURE dbo.usp_InsertProd
> | (@.ProductName [varchar](40), @.Unit_Price[Money])
> | AS
> | BEGIN
> | If @.Unit_Price > 100
> | RAISERROR(50001,16,1)
> | ELSE
> | BEGIN
> | BEGIN TRANSACTION
> | INSERT INTO dbo.Products
> | (ProductName, UnitPrice)
> | IF @.@.ERROR <> 0
> | Rollback TRANSACTION
> | ELSE
> | COMMIT TRANSACTION
> | END
> | END
> `--
>
> --
> Regards,
> Greg Strong|||On Thu, 20 Apr 2006 00:24:42 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>Finally, the formatting is very bad. Matching BEGIN and END statements
>should line up; statements between BEGIN and END should be indented by
>the same amount.
Yes, I agree.

>Here's how I would write it:
Thanks! Yours is much easier to follow. Thanks again!!!
Regards,
Greg Strong|||On Thu, 20 Apr 2006 00:52:08 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>Wrong. There are three BEGIN and three END keywords.
>BEGIN TRANSACTION is not a BEGIN keyword. BEGIN TRANSACTION starts a
>transaction, which is later ended with either COMMIT TRANSACTION or
>ROLLBACK TRANSACTION.
Thanks for the clarification!
Regards,
Greg Strong|||On Wed, 19 Apr 2006 17:17:57 -0700, "Norman Yuan" <NotReal@.NotReal.not>
wrote:

>If Seems that th e"INSERT INTO..." is not completed.
Thanks to all for your comments!
Regards,
Greg Strong|||hi Greg,
Greg Strong wrote:
>...
> Unfortunately when attempting to
> save the procedure I receive an error message that reads "ADO error:
> Incorrect syntax near the keyword 'IF'".
> I believe I've found one of the problems which was a space between '@.'
> and 'Unit_Price' on the 1st 'If' statement. If I'm correct, this
> correction eliminated several other errors which left me with the
> above error message. I'm searching through 'Books On-Line' per
> http://msdn.microsoft.com/library/d.../>
rt_4fht.asp
> without luck. I've found a few other errors in the books code which
> also relates to the 'IF' statement. It could be that I'm using SP4
> while the book was probably written with the first release. I don't
> know, but I'm just guessing.
the exception is raised becouse of this line,
INSERT INTO dbo.Products
(ProductName, UnitPrice) IF
as the INSERT INTO syntax requires you to provide the VALUES
(param_or_constant, ...)
column names are not mandatory, but values to be inserted of course are...
so modify it as following
-- USE tempdb;
-- GO
CREATE PROCEDURE dbo.usp_InsertProd (
@.ProductName [varchar](40),
@.Unit_Price [Money]
)
AS
BEGIN
IF @.Unit_Price > 100
RAISERROR(50001,16,1);
ELSE BEGIN
BEGIN TRANSACTION;
INSERT INTO dbo.Products
(ProductName, UnitPrice) VALUES (@.ProductName, @.Unit_Price);
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION;
ELSE
COMMIT TRANSACTION;
END;
END;
GO
-- DROP PROCEDURE dbo.usp_InsertProd;
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply