473,225 Members | 1,209 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

Returning multiple rows from a stored procedure

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_MULTIPLE_NAMES @search4fatherOf
varchar(255), @maximum_fathers int = 100, @ReturnFullName varchar(255)
Output

....

SELECT @ReturnFullName = name FROM #FULLNAME

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

May 22 '06 #1
5 16519
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_MULTIPLE_NAMES @search4fatherOf
varchar(255), @maximum_fathers 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_MULTIPLE_NAMES @search4fatherof='????',
@returnfullname=@test
You have to include the OUTPPUT keyword on the call as well:

EXEC sp_SEARCH_MULTIPLE_NAMES
@search4fatherOf = '????',
@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
May 22 '06 #2
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

May 22 '06 #3
Wael (se***@rocketmail.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****@sommarskog.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
May 22 '06 #4
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSearch.Click
Dim drParents As SqlDataReader
Dim cmdParents As New SqlCommand
Dim spParamenters As New SqlParameter
cnParents.ConnectionString = Constants.ConnectionString
cmdParents.CommandType = CommandType.StoredProcedure
cmdParents.CommandText = "sp_SEARCH_MULTIPLE_NAMES"
spParamenters.ParameterName = "@search4fatherOf"
spParamenters.ParameterName = "@returnfullname"
cmdParents.Parameters("@search4fatherOf").Value =
txtSearchParent.Text
cmdParents.Parameters("@returnfullname").Value = "test"
spParamenters.SqlDbType = SqlDbType.VarChar
cmdParents.Parameters.Add(spParamenters)
cnParents.Open()
cmdParents.Connection = cnParents
drParents = cmdParents.ExecuteReader
Response.Write(drParents.GetString(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 '@search4fatherOf' is not contained
by this SqlParameterCollection.

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.

May 22 '06 #5
Wael (se***@rocketmail.com) writes:
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSearch.Click
Dim drParents As SqlDataReader
Dim cmdParents As New SqlCommand
Dim spParamenters As New SqlParameter
cnParents.ConnectionString = Constants.ConnectionString
cmdParents.CommandType = CommandType.StoredProcedure
cmdParents.CommandText = "sp_SEARCH_MULTIPLE_NAMES"
spParamenters.ParameterName = "@search4fatherOf"
spParamenters.ParameterName = "@returnfullname"
cmdParents.Parameters("@search4fatherOf").Value =
txtSearchParent.Text
cmdParents.Parameters("@returnfullname").Value = "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 '@search4fatherOf' is not contained
by this SqlParameterCollection.


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****@sommarskog.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
May 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Rowland Hills | last post by:
I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM...
3
by: JB | last post by:
To anyone that is able to help.... What I am trying to do is this. I have two tables (Orders, and OrderDetails), and my question is on the order details. I would like to set up a stored...
3
by: - | last post by:
I have a country table with code and name columns and create a stored procedure 'get_countries()' but have no idea what is the syntax to return multiple rows. I have searched the newsgroups and...
5
by: Stanley Sinclair | last post by:
I have a need to return multiple result sets from a stored procedure. Want that SP to call others to get the data. Win2003, db2 8.1.5. Can't figure out how to handle open cursors, and return...
1
by: Andrew | last post by:
Hey all, I am very new to ASP.Net (and .Net in general), but that isn't stopping the boss from wanting to begin new projects in it. This latest project has me kinda stumped and after a couple...
2
by: jzogg7272 | last post by:
In my code I am executing a stored procedure to do a single row insert. I check the return value of the execution and I am getting -1, whereas a few weeks ago it was returning 0. Actually, I found...
3
by: bogdan | last post by:
Hi, I have a stored procedure that returns a single value. Example: SELECT @RowCount = COUNT(*) FROM t WHERE RETURN @RowCount I created a data set, table adapter, and adapter's method...
6
by: Constantine AI | last post by:
Can anyone help? Ive created the below stored procedure to allow an input parameter of post code to be entered to display properties which have this post code. The procedure creates fine but...
3
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.