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

Create indexes - own File Group

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
"rcamarda" <robert.a.camarda@.gmail.comwrote in message
news:1173725223.934126.197140@.30g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

With help of others on this group, I've been learning and researching
about indexes; an area I neglected.
I see I can specify which filegroup I wish to create an index, which
the default is Primary.
I have more than one drive in my SQL server where I put data and logs
on their own logical raid groups.
My databases are SIMPLE, so they dont use much, if any logs (none as I
understand).
I was thinking of adding an additional file to my database and use it
solely for the indexes.
Any thoughts?
>

Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.

HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.

Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||Greg,
Its a data warehouse. I have tools that could rebuild an empty
database pretty quickly (few hours) and I have daily backups. Builds
take about 2 hours, then its read only the rest of the day.
I use enterprise because of our licensing.

Thanks for the info!

Quote:

Originally Posted by

>
Yes, I've done this and putting your non-clustered indexes in its own group
can provide an improvement. Especially for rebuilds and the like.
>
HOWEVER, your database most definitely DOES use the logs. SIMPLE simply
means that as soon as transactions are complete, the log is truncated so it
won't grow. But it is definitely being used.
>
Keep in mind that w/o logs, you seriously hamper your disaster recovery
options. Since you're running SQL Server Enterprise, that makes me suspect
this is more than just a little test site.
>

Quote:

Originally Posted by

SQL Server 2005 Enterprise x64 SP2
8 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.
Thanks,
Rob

>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com