Solved

SQL Msg 8162, Level 16, State 2

Posted on 2014-02-19
6
1,133 Views
Last Modified: 2014-02-19
When I execute the SQL procedure I get the following error message...

Msg 8162, Level 16, State 2, Procedure spITS_FullCaseLabels, Line 0
Formal parameter '@iUserID' was defined as OUTPUT but the actual parameter not declared OUTPUT.

Please see the attached jpg file for a screen shot and more information.
Thanks in advance.
msg-8162.JPG
0
Comment
Question by:tesla764
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 275 total points
ID: 39870437
Doesn't appear that the sp has the iUserID parameter defined as an output parameter in the proc definition.  You'll need to alter the procedure to mark that as an output parameter.
0
 

Author Comment

by:tesla764
ID: 39870454
Could you tell me how to do that?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 225 total points
ID: 39870464
please remove the OUTPUT for all the parameters excel the result and result_code parameters.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 10

Accepted Solution

by:
PadawanDBA earned 275 total points
ID: 39870483
Sure thing!  Easiest way:  right click the stored procedure > script stored procedure as > alter to > new query window.  Then all you'll need to do is look for the "@iUserID bigint" and change that to "@iUserID bigint OUTPUT".


Edit:
Guy does bring up a good point.  Unless your stored procedure is doing something that assigns a value to the @iUserID variable, it's not really going to do anything.
0
 

Author Comment

by:tesla764
ID: 39870494
Do you mean in the EXEC area remove all the OUTPUT statements?
What do you mean by...
excel the result and result_code parameters
Could you please explain.
I have never encountered this situation before.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 225 total points
ID: 39870611
you have the code like this :
declare @variable_1 data_type
 , @variable_2 data_type
 ...etc...
exec procedure_name @variable_1 OUTPUT
   ,  @variable_2 OUTPUT
    ... etc  ...

select @variable_1
    , @variable_2 
  ... etc ...

Open in new window


now, the BIG question is: should the stored procedure return data into all those (output) parameters or not.

if yes: the parameter code is wrong, as only the first 2 parameters (in the procedure itself, not in the calling code) are defined to be OUTPUT. to change that, you have to EDIT the stored procedure, and add OUTPUT to the parameter declaration. when you have the code of the stored procedure opened, you shall see that easily

if not: remove the OUTPUT keyword for all the parameters in the EXEC procedure_name part which are NOT returning any value, but for which you have to provide some input data so the procedure knows on what to work on.
for those, you need to SET the data, which needs to happen between the DECLARE and the EXEC part, like this:
set @variable = some_value

if all of this is "chinese" to you, I think you are completely wrong person on the task you are trying to achieve, in the sense that unless you do some SQL (T-SQL) training, you should keep your hands from the keyboard
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question