To update a table row depending on user selections

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

To update a table row depending on user selections

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-05-04 : 11:36:26

I have a stored procedure that updates a table row, but in the application user can choose some options or not, in case the user doesn' t select the fields (User_Type, City_num and Activity_num ) the SP can' t make the update because these three fields doesn' t accept '0' values, because they have foreign keys. The '0' value is returned by the application when there is no item selected, and once into the DB, these fields only accept null value or one valid value addording to the foreign key. So, I want the DB table only updates these fields if they are different to '0'. How can I do this?
It seems very difficult to me because it has many possible combinations, the user can choose:

1. None of the three
2. User_Type
3. User_Type and City_num
4. User_Type, City_num and Activity_num
5. City_num
6. City_num and User_Type
7. City_num, User_Type and Activity_num
8. Activity_num
9. Activity_num and User_Type
10. Activity_num, User_Type and City_num

The SP now:


USE market1
GO
CREATE PROC Form2_NewUser
@User_id bigint, @User_Name varchar(100), @User_Type smallint, @City_num int,
@Address varchar(100), @Activity_num smallint

AS
 
SET NOCOUNT ON

	UPDATE Users 
        SET User_Name = @User_Name , User_Type = @User_Type, City_num =  @City_num, Address = @Address, Activity_num =  @Activity_num 

        WHERE User_id = @User_id        
        
SET NOCOUNT OFF
GO

Thank you,
Cesar

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-04 : 11:54:26

Maybe this:
--*********************
UPDATE Users
SET User_Name = @User_Name , User_Type = case when @User_Type = 0 then User_type else @User_type end , City_num = case when @City_num = 0 then City_Num else @City_Num end , Address = @Address, Activity_num = case when @Activity_num = 0 then Activity_num else @Activity_Num end

WHERE User_id = @User_id

Duane.

cesark
Posting Yak Master

215 Posts

Posted - 2004-05-04 : 13:53:51

It works, but I don' t understand the code. Would you mind explaining me what the SP makes more or less in every line?

This is the code that works:


USE market1
GO
CREATE PROC Form2_NewUser
@User_id bigint, @User_Name varchar(100), @User_Type smallint, @City_num int,
@Address varchar(100), @Activity_num smallint

AS
 
SET NOCOUNT ON

	UPDATE Users
        SET User_Name = @User_Name, User_Type = case
        when @User_Type = 0 then User_Type  
        else @User_Type 
        end,
        City_num = case
        when @City_num = 0 then City_num
        else @City_num  
        end,
        Address = @Address, Activity_num = case 
        when @Activity_num = 0 then Activity_num 
        else @Activity_num 
        end
  
        WHERE User_id = @User_id        
        
SET NOCOUNT OFF
GO

Thank you!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-05 : 02:09:41

Hi,
basically what is happening is the code checks each of the 3 parameters for a value of 0.
If the parameter being checked is = 0 then it updates the value of that field to the value that it already has (in other words the value does not change), otherwise it updates the value of the field to the non-zero value of the parameter.

I hope this makes sense.

I made use of the "CASE" Statement to perform the checks.
Look up CASE in Books On Line

Duane.

cesark
Posting Yak Master

215 Posts

Posted - 2004-05-05 : 05:35:49

Good

, thank you very much Duane!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources