Trying to return only the necessary output param | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Trying to return only the necessary output param

I have a Stored Procedure that returns four output parameters every time it is executed.
But I would like that only is returned one, two or three output parameters depending on the specific case. Thus, my web application will not to wait always four parameters in situations that only expects for example one parameter. Is it possible to do such thing? Here is the piece of the SP that determines the output parameter values:
USE market
GO
ALTER PROC new_offer2
@Offer_id bigint, …, @Picture_name varchar(256) = null, …, @Picture_name_QA varchar(256) = null, …, @Picture_name_QM varchar(256) = null, …, @Picture_name_QB varchar(256) = null, …, @OfId_Picture_name varchar(256) output, @OfId_Picture_name_QA varchar(256) output, @OfId_Picture_name_QM varchar(256) output, @OfId_Picture_name_QB varchar(256) output, @OfId_Picture_name_QB varchar(256) output AS Declare @Offer_num As varchar(256)
SET @Offer_num = @Offer_id
SET @OfId_Picture_name =
CASE @Picture_name WHEN null THEN null (not output param) ELSE @Offer_num + ‘_’ + @Picture_name (output param) END
SET @OfId_Picture_name_QA =
CASE @Picture_name_QA WHEN null THEN null (not output param) ELSE ‘QA_’ + @Offer_num + ‘_’ + @Picture_name_QA (output param) END
SET @OfId_Picture_name_QM =
CASE @Picture_name_QM WHEN null THEN null (not output param) ELSE ‘QM_’ + @Offer_num + ‘_’ + @Picture_name_QM (output param) END
SET @OfId_Picture_name_QB =
CASE @Picture_name_QB WHEN null THEN null (not output param) ELSE ‘QB_’ + @Offer_num + ‘_’ + @Picture_name_QB (output param) END
SET NOCOUNT ON UPDATE Offers
… SET NOCOUNT OFF
GO Thanks
you can have optional output parameters by adding the = null keyword before the output keyword in the proc declaration… that’s about as close as you can get I think… Cheers
Twan
Yes it is thus, [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />] Thank you very much!
]]>