Using several columns of a row as stored procedure parameters

Question

I have an SQL stored procedure with several parameters, and I want to call it using several columns from (the same) row for some of the parameters. Here's what the current implementation looks like (not including the unused columns / parameters, which exist):

DECLARE @id int;
select @id = min( id ) from Table_1;
declare @param1 nvarchar(10), @param2 nvarchar(10);
select  @param1 = param1, @param2 = param2 from Table_1 WHERE id=@id;
EXEC [dbo].otherProc @param1, @param2;

This is an improvement over multiple selects, but I can't help wondering if there's a way of doing this without variables (except @id). Selecting directly in the EXEC statement doesn't seem to work (or maybe I don't know the right syntax for it).

You want table valued types/parameters.msdn.microsoft.com/en-us/library/… — Giorgi Nakeuri
– Giorgi Nakeuri, Commented May 18, 2016 at 13:05
You could change your procedures to accept just the @id parameter and perform your selects inside the procedure along with your other code.Oracle allows for a RowType which lets you specify a single variable/parameter as a row of a particular table, but afaik, SQL Server does not have this. — gmiley
– gmiley, Commented May 18, 2016 at 13:09
@gmiley for normal stored proc that might be a good idea, but in my case otherProc is CLR (autogenerated from C# code) so even if that worked it would only make the code worse. — MASQ
– MASQ, Commented May 18, 2016 at 14:08
@Giorgi Nakeuri I haven't worked with table-valued parameters, would it involve changing the parameters in the stored proc definition? In my case otherProc is CLR and from what I've seen, passing table-valued parameters to CLR is either impossible or extremely complicated. — MASQ
– MASQ, Commented May 18, 2016 at 14:12
Yes it requires changes to stored proc parameters. Anyway you can pass to proc only constants and variables. You can not have expressions there. — Giorgi Nakeuri
– Giorgi Nakeuri, Commented May 18, 2016 at 14:17

Tab Alleman · Accepted Answer · 2016-05-18 14:59:46Z

1

You could execute a dynamic solution:

DECLARE @sql nvarchar(max) 
SELECT @sql = '
EXECUTE dbo.OtherProc ''' + param1 + ''',''' + param2 + ''';'
FROM Table_1
WHERE id=@id;

EXECUTE (@sql);

answered May 18, 2016 at 14:59

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

Comments

Zohar Peled · Accepted Answer · 2016-05-18 14:02:48Z

0

Unless you can change the stored procedure, the only improvement I came up with is this:

DECLARE @id int, @param1 nvarchar(10), @param2 nvarchar(10);
select @id = min( id ) from Table_1;

select  TOP 1 @id = id, 
        @param1 = param1, 
        @param2 = param2 
from Table_1 
order by id;

this way you populate all parameters in a single select statement.

answered May 18, 2016 at 14:02

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

2 Comments

MASQ Over a year ago

My code also populates them all in a single statement. I was hoping to avoid parameters, since I don't do anything with them except pass values through them.

Zohar Peled Over a year ago

The code you posted populates the @Id first, and then the other parameters. mine populates the @id with all the others. again, if you can change the procedure, you can pass only the @id and populate everything else inside the procedure.

Collectives™ on Stack Overflow

Using several columns of a row as stored procedure parameters

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related