# stored procedure with input and output parameters

Is there a way to call a stored procedure with an input and output parameters?

I tried to run one but it gives me different errors depending on the order of the parameters. See below:

DECLARE @LinkedServer NVARCHAR(MAX)
EXEC spGetLinkedServerOpenQuery @DynamicSQL = N'SELECT * FROM sys.databases', @LinkedServer OUTPUT

/* Msg 119, Level 15, State 1, Line 56
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'. */

EXEC spGetLinkedServerOpenQuery @LinkedServer OUTPUT, @DynamicSQL = N'SELECT * FROM sys.databases'

/* Msg 8162, Level 16, State 2, Procedure spGetLinkedServerOpenQuery, Line 54
The formal parameter "@DynamicSQL" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output. */


As it states in the first error message, you cannot mix methods of specifying parameters: you either need to always use "@param_name = value_or_variable" OR "value_or_variable". So just change the call to be:

(not specifying the parameter names requires that the values are provided in the order that the parameters are delcared in the CREATE PROCEDURE statement)

DECLARE @LinkedServer NVARCHAR(MAX);

N'SELECT * FROM sys.databases',



OR

(specifying the parameter names allows for the parameters to be specified in any order)

DECLARE @LinkedServer NVARCHAR(MAX);

@DynamicSQL = N'SELECT * FROM sys.databases',

-- or


• ok. I'm not sure that the output issue has anything to do with properly specifying input / output parameters such that this executes. What are you expecting the @LinkedServer variable to be populated with after executing the stored procedure? – Solomon Rutzky Jun 4 '18 at 15:56
• Ok. Why are you using a stored procedure instead of a scalar UDF for this, if the proc doesn't execute the code, but just does a lookup and string concatenation? Either way, without seeing the proc code I cannot tell you where it went wrong. Although if you get two result sets back then I bet you aren't setting the parameter value to anything within the proc, but are instead returning the result set from the proc. Did you forget to add the variable name to the SELECT statement in the proc? SELECT @OutputParam += N'SELECT ...' + ls.[name] + N', ' + @Query... or something like that? – Solomon Rutzky Jun 4 '18 at 16:08