CODEWITHSUNDEEP
sqlsql-serverstored-proceduresparameters
MASQ
MASQ

Reputation: 135

Using several columns of a row as stored procedure parameters

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).

Upvotes: 0

Views: 210

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

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);

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 0

Related Questions