Correct syntax for an update stored procedure

  • Thread starter Thread starter martinharvey
  • Start date Start date
M

martinharvey

This is probably a very simple question but i would appreciate some help with
the correct syntax for and update stored procedure

I have created a user form that allows the user to update the name and
address fields in a datatable called customers based on the input value
customer ID = ( datatable/Customers)customerID

I have got this far and then got lost:

Create SP_UpdateCustomer
(@customerID, @name, @address)

As

Update customers ( name, address)

Where customerID = @customerID

GO

Could anyone tell me what the correct sntax should be.

many thanks

Martin
 
martinharvey said:
This is probably a very simple question

It is, and has very little to do with ASP.NET -
microsoft.public.sqlserver.programming would have been the correct newsgroup
to post this...
but i would appreciate some help with
the correct syntax for and update stored procedure

A quick search in Books OnLine, or even Google, would have answered this for
you straightaway.
I have created a user form that allows the user to update the name and
address fields in a datatable called customers based on the input value
customer ID = ( datatable/Customers)customerID

I have got this far and then got lost:

Create SP_UpdateCustomer
(@customerID, @name, @address)

As

Update customers ( name, address)

Where customerID = @customerID

GO

Could anyone tell me what the correct sntax should be.

On the assumption that 'customerID' is an int, and that 'name' and 'address'
are varchar(50), the syntax is as follows:

CREATE uspUpdateCustomer
@customerID int,
@name varchar(50),
@address varchar(50)
AS
UPDATE
customers
SET
[name] = @name,
address = @address
WHERE
customerID = @customerID


Also:

1) avoid naming stored procedures SP_xxxxx
http://www.devx.com/tips/Tip/14432

2) avoid using SQL keywords e.g. 'name' as table names or field names
 
Create Procedure SP_UpdateCustomer
(@customerID Int,
@name NVarchar(200),
@address NVarchar(200)
)

As

update customers set name = @name,
address = @address
where customerID = @customerID

GO
 
Martin,

The correct syntax for a simple UPDATE command would be:

Update customers Set name = @name, address = @address
Where customerID = @customerID
 
Back
Top