CODEWITHSUNDEEP
sql-servert-sqlsql
Serenity
Serenity

Reputation: 5098

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 'UserAddressID'

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

Upvotes: 0

Views: 1913

Answers (3)

Sir Wobin
Sir Wobin

Reputation: 1140

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.

Upvotes: 0

pavanred
pavanred

Reputation: 13803

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838216

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)

Upvotes: 2

Related Questions