How do I get this to execute in SQL without Errors?

[ASPNETnewbie]

I can't seem to execute the code below with my SQL compiler and I was wondering if anyone can help me understand what is wrong with this and How I can go about fixing the problem.

The Main error I keep getting is with the GO keyword


Error Source: .Net sqlClient Data Provider
Error Message: Incorrect Syntax near 'GO'
Must declare the scalar variable "@Name"
Incorrect syntax near the keyword 'PROCEDURE"
Incorrect Syntax near 'GO'
Must declare the scalar variable "@Name"
Must declare the scalar variable "@Address1"
Must Declare the scalar variable"@CreditCard"


===========================================================
SQL CODE
===========================================================
CREATE PROCEDURE GetCustomerIDPassword
(@Email varchar(50))
AS

SELECT CustomerID, [Password]
FROM Customer
WHERE Email = @Email

GO

CREATE PROCEDURE AddCustomer
(@Name varchar(50),
@Email varchar(50),
@Password varchar(50),
@Phone varchar(100))
AS

DECLARE @CustomerID AS int

INSERT INTO Customer ([Name], Email, [Password], Phone)
VALUES (@Name, @Email, @Password, @Phone)
SET @CustomerID = @@IDENTITY

SELECT @CustomerID

GO

CREATE PROCEDURE GetCustomer
(@CustomerID varchar(50))
AS

SELECT CustomerID, [Name], [Password], Email, CreditCard, Address1, Address2, City,
Region, PostalCode, Country, Phone
FROM Customer
WHERE CustomerID = @CustomerID

GO

CREATE PROCEDURE UpdateCustomerDetails
(@CustomerID int,
@Name varchar(50),
@Email varchar(50),
@Password varchar(50),
@Phone varchar(100))

AS
UPDATE Customer
SET [Name] = @Name, Email = @Email, [Password] = @Password, Phone = [Phone]
WHERE CustomerID = @CustomerID

GO

CREATE PROCEDURE UpdateAddress
(@CustomerID int,
@Address1 varchar(100),
@Address2 varchar(100),
@City varchar(100),
@Region varchar(100),
@PostalCode varchar(100),
@Country varchar(100)
)

AS
UPDATE Customer
SET Address1 = @Address1, Address2 = @Address2, City = @City, Region = @Region,
PostalCode = @PostalCode, Country = @Country
WHERE CustomerID = @CustomerID

GO

CREATE PROCEDURE UpdateCreditCard
(@CustomerID int,
@CreditCard varchar(512))

AS
UPDATE Customer
SET CreditCard = @CreditCard
WHERE CustomerID = @CustomerID

GO

 

[SQLDenis]

you can't have GO inside a proc as a matter of fact your proc has only code until the GO
Open up the proc and look at it
GO is a batch terminator and can not be used inside procedures

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ASPNETnewbie]

Could you please explain what you mean. The code above is actually code taken from a book I am studying with and it has it all written that way with GO in there. If I wanted to rewrite the stored procedure without the Go, how can I do that please?

 

[SQLDenis]

Okay I see what you are doing. You are creating this from ADO.Net?
GO is not a SQL Command
Use Query Analyzer/Sql server Managment Studio to create the procedures

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

All the procs will work if created one at a time in Query analyzer. However, I am concerned about your methodology. Never ever use @@identity as it will not in all circumstances pull the correct value because it pulls the last identity created which means if someone ever puts a trigger on the table which inserts to another table with an identity field that is the identity it will pull not the one just creted in your table. @@identity is a recipe for having data integrity problems later on. Replace it with scope_identity().

And you have a problem with your field names as well. Never use SQL reserved words such as name for field names. This will cause no end of problems for the system. That is why they have to be enclosed in brackets for the code to work. Otherwise it thinks you are using them inthe SQl command sense and this will make your code fail. It is best to always avoid these key words.

Also email fields are often more than 50 characters, if you use a smaller varaible, then your email might not properly match up. And you have customer ID as a varchar in one of the procs but it is an int inthe others. If it isna int always define it as an int.

And be very wary of using code from a book without completely understanding what it does.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

>>All the procs will work if created one at a time in Query analyzer

You should be able to run the whole script int Queary Analyzer, the Go's are after the procs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

That's true, dennis. I just checked the code one at a time, so that's what I wrote.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ASPNETnewbie]

I have a lot of code like this one to run. Are you saying that if I ran it in a query analyzer, I would not need to change anything in the code at all?

 

[SQLDenis]

yes

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/