|
|
Posted by Hugo Kornelis on 05/22/06 22:13
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
Navigation:
[Reply to this message]
|