?
Solved

Stored Procedure Output Value

Posted on 2007-10-20
6
Medium Priority
?
159 Views
Last Modified: 2013-11-26
Can some please tell what I am doing wrong.  I cannot get this to output a value.  If I dont assign a value to @SystemsAssigned parameter it gives me this error "Procedure or Function 'sp_CG_LB_SelectGrower' expects parameter '@SystemsAssigned', which was not supplied."  If I do assign a value it runs and returns nothing.

ALTER PROCEDURE [dbo].[sp_CG_LB_SelectGrower]
      -- Add the parameters for the stored procedure here
      @Operator varchar(50),
      @SystemsAssigned varchar(100) OUTPUT
AS
BEGIN
    Blah blah
END

            'Define command
            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Add(New SqlParameter("@Operator", SqlDbType.VarChar)).Value = Barcode
                    .Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output))

                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
==>             SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
0
Comment
Question by:pat_cunningham
6 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1200 total points
ID: 20116575
First, I have always sent a value with out parameters, 0 for integers and the empty string for varchars. I seem to remember a similar problem when I was first trying to use OUT parameters.

Second, when you say it doesn't return anything,
1. What does the SP assign to that variable? and
2. what is the value of that parameter when it gets back?

Jim
 
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20116609
I just noticed one other issue: Parameter.Value is an object. Since you are retrieving a string, so you need to cast Parameters("@SystemsAssigned").Value to a string before you can assign it.

Jim



0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20116881
Hi there,

Have you checked if the SP returns a value for that given parameters using management studio?

Have you viewed .Parameters("@SystemsAssigned").Value after the execution in a debug session, is it an unintended result?

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 20117224
I agree with JimBrandley's first comment:
>First, I have always sent a value with out parameters, 0 for integers and the empty string for varchars. I seem to remember a similar problem when I was first trying to use OUT parameters.

do that:
 'Define command
            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Add(New SqlParameter("@Operator", SqlDbType.VarChar)).Value = Barcode
                    .Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output)).Value = ""

                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
==>             SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
0
 
LVL 8

Assisted Solution

by:MrRobot
MrRobot earned 400 total points
ID: 20117264
btw,

I'm assuming you're already feeding your output variable like

.Parameters.Add(New SqlParameter("@SystemsAssigned", SqlDbType.VarChar, 100, ParameterDirection.Output)).Value = ""

since you've mentioned you already tested it assigning a value and it doesn't send the value and throws an exception otherwise.
0
 

Author Comment

by:pat_cunningham
ID: 20122526
Thanks for your help I was able to get it to work like this.  I am still not sure why the other one would not work.

            Using cmd As New SqlCommand(sql, New SqlConnection(My.Settings.MaterialsConnectionString))
                With cmd
                    'Set command type
                    .CommandType = CommandType.StoredProcedure
                    'Add parameters
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@Operator", Barcode)
                    .Parameters.AddWithValue("@SystemsAssigned", SelectSystem)
                    .Parameters("@SystemsAssigned").Direction = ParameterDirection.Output
                    .Parameters("@SystemsAssigned").Size = 100
                    If .Connection.State = ConnectionState.Closed Then .Connection.Open()
                    'Execute command
                    .ExecuteNonQuery()
                    SelectSystem = .Parameters("@SystemsAssigned").Value
                    'Close connection
                    .Connection.Dispose()
                End With
            End Using
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 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