June 22, 2016 at 5:43 am
Hi All,
I should start by saying that at this time there is no User Interface for this database, it is currently under development. This is just a stop gap process to allow some advanced users that capability to update the data while waiting on the UI. Though these SPs might be used by the UI in the future.
I have been asked to create stored procedures that will update data in a table. While I have no problem creating an SP and an UPDATE statement I am having trouble coming up with how to do the SET commands.
The reason for the trouble is that my initial plan had been to have a parameter for the Primary Key and one for each field, the latter being optional, e.g. if a table has 5 fields but only needed the 4th field needed updating the 4th field, I would pass NULL for the the other field. Of course this has the following challenges:
1. If the SP only received a values for the 4th field or the 2nd and 3rd how to you create the UPDATE SET... command
Note: I would like to avoid dynamic SQL because if I understand correctly once I use that I need to give UPDATE Permission for the TABLE to the user, which I don't want to do, I only wanted to provide EXECUTE permission to the SP
2. How do I deal with NULLable field. How do I know if a user passed in the NULL values to update the field or ignore it
I need to provide this for approx 30 tables, so I am looking for a design pattern I suppose on how this is usually handled. I assume it is that is done by other and hope I am just missing something obvious/fundamental.
As a side note, I also know a bit about SSIS which is available so if there is a package pattern which will perform updates from a spreadsheet I would be happy to do it that way. But again I would not want to require the user to populate the spreadsheet with all existing data and along with any changes... though if that is the only way
Thanks
Steve
June 22, 2016 at 5:54 am
You would have to check the input parameters for NULLs and then skip those. That might mean that you have a zillion different updates (one for each parameter), or you use dynamic SQL.
June 22, 2016 at 6:19 am
Can you provide brief table structures with sample data to better help you?
I think its possible for you even though the UI interface not yet ready.
June 22, 2016 at 6:20 am
Hi pietlinden,
Thanks for the quick response.
So I guess what your saying is there is no way to do what I want.
Disappointing to say the least. I don't think the DBA's are going to let me give UPDATE TABLE permission to these users, rightly so I think, so Dynamic is more than likely out of the question.
That might mean that you have a zillion different updates
When you say that did you mean have a single update for each parameter, for instance
IF Parm1 IS NOT NULL
UPDATE SET Field1 = PARM1
END
IF Parm2 IS NOT NULL
UPDATE SET Field2 = PARM2
END
or multiple updates that handle all combinations:
IF Parm1 IS NOT NULL AND Parm2 IS NULL
UPDATE SET Field1 = PARM1
END
IF Parm2 IS NOT NULL AND Parm1 IS NULL
UPDATE SET Field2 = PARM2
END
IF Parm1 IS NOT NULL AND Parm2 IS NOT NULL
UPDATE SET Field1 = PARM1, Field2 = PARM2
END
Don't think that is practical for the bigger tables.
I guess when a database has a UI, the UI would be collecting all field values data in the record (via a ADO Recordset oor something) and then it just passes all field values back with some of them being changed and an UPDATE statement occurs with all fields listed in the SET clause
Thanks
Steve
June 22, 2016 at 6:23 am
For option 1:
DECLARE @Param1 varchar(10) = NULL;
UPDATE dbo.table
SET Column1 = ISNULL(@Param1,Column1);
So if @Param1 is null, it will use the existing value, so no change to the contents of that column.
For option 2, you might need something that specifies that column should be nulled - a boolean parameter possibly.
June 22, 2016 at 6:25 am
Steve
Try something like this, but bear in mind:
(1) I don't recommend you use this in your application - just for the stopgap period
(2) I haven't included any WHERE clause logic, so this is going to update every row in your table
(3) You'll need one of these procedures for each table that you want users to be able to update
(4) Make sure the default values in the parameter list are values that will never actually be used in your table
CREATE PROC UpdateMyTable (
@param1 int = -9999999
,@param2 varchar(100) = '~??'
,@param3 datetime = '9999-12-31'
)
AS
UPDATE MyTable
SET
col1 =CASE @param1
WHEN -9999999 THEN col1
ELSE @param1
END
,col2 = CASE @param2
WHEN '~??' THEN col2
ELSE @param2
END
,col3 = CASE @param3
WHEN '9999-12-31' THEN col3
ELSE @param3
END
John
Edit - corrected column names in code
June 22, 2016 at 6:27 am
Hi durga.palepu
Thanks for the quick response.
For sample data, so keep things simple, assume the following structure
CREATE Table UpdateTest (ID INTEGER , Field1 CHAR(5) NULL, Field2 CHAR(5) NOT NULL)
INSERT dbo.UpdateTest VALUES ( 1, 'F1V1', 'F2V1' )
INSERT dbo.UpdateTest VALUES ( 2, NULL, 'F2V2' )
INSERT dbo.UpdateTest VALUES ( 3, 'F1V3', 'F2V3' )
INSERT dbo.UpdateTest VALUES ( 4, 'F1V4', 'F2V4' )
I need Stored Procedure that would allow me to the following changes:
- ID = 1, Field1 = 'New Value'
- ID = 2, Field2 = 'New Value'
- ID = 3, Field1 = 'New Value', Field2 = 'New Value'
- ID = 4, Field1 = NULL, Field2 = 'New Value'
Hopefully that makes sense.
Thanks
Steve
June 22, 2016 at 6:32 am
Option1 is will impact the performance severely as database will be hit for n times for n parameters.
June 22, 2016 at 6:39 am
Hi John, BrainDonor
I had obviously missed the the whole SET Col1 = Col1 part, thanks very much for that.
As for the default being a value never used that is quite possible while still letting users pass through NULL to clear out a nullable.
Thanks I think this gives me something to work with for now
Steve
June 22, 2016 at 6:54 am
Hi Steve,
Thanks for providing the sample data.
Here is the solution which would work for you.
CREATE PROC uspupdatetest
(@id int =null
, @param1 CHAR(5) = null
, @param2 CHAR(5) =null
)
as
begin
set nocount on;
set xact_abort on;
update dbo.UpdateTest
set
Field1 = case
when @param1 IS null then Field1
else@param1
end
,Field2 = case
when @param2 IS null then Field2
else@param2
end
where id = @id
end;
exec uspupdatetest 1, 'r1cl1', NULL;
exec uspupdatetest 2,NULL, 'r2cl2';
exec uspupdatetest 3, 'r3cl1', 'r3cl2';
exec uspupdatetest 4, NULL, 'r4cl2';
Output:
IDField1Field2
1r1cl1 F2V1
2NULL r2cl2
3r3cl1 r3cl2
4F1V4 r4cl2
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply