Initialise A Variable With The Output Of A Stored Procedure In MS SQL Server

Answer 1

You can create a function (instead of a procedure) that returns a table.

CREATE FUNCTION [dbo].[my_function]
(
   @par2     UNIQUEIDENTIFIER, 
   @par2     UNIQUEIDENTIFIER,
   @par3     UNIQUEIDENTIFIER
)
RETURNS @returntable TABLE 
(
   col1 UNIQUEIDENTIFIER,
   col2 NVARCHAR(50),
   col3 NVARCHAR(50)
)
AS
BEGIN
...
END

Answer 2

If you don't want to touch your PROCEDURE, you can create a FUNCTION that wraps it and use that wrapper function in queries.

Answer 3

Change the line:

SET @myDesc = 
  EXEC UDSPRBHPRIMBUSTYPESTARTUP @CODE = @myCode, @DESC = @@tempDesc OUTPUT

to

EXEC UDSPRBHPRIMBUSTYPESTARTUP @CODE = @myCode, @DESC = @tempDesc OUTPUT

And you have missed assigning @DESC in the stored procedure.

SET @SQL = 
  'SELECT @DESC = clnt_cat_desc FROM ' + @SERVERNAME + 
  '.' + @DBASE + '.dbo.hbl_clnt_cat WHERE inactive = ''N''
  AND clnt_cat_code = ''' + @CODE + ''''

EXECUTE sp_executeSQL @SQL, N'@DESC varchar(255) output', @DESC output

You should then use @tempDesc in the next select:

SELECT
  a.clid
  , b.fileid
  , c.usrfullname AS ClientPartner
  , e.usrfullname AS ClientFeeEarner
  , @tempDesc AS ClientPrimaryBusinessType

Also your stored procedure allows for SQL injection around:

SET @SQL = 
  'SELECT clnt_cat_desc FROM ' + @SERVERNAME + 
  '.' + @DBASE + '.dbo.hbl_clnt_cat WHERE inactive = ''N''
  AND clnt_cat_code = ''' + @CODE + ''''

EXECUTE sp_executeSQL @SQL

Answer 4

Pheww, I was going crazy on how to do this, I needed to make a result from a stored procedure part of a current query and I was having the hardest time doing this. What I did was wrap Procedure with a function and then return the value and that was it.

Thanks Again, Miami Web Design