Extract parameters from a table of parameters in a stored procedure?

Question

I defined the next Data Type:

CREATE TYPE dbo.FieldsTable AS TABLE
(
    [Name]  nvarchar(255),
    [Value] nvarchar(255)
)

I want to declare a stored procedure with this sign:

CREATE PROCEDURE dbo.uspSaveEntity
(
    @Id int,
    @Fields [FieldsTable] readonly
)
AS
...

This procedure should receive a FieldsTable type with the list of fields and values that I need to set in the UPDATE of the entity.

For example, to call the procedure I want to do somnething like:

DECLARE @entityFields FieldsTable;

INSERT INTO @entityFields VALUES ('FirstName', 'John');
INSERT INTO @entityFields VALUES ('LastName', 'Doe');

EXEC uspSaveEntity 1500, @entityFields

The question is: How I can extract the parameters from the table I receive?

Until now I was thinking in something like:

SET @FirstName = (SELECT Value FROM @Fields WHERE Name = 'FirstName');
SET @LastName = (SELECT Value FROM @Fields WHERE Name = 'LastName');
...
UPDATE tblEntity SET FirstName = @FirstName ...

I think using SET and SELECT for each field is too much code maybe... exist some approche more clear to do this?

The UPDATE query and all the stored procedure dont need to be dynamic, but needs to support have this particular way to receive parameters.

Gordon Linoff · Accepted Answer · 2015-11-18 04:04:59Z

1

The individual set statements are quite reasonable. An alternative would be a pivot or conditional aggregation:

select @FirstName = max(case when Name = 'FirstName' then Value end),
       @LastName = max(case when Name = 'LastName' then Value end),
       . . .
from @Fields;

answered Nov 18, 2015 at 4:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gabriel Roldan Over a year ago

I like this way you suggested, I think to do something like: SELECT @ FirstName = COALESCE(MAX(CASE WHEN Name = 'FirstName' THEN Value END), 'Default'), @ LastName = COALESCE(MAX(CASE WHEN Name = 'LastName' THEN Value END), 'Default') FROM @ Fields To have default values. Thanks!

Giorgi Nakeuri · Accepted Answer · 2015-11-18 04:13:41Z

0

The table you have is a local table and rows count I suspect is very little. So from performance view every solution will be identical. I would prefer the solution which is more readable in this case. You can directly select values in variables in select without using set statements:

SELECT @FirstName = Value FROM @Fields WHERE Name = 'FirstName';

answered Nov 18, 2015 at 4:13

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

Extract parameters from a table of parameters in a stored procedure?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related