sql - How do I execute a stored procedure with multiple output parameters? -

i have stored procedure 2 output parameters , want execute using sql fail values out when assigning them result of stored procedure.

in short have stored procedure gets company of logged in user has 2 output parameters. 1 integer error code , company name. stored procedure returns result of other users of company.

since users have same company name didn’t want bloat response field , thought best assign company name variable cannot work proper.

my sql execute stored procedure is:

declare @parmret1 varchar(30) declare @parmret2 int  execute crud_user @pvchaction = 'select',                       @id = '30862982-e189-4ce6-a299-5996d62ddcd5',                   @pcompanynameout = @parmret1 output,                    @pinterrdescout = @parmret2 output  select @parmret1 [output parameter], @parmret2 [output parameter] 

my stored procedure code is:

alter proc [dbo].[crud_user] (             @id                 varchar(400)=null,       @name               varchar(400)=null,       @phone              varchar(200)=null,       @pricenotes         varchar(max)=null,       @isactive           bit= 0,       @pvchaction         varchar(50)=null,             @pinterrdescout     int output,     @pcompanynameout    varchar(200) output     )                     begin              if(@pvchaction='select')                  begin                 --exec    @pcompanynameout = dbo.sel_company_by_userid @id output                  set @pinterrdescout = '3'; --works in getting output                 exec @pcompanynameout = sel_company_by_userid @id --not working                   select   *                   aspnet_users u                 left outer join userinfo ui on u.userid = ui.userid                  left outer join aspnet_membership m on m.userid = u.userid                  left outer join company c on c.id = ui.companyid                  u.userid = isnull(@id, u.userid)  ;                  return            end      .....   if (@@error <> 0)                    begin                          set @pinterrdescout = 1                 end              else                   begin                         set @pinterrdescout = 0               end      end 

when extracting stored proc sql sel_company_by_userid works:

set @pcompanynameout = (select name                          company c inner join userinfo ui                          on c.id = ui.companyid                        ui.userid = '30862982-e189-4ce6-a299-5996d62ddcd5'  ) 

but fails when want have proc retirn teh same:

"exec @pcompanynameout = sel_company_by_userid '30862982-e189-4ce6-a299-5996d62ddcd5'   

after having little chat in comments section boils down return value procedure sel_company_by_userid, cannot return anyother datatype other int procedure. other int datatypes need use output parameters.

have @ following code.

alter sel_company_by_userid @id guid, @nameval varchar(100) output   --<-- add param here "output" key word begin   set nocount on;    select @nameval   =  name    company c inner join userinfo ui    on c.id = ui.companyid    ui.userid = @id end 

now once have changes logic inside sel_company_by_userid stored procedure , change accordingly in [dbo].[crud_user].

something

  if(@pvchaction='select')                  begin              set @pinterrdescout = '3'; --works in getting output             execute sel_company_by_userid @id = @id                                         , @nameval = @pcompanynameout output      ..... rest of code......