moderate sql procedure question

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

jagdipa wrote:

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

hi jagdipa,
we're on the same situation on that one, I've decided since then to use ado.net to update optional data, and use only stored procs if the data to be update/inserted is consistent; meaning it updates all column and not a few...

|||Once approach you could try is this:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50) = NULL,
@.new_V_Address1 varchar(50) = NULL,
@.new_V_Address2 varchar(50) = NULL,
@.new_V_Address3 varchar(50) = NULL,
@.new_V_TownCity varchar(50) = NULL,
@.new_I_RegionID bigint = NULL,
@.new_V_Postcode varchar(10) = NULL
)
AS
UPDATE T_Sites SET
[V_SiteName] = ISNULL(@.new_V_SiteName,[V_SiteName]),
[V_Address1] = ISNULL(@.new_V_Address1,[V_Address1]),
[V_Address2] = ISNULL(@.new_V_Address2,[V_Address2]),
[V_Address3] = ISNULL(@.new_V_Address3,[V_Address3]),
[V_TownCity] = ISNULL(@.new_V_TownCity,[V_TownCity]),
[I_RegionID] = ISNULL(@.new_I_RegionID,[I_RegionID]),
[V_PostCode] = ISNULL(@.new_V_Postcode,[V_PostCode])
WHERE
I_SiteID= @.old_I_SiteID

GO

|||good one terry, i've used default values before but never thought on this implementation.. sweet..|||

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

|||

meantown2 wrote:

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

hi meantown, the poster wants to update only a selected column, if he/she is going to pass all values he might update columns he doesn't want to update. The reply of terry will prevent him/her from doing that, passing only selected parameters.

|||The reason I asked this was not really for this stored procedure (I just used this one for an easy example).

I have a B2B website where there are a lot of text fields on one webform. The data entered could grow. To try to improve effieciecy, I wanted to only pass the values that have changed.

This does mean a little extra work on the webform though. I will have to go through each value and check if it has changed.

So that leads me to my next question (this is a hard question to put down in writing, but I've tried my best to put my point across):

Lets say I have loaded the old values into a webform.
Then the user changes some of these and hits Save.
Now I need to check whether the values have changed. I could do this by comparing the values against the ones in the database - but this means an extra database access to retrieve the old values. Is there a way I can use the viewstate and somehow compare the values entered by the user against the ones stored in the viewstate?

Note: I dont want to put a load of invisible input fields everywhere and save the old values in that.

Thanks in advance for any help.

Jagdip