[RESOLVED] Execution of SP in SQL Server 2008

[sonia.sardana]

Mine SP : -

CREATE PROCEDURE [dbo].[GetMarks]
@iRoll int,
@iMarks int OUTPUT
AS
BEGIN
Select @iMarks = marks  from student where roll = @iRoll 
END
GO

In this SP,for consideration I just use the simple Table STudent whose columns are Roll & Marks.
Through SP, I want to get the Marks as OutPut Parameter by Passing ROll as Input Parameter. My SP is succeed successfully.

I just want to know how to Execute it & see the Result of SP in SQL .
To Execute I use the following commands : -

Declare @temp_Marks Int 
EXEC GetMarks 1 , @iMarks = @temp_Marks

When I execute the above statement I get the message Command(s) completed successfully.But I am not able to see the Mark of Roll Number 1. How to get that?? Kindly Help ME!!!!

[abhijit]

PRINT @iMarks

[TysonLPrice]

Try this out:

Create table Students(marks int, roll int)
insert into students(marks,roll) values(1,1)

Create PROCEDURE [dbo].[GetMarks]
@iRoll int,
@iMarks int OUTPUT
AS
BEGIN
set @imarks = (select marks  from students where roll = @iRoll)
Select @iMarks
END
GO

exec getmarks 1, null

drop procedure GetMarks
drop table students

[techgnome]

sigh.... no, no, no....

Pay attention people... the parameter is an output ...
PRINT @iMarks won't work because it's the name of the parameter inside the sproc... outside of the sproc @iMarks is undefined.

Tyson's is a little better except for it totally ignores the output parameter...why have the output if you're going to select it... plus it doesn't capture the result into a variable for use later.

The original sproc is fine... the problem is in how it's called.
it should be like this:

Declare @temp_Marks Int 
EXEC GetMarks 1 , @temp_Marks OUTOUT
-- NOW you can print @temp_Marks, select it, or use it for other purposes

-tg

[TysonLPrice]

sigh.... no, no, no....

Pay attention people... the parameter is an output ...
PRINT @iMarks won't work because it's the name of the parameter inside the sproc... outside of the sproc @iMarks is undefined.

Tyson's is a little better except for it totally ignores the output parameter...why have the output if you're going to select it... plus it doesn't capture the result into a variable for use later.

The original sproc is fine... the problem is in how it's called.
it should be like this:

Declare @temp_Marks Int 
EXEC GetMarks 1 , @temp_Marks OUTOUT
-- NOW you can print @temp_Marks, select it, or use it for other purposes

-tg

I must be missing something. I tested my approach and it works...at least the way I interpreted the OP. Just try it.

[techgnome]

Oh I don't doubt that it works.... but the intent is to put the result into a variable.... which is why there is an output variable in the first place... why put it into the variable in the sproc, only to select it... And then you pass in a null for the parameter so once again, it's not in a variable where I could use it again. It may have worked... but it needs to work right.

My point being that the OP didn't put OUTPUT on the parameter when calling the sproc... ultimately that's all that was really missing from the original code... otherwise it was perfectly fine.

-tg

[TysonLPrice]

Oh I don't doubt that it works.... but the intent is to put the result into a variable.... which is why there is an output variable in the first place... why put it into the variable in the sproc, only to select it... And then you pass in a null for the parameter so once again, it's not in a variable where I could use it again. It may have worked... but it needs to work right.

My point being that the OP didn't put OUTPUT on the parameter when calling the sproc... ultimately that's all that was really missing from the original code... otherwise it was perfectly fine.

-tg

You posted too fast. I was on a smoke break and I realized why it works and why it doesn't really work as it was posted

I get it...

[techgnome]

It's all goood... at least it lead itself to the discussion of how there's different ways to solve the same problem....

-tg

[sonia.sardana]

Hello ALL, I changed my SP as follows : -

Create PROCEDURE [GetMarks]  
@iRoll int,  
@iMarks int OUTPUT  ,
@sName varchar(10) OUTPUT
AS  
BEGIN  
 (select marks from student where roll = @iRoll)  END

Called in the SP in the following way : -

Declare @temp_Marks Int 
EXEC GetMarks 2 , @iMarks = @temp_Marks

It Worked for me.
In the original SP, I just Changed the statement (BOLD) & all worked

[szlamany]

Just wanted to point out that you have some unneeded BEGIN/END and ()'s.

If you start thinking things like that are required you won't appreciate the real reason that they are required.

A stored procedure - created in a query window - where you click EXECUTE - should look like this

Create PROCEDURE [GetMarks]  
@iRoll int,  
@iMarks int OUTPUT  ,
@sName varchar(10) OUTPUT
AS
select marks from student where roll = @iRoll
Go

The BEGIN/TRAN and ()'s are not needed.

If you are using a QUERY WINDOW and clicking EXECUTE button to load up this SPROC then the GO is used to tell SQL Server Management Studio (SSMS) that this batch of text needs to be sent to the server. You could follow the GO with more batches of SQL. Sometimes you need to do this in a script if the script is going to alter things like column names.

If you are putting up this SPROC in some wizard-create-procedure window the GO can probably be skipped.

Also - you are missing the third parameter from the EXECUTE statement.

btw - why are you using variables to pass data out of a select when the SELECT naturally puts the data in a very easy to read data object (such as a sql reader...)??

[sonia.sardana]

Yes you are right, BEGIN, END & braces are not required even the SP will also work without Go keyword. I m missing the third parameter also. I am putting the values in a variables,& fetching the values using the output parameter cz this SP I used with VS.NET.

[szlamany]

It is more traditional to use a SQLCommand into a SQLDataReader to process data from a select statement.

Like this:

        Try
            Using dcn As New SqlConnection(m_ConnStr)
                Using cmd As New SqlCommand
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandText = "dbo.WordList_Get"
                    cmd.Connection = dcn
                    dcn.Open()
                    Using sdrReader As SqlDataReader = cmd.ExecuteReader
                        While sdrReader.Read
                            m_WordList.Add(sdrReader(0).ToString)
                        End While
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Reader_Load WordList_Get")
        End Try

Of course this is setup to handle several rows of data - you only have one row in your sproc.

If you only had one value to return the normal fashion is to use

Try
   Using dcn As New SqlConnection(m_ConnStr)
       Using cmd As New SqlCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "Session_Load" ' "TagInfo_Load"
            cmd.Connection = dcn
            cmd.Parameters.AddWithValue("@Username", "STEVE")
            dcn.Open()
            Dim strMessage As String = cmd.ExecuteScalar.ToString
            FSOb.FNTags = strMessage
        End Using
    End Using
Catch ex As Exception
    returnMessage = "GetContextCallBack: " & ex.Message
End Try

I only ever use OUTPUT PARAMETERS when I have a requirement of returning some kind of status values in additional to a recordset of data.