Dynamically update with stored procedures in SQL Server

Question

We have a table as follows:

CREATE TABLE dbo.Test (
    StartDate DATETIME2 NOT NULL,
    EndDate DATETIME2 NULL
    Field1 NVARCHAR(50) NOT NULL,
    Field2 NVARCHAR(50) NOT NULL,
    etc.
)

If we insert a row the EndDate is always NULL. If it comes to an update we don't really update the existing row but we set the EndDate of it and insert a new one. Assuming there is a stored procedure for insert and update.

DECLARE @newStartDate DATETIME2
SELECT @newStartDate = GETDATE()

UPDATE dbo.Test SET (EndDate = @newStartDate) WHERE StartDate = @someDate

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
     SELECT @newStartDate, NULL, Field1, Field2 
       FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

It can happen that we need to set other start/insert values than the old ones. That can be done as follows.

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
    SELECT @newStartDate, NULL, @parameter, Field2 
      FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

The main problem is now that the real table contains more than 100 columns and the above approach would work but is very error prone.

I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value if it exists.

In C# I would do something like this. existing would be a Dictionary containing every column and overrides are the not default values that needs to be changed.

var existing= ..."SELECT TOP 1 * from dbo.Test"
foreach (var changeParameter in overrides)
     existing[changeParameter.Key] = changeParameter.Value;

In existing are now all values stored as they should be for the insert. Therefore I can build a new insert statement.

Is this approach somehow possible on the database? I don't want to load all data rows just to write it back right away. I would rather do everything directly on the database.

_"I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value"_ - why not just build the insert SQL dynamically? — stuartd, Jun 03 '16 at 11:32
@stuartd Sure, but how can I do that? I wanna build the insert statement on the database. How can I decide whether I can take parameter '@field1' or Field1 from the old row dynamically? — Dani, Jun 03 '16 at 12:02
Am I correct that you want sql script that will have `insert (cols) values (vals)` where cols __list__ and __vals__ list will contain some array of values that was passed from somewhere else? Is this your dynamic part? Long list of columns that may differ? — Ivan Starostin, Jun 03 '16 at 13:48
@IvanStarostin Yes, the list with passed parameter could be long, the main goal is to pick either the value from the parameter, if it's provided, or select it from the previous row. — Dani, Jun 03 '16 at 13:52
@Dani So set of params has constant number of elements but some of them are nulls (and this insert is placed likely within stored proc) **or** _number of elements_ passed varies and `insert` script is an ad-hoc query build on client side? — Ivan Starostin, Jun 03 '16 at 14:13

score 2 · Answer 1 · edited May 23 '17 at 11:45

2

Answering your question :

I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value if it exists. Is this approach somehow possible on the database?

To get T-SQL dynamism in SQL server you will have to use sp_executesql, more information :

Execute Dynamic SQL commands in SQL Server
You will have to pass field parameters as some kind of array. There are couple of ways, here is one way :

Passing array to a SQL Server Stored Procedure
Your solution will involve use of CURSOR to build dynamic Sql query, see example :

SQL Server Cursor Example

edited May 23 '17 at 11:45

Community

1
1

answered Jun 03 '16 at 11:34

Edgars Pivovarenoks

1,526
1
17
31

Please see my comment above – Dani Jun 03 '16 at 12:04
You have answered it in C# example by mentioning key/value pair. What you will have to pass to Stored Procedure field and value combinations. – Edgars Pivovarenoks Jun 03 '16 at 12:09

Tab Alleman · Accepted Answer · 2016-06-03T13:58:32.453

2

I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value if it exists.

This is the way I have always done this:

Make the parameter optional by giving it a default value of NULL.

Then COALESCE the parameter with the field you want to use if no parameter value was supplied:

INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2) 
    SELECT @newStartDate, NULL, COALESCE(@parameter,Field1), Field2 
      FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate

Simple as that.

EDIT:

If you use a Table Valued Parameter, and JOIN to it, then your COALESCE looks like either this, if you have 1 column per field in the TVP:

COALESCE(tvp.Field1, Test.Field1)

Or if you passed the TVP as a vertical table of FieldName, FieldValue pairs, then you would have to use either a CASE expression or Subquery like this:

COALESCE((SELECT FieldValue FROM TVP WHERE FieldName='Field1'),Field1)

edited Jun 03 '16 at 13:58

answered Jun 03 '16 at 12:57

Tab Alleman

31,483
7
36
52

Thanks, the only problem is that my table has over 270 columns, that would be quite a parameter list... – Dani Jun 03 '16 at 13:28
In that case, you can pass a table-valued parameter, JOIN to it, and use the same technique. – Tab Alleman Jun 03 '16 at 13:32
How do you mean "JOIN"? I think that I don't understand it right. Do you have an example? – Dani Jun 03 '16 at 13:37
JOINing tables is a basic SQL operation that is too broad to teach in a SO answer. If you google `SQL JOIN Tutorial` you will find plenty of examples. – Tab Alleman Jun 03 '16 at 13:40
I know what JOIN's are but I don't get the point how joining a table-valued parameter solves my problem. – Dani Jun 03 '16 at 13:45
Added explanation in my answer. – Tab Alleman Jun 03 '16 at 13:58

score 0 · Answer 3 · answered Jun 03 '16 at 14:58

Not sure how c# parses query params but you can do something like this:

var sql = "
declare @param1 int = :param1,
  @param2 int = :param2,
  @param3 date = :param3,
  @somedate datetime = :somedate,
  @newstartdate datetime = :newstartdate,
  ...


insert into dbo.Test (...full list of columns)
select isnull(@param1, field1), isnull(@param2, field2), ...and so on
from dbo.Test
where StartDate = @someDate AND EndDate = @newStartDate
"

And then assign values or NULL to all the params. ISNULL will do the thing and you'll have new value or old value if new is NULL. But some tricks would be required to place exactly null.

or

you have to build whole query at runtime on client-side

or

pass TVP with param names and values as @TabAlleman suggested and check this table for existence of param of the same name as specific column int your table. And then proceed with ISNULL or something to choose whether to leave existing value or take from TVP passed.

Dynamically update with stored procedures in SQL Server

3 Answers3