Databas: SQL 2000
Plattform: ASP.NET
Språk: VB

Detta känns lite som fel forum, men jag provar ändå :-)

Jag försöker köra följande procedur:
<code>
CREATE PROCEDURE spUpdateUserDetailsByAdmin

@UserName varchar(255),
@Password varchar(255),
@UserIsActive int,
@FirstName varchar(255),
@LastName varchar(255),
@Address varchar(255),
@Zip varchar(255),
@City varchar(255),
@WorkPhone varchar(255),
@Phone varchar(255),
@WorkMobile varchar(255),
@Mobile varchar(255),
@WorkFax varchar(255),
@Fax varchar(255),
@WorkEmail varchar(255),
@Email varchar(255),
@AccountUpdateDate date,
@UserID int,
@AutoLogin int,
@UserLevel int

AS

UPDATE tblUsers
INNER JOIN tblUserDetails
ON tblUsers.pkUserID = tblUserDetails.fkUserID
SET tblUsers.UserName = @UserName, tblUsers.[Password] = @Password, tblUserDetails.UserIsActive = @UserIsActive,
tblUserDetails.FirstName = @FirstName, tblUserDetails.LastName = @LastName], tblUserDetails.Address = @Address,
tblUserDetails.Zip = @Zip, tblUserDetails.City = @City, tblUserDetails.WorkPhone = @WorkPhone, tblUserDetails.Phone = @Phone,
tblUserDetails.WorkMobile = @WorkMobile, tblUserDetails.Mobile = @Mobile, tblUserDetails.WorkFax = @WorkFax, tblUserDetails.Fax = @Fax,
tblUserDetails.WorkEmail = @WorkEmail, tblUserDetails.Email = @Email, tblUserDetails.AccountUpdatedDate = @AccountUpdateDate, tblUserDetails.UpdatedByID = @UserID,
tblUserDetails.AutoLogin = @AutoLogin, tblUserDetails.fkUserLevel = @UserLevel
WHERE tblUserDetails.fkUserID=@UserID

RETURN
GO
</code>

Jag får följande felmeddelande:

Error 156: Incorrect syntax near the keyword 'INNER'

kan du inte köra det som två separata update satser då? kanske lite ruttet prestanda mässigt vad vet jag men det borde ju funka iaf ;-)

<code>
CREATE PROCEDURE ......

AS

UPDATE tblUser SET UserName = @User...... WHERE pkUser = @UserID

UPDATE tblUserDetails SET UserIsActive = @User... WHERE fkUserID = @UserID
</code>

Har för mig att man skrev:

<code>
UPDATE tblUsers
SET tblUsers.UserName = @UserName, tblUsers.[Password] = @Password, tblUserDetails.UserIsActive = @UserIsActive,
tblUserDetails.FirstName = @FirstName, tblUserDetails.LastName = @LastName], tblUserDetails.Address = @Address,
tblUserDetails.Zip = @Zip, tblUserDetails.City = @City, tblUserDetails.WorkPhone = @WorkPhone, tblUserDetails.Phone = @Phone,
tblUserDetails.WorkMobile = @WorkMobile, tblUserDetails.Mobile = @Mobile, tblUserDetails.WorkFax = @WorkFax, tblUserDetails.Fax = @Fax,
tblUserDetails.WorkEmail = @WorkEmail, tblUserDetails.Email = @Email, tblUserDetails.AccountUpdatedDate = @AccountUpdateDate, tblUserDetails.UpdatedByID = @UserID,
tblUserDetails.AutoLogin = @AutoLogin, tblUserDetails.fkUserLevel = @UserLevel
FROM tblUsers INNER JOIN tblUserDetails ON tblUsers.pkUserID = tblUserDetails.fkUserID
WHERE tblUserDetails.fkUserID=@UserID
</code>
Eller:
<code>
UPDATE tblUsers
SET tblUsers.UserName = @UserName, tblUsers.[Password] = @Password, tblUserDetails.UserIsActive = @UserIsActive,
tblUserDetails.FirstName = @FirstName, tblUserDetails.LastName = @LastName], tblUserDetails.Address = @Address,
tblUserDetails.Zip = @Zip, tblUserDetails.City = @City, tblUserDetails.WorkPhone = @WorkPhone, tblUserDetails.Phone = @Phone,
tblUserDetails.WorkMobile = @WorkMobile, tblUserDetails.Mobile = @Mobile, tblUserDetails.WorkFax = @WorkFax, tblUserDetails.Fax = @Fax,
tblUserDetails.WorkEmail = @WorkEmail, tblUserDetails.Email = @Email, tblUserDetails.AccountUpdatedDate = @AccountUpdateDate, tblUserDetails.UpdatedByID = @UserID,
tblUserDetails.AutoLogin = @AutoLogin, tblUserDetails.fkUserLevel = @UserLevel
INNER JOIN tblUserDetails ON tblUsers.pkUserID = tblUserDetails.fkUserID
WHERE tblUserDetails.fkUserID=@UserID
</code>
Eller Varför inte göra två uppdateringar?
<code>
UPDATE tblUsers SET tblUsers.UserName = @UserName, tblUsers.[Password] = @Password
WHERE tblUsers.pkUserID=@UserID
</code>
UPDATE tblUserDetails SET tblUserDetails.UserIsActive = @UserIsActive,
tblUserDetails.FirstName = @FirstName, tblUserDetails.LastName = @LastName], tblUserDetails.Address = @Address,
tblUserDetails.Zip = @Zip, tblUserDetails.City = @City, tblUserDetails.WorkPhone = @WorkPhone, tblUserDetails.Phone = @Phone,
tblUserDetails.WorkMobile = @WorkMobile, tblUserDetails.Mobile = @Mobile, tblUserDetails.WorkFax = @WorkFax, tblUserDetails.Fax = @Fax,
tblUserDetails.WorkEmail = @WorkEmail, tblUserDetails.Email = @Email, tblUserDetails.AccountUpdatedDate = @AccountUpdateDate, tblUserDetails.UpdatedByID = @UserID,
tblUserDetails.AutoLogin = @AutoLogin, tblUserDetails.fkUserLevel = @UserLevel
WHERE tblUserDetails.fkUserID=@UserID
</code>
Varför har du två tabeller? Får informationen inte plats i tblUsers?

Nja, jag har get mig F*N på att få detta att fungera. Jag är inte så hemma i procedurvärlden så jag vill lära mig av mina misstag. Tack i alla fall!

Dina två förslag fungerade inte :-(

Det blir kanske till att köra dubbeluppdatering eller bygga ut tabellen tblUserDetails.

Kollat upp i MSDN nu.

Transact-SQL Reference: UPDATE
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_82n9.asp

Man kan inte uppdater två tabeller utan endast en av tabellerna i joinen.

Exempel:

UPDATE titles SET ytd_sales = t.ytd_sales + s.qty
FROM titles t INNER JOIN sales s ON t.title_id = s.title_id
WHERE s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

Tack för hjälpen!