SQL Server error: Invalid column name

when some link "Make Default" is clicked the following SP is executed. What I want is that when the link "Make Default " is clicked then only the "IsDefault" record corresponding to the particular "UserAddressID" is set to 1 and ALL the rest of records to be set to 0 in the teable's column

giving me the following error:-

Invalid column name 'UserAddres开发者_JAVA百科sID'

When the column exists! Whats wrong with my SP??

---

The problem is here:

if(UserAddressID!=@UserAddressID)

The column UserAddressID does not exist outside the scope of the SQL statement.

In fact you don't need the if statement at all. The condition you are checking for is already included in the SQL.

You can also combine both queries into a single update:

UPDATE SC.UserAddressDetails
SET IsDefault = (CASE WHEN UserAddressID=@UserAddressID THEN 1 ELSE 0 END)

---

You can update the IsDefault value to 1 using -

UPDATE SC.UserAddressDetails SET IsDefault=1 WHERE UserAddressID=@UserAddressID

In your table structure you can set the default value for ISDefault as 0. In which case you wont need to update all the records again using

ALTER TABLE SC.UserAddressDetails ADD CONSTRAINT default_isdefault DEFAULT 0 FOR IsDefault 

---

This sounds like a schema problem which is why your stored procs are getting confusing. Make a column called DefaultUserAddressID in your Users table, . Then there can only be one. Making an IsDefault column on the Addresses table isn't very good design since it uses more data to achieve the same thing, is error prone and requires you to make all sorts of other checks in all stored procs that update address information.

Simple is better.