Returning multiple rows from a stored procedure

[Wael]

Hi,
I have the following stored procedure that does some processing and
puts the result in a temporary table. I tried several things that
procedure to display output that I can access with ADO.Net, but it
doesn't work. It doesn't even display the result in the query analyzer
unless I add SELECT @ReturnFullName

Any help?

The stored procedure:
CREATE PROCEDURE sp_SEARCH_MULTI PLE_NAMES @search4fatherO f
varchar(255), @maximum_father s int = 100, @ReturnFullName varchar(255)
Output

....

SELECT @ReturnFullName = name FROM #FULLNAME

------------------------------------------------
To Execute the stored procedure:
DECLARE @test varchar(255)
EXEC sp_SEARCH_MULTI PLE_NAMES @search4fathero f='مريم',
@returnfullname =@test
PRINT CONVERT(varchar (255), @test)

[Hugo Kornelis]

On 22 May 2006 10:11:11 -0700, Wael wrote:

Hi,
I have the following stored procedure that does some processing and
puts the result in a temporary table. I tried several things that
procedure to display output that I can access with ADO.Net, but it
doesn't work.
Hi Wael,

If you want to return a resultset to the client, just add a command such
as the one below in the appropriate place in your stored procedure:

SELECT Co11, Col2, ...
FROM #TempTable
-- WHERE ?????

This will expose the results of this query as a recordset to the client.
The stored procedure:
CREATE PROCEDURE sp_SEARCH_MULTI PLE_NAMES @search4fatherO f
varchar(255) , @maximum_father s int = 100, @ReturnFullName varchar(255)
Output
Please choose a different name for your stored procedure. The "sp_"
prefix is reserved for Microsoft-supplied system stored procedures. If
you use this prefix for your own procedures, you will lose some
performance (because SQL Server will first try to find the procedure in
the master database), and yoou run the risk of unexpected effects if
Microsoft decides to use the same name for a system stored procedure
included in the next version, service pack or patch.

...

SELECT @ReturnFullName = name FROM #FULLNAME
If the #FULLNAME table holds more than one row, the effect of this
sttatement will be to assign the name from each of those rows in turn to
the variable, constantly replacing the "previous" value. Only the value
from the row that's processed last will stick. Since order of processing
of the rows is undefined, the net result of this statement will be to
waste some time and assign one "randomply chosen" name from the table to
@ReturnFullName .

------------------------------------------------
To Execute the stored procedure:
DECLARE @test varchar(255)
EXEC sp_SEARCH_MULTI PLE_NAMES @search4fathero f='????',
@returnfullnam e=@test
You have to include the OUTPPUT keyword on the call as well:

EXEC sp_SEARCH_MULTI PLE_NAMES
@search4fatherO f = '????',
@ReturnFullName = @test OUTPUT
PRINT CONVERT(varchar (255), @test)

No need for the CONVERT - @test is already typed as varchar(255).

--
Hugo Kornelis, SQL Server MVP

[Wael]

Hi Hugo,

Thanks for your response. My ultimate goal is to return all the rows
not just one. Is that doable?

Also even with the select statement, the results are not accessible to
ADO.Net even though I can see them in the query analyzer.

THanks
Wael

[Erland Sommarskog]

Wael (se***@rocketma il.com) writes:

Thanks for your response. My ultimate goal is to return all the rows
not just one. Is that doable?
Yes, that's a very normal thing to do.
Also even with the select statement, the results are not accessible to
ADO.Net even though I can see them in the query analyzer.

So how does your ADO .Net code look like?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Wael]

Private Sub btnSearch_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnSearch.Click
Dim drParents As SqlDataReader
Dim cmdParents As New SqlCommand
Dim spParamenters As New SqlParameter
cnParents.Conne ctionString = Constants.Conne ctionString
cmdParents.Comm andType = CommandType.Sto redProcedure
cmdParents.Comm andText = "sp_SEARCH_MULT IPLE_NAMES"
spParamenters.P arameterName = "@search4father Of"
spParamenters.P arameterName = "@returnfullnam e"
cmdParents.Para meters("@search 4fatherOf").Val ue =
txtSearchParent .Text
cmdParents.Para meters("@return fullname").Valu e = "test"
spParamenters.S qlDbType = SqlDbType.VarCh ar
cmdParents.Para meters.Add(spPa ramenters)
cnParents.Open( )
cmdParents.Conn ection = cnParents
drParents = cmdParents.Exec uteReader
Response.Write( drParents.GetSt ring(0))
drParents.Close ()

End Sub

I have to give the output parameter a value otherwise it doesn't work.
Here is the error i get:
An SqlParameter with ParameterName '@search4father Of' is not contained
by this SqlParameterCol lection.

Before I used the parameters I used to get an error that there was no
data. I don't see why I should be using 'Output' since I have to use
the SELECT statement anyway.

[Erland Sommarskog]

Wael (se***@rocketma il.com) writes:

Private Sub btnSearch_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles btnSearch.Click
Dim drParents As SqlDataReader
Dim cmdParents As New SqlCommand
Dim spParamenters As New SqlParameter
cnParents.Conne ctionString = Constants.Conne ctionString
cmdParents.Comm andType = CommandType.Sto redProcedure
cmdParents.Comm andText = "sp_SEARCH_MULT IPLE_NAMES"
spParamenters.P arameterName = "@search4father Of"
spParamenters.P arameterName = "@returnfullnam e"
cmdParents.Para meters("@search 4fatherOf").Val ue =
txtSearchParent .Text
cmdParents.Para meters("@return fullname").Valu e = "test"
...
I have to give the output parameter a value otherwise it doesn't work.
Here is the error i get:
An SqlParameter with ParameterName '@search4father Of' is not contained
by this SqlParameterCol lection.

What it says. You have defined a parameter, set the name of it twice.
But you have never added it to the Parameters collection. To that end
you need to use the .Add method.

Apparently, you are very new to ADO .Net programming. Unfortunately,
newsgroups are not good venues for learning things from scratch, because
the answer will be small tidbits here and there. You are better off
trying to find some book with exercises and samples to get you going.
Or find some classes you can take in your area.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx