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)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|||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|||Wael (sedky@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.|||Wael (sedky@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment