-1

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
SELECT @LinkedServer

/* 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'. */

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

/* 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. */
Improve this question

1 Answer 1

Reset to default
2

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

EXEC dbo.spGetLinkedServerOpenQuery
    N'SELECT * FROM sys.databases',
    @LinkedServer OUTPUT;

SELECT @LinkedServer;

OR

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

DECLARE @LinkedServer NVARCHAR(MAX);

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

-- or

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

SELECT @LinkedServer;

Also, it is best to always specify the Schema name for schema-bound objects (i.e. the "dbo." in this case)

Improve this answer
7
  • Thanks. Noted on the dbo tip. I am getting 2 separate NULL result-set column. I am currently trying to figure it out. Basically what the sp tries to do is accepts a dynamic query and outputs a SELECT * FROM OPENQUERY query per linked server.
    – Edgar Allan Bayron
    Jun 4, 2018 at 15:52
  • 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, 2018 at 15:56
  • yes. the output would be something like .. SELECT * FROM OPENQUERY([server1\instance1], 'SELECT * FROM sys.databases') UNION ALL SELECT * FROM OPENQUERY([server2\instance1], 'SELECT * FROM sys.databases') UNION ALL .... This output, which is in LinkedServer parameter will be populated based from the existing linked servers. The dynamic query will be based from the input in DynamicSQL parameter.
    – Edgar Allan Bayron
    Jun 4, 2018 at 16:01
  • 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, 2018 at 16:08
  • Hi, thanks for that. Anyway, was originally a UDF. But I am thinking to include a PRINT output in it that's why I am trying to avoid UDF. I will just stick with a SELECT statement instead of PRINT. Thanks for the help!
    – Edgar Allan Bayron
    Jun 4, 2018 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.