OUTPUT and Recordset

I have created a 'Intelligent' search stored procedure that accepts two input parameters SearchFor and SearchIn and an OUTPUT parameter @.Result

Basically it starts by looking for a match for the SearchFor and if more than 1 is found, returns a recordset of the matches so the user can select a SearchFor category, at the same time the OUTPUT parameter is set to let the calling script know what is being returned.

Likewise it does the same for the SearchIn until both are satisfied and the true search results recordset can be returned.

Is it possible to get an OUTPUT parameter AND a recordset at the same time?

Any help will be appreciated,

PhlazarI would put the results of the stored procedure into a global temp table....

Cheers
C

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp
or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:
> How to get a field from a specific column as output parameter?
> Hrcko
>
>

out parameter

hello all,
i am trying to get a returned value from a SP using an out parameter.
my code is in C# and i need some help (pls).
be greatfull if you send an example of the SP and the C# code that execute
it and collect the returning parameter value.
thanks a lot, Ran.
I don't think this has a C# example but it does explain OutPut parameters:
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Ran Y" <ranyc@.012.net.il> wrote in message
news:OjMSGU$PEHA.3456@.TK2MSFTNGP11.phx.gbl...
> hello all,
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
> thanks a lot, Ran.
>
|||[posted and mailed, please reply in news]
Ran Y (ranyc@.012.net.il) writes:
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
You did not specify which .Net Data Provider you are using, so I am
assuming SqlClient. Furthermore, the code I have around is VB.Net,
so you will need to transliterate in to C# on your own:
Dim p As SqlParameter = New SqlParameter
p.ParameterName = "@.outparam"
p.DbType = SqlDbType.Int ' For instance.
p.Direction = ParameterDirection.InputOutput
p.Value = DBNull.Value
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery ' Or .Fill or an .ExecuteReader loop
The value of @.outparam is now in p.Value.
There are a couple of variations on how you can create the
parameter, see the online documentation for this.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

OUT and OUTPUT - Stored procedure parameters.

Hi All,

The following is a code snippit. My main interests are the OUT and OUTPUT parameter keywords. One returns a single value, and the other seemingly a resultset. OUTPUT returns a single value, however OUT seems to return a list of values. Could I please get this confirmed?

Also, I cannot see how the value being returned by OUT is being iterated...

Any help on the obove two matters is appreciated.

Thank You

Chris

BEGIN SNIPPET-

--The following example creates the Production.usp_GetList

--stored procedure, which returns a list of products that have

--prices that do not exceed a specified amount.

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.uspGetList;

GO

CREATE PROCEDURE Production.uspGetList @.Product varchar(40)

, @.MaxPrice money

, @.ComparePrice money OUTPUT

, @.ListPrice money OUT

AS

SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;

-- Populate the output variable @.ListPprice.

SET @.ListPrice = (SELECT MAX(p.ListPrice)

FROM Production.Product AS p

JOIN Production.ProductSubcategory AS s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);

-- Populate the output variable @.compareprice.

SET @.ComparePrice = @.MaxPrice;

GO

USE

DECLARE @.ComparePrice money, @.Cost money

EXECUTE Production.uspGetList '%Bikes%', 700,

@.ComparePrice OUT,

@.Cost OUTPUT

IF @.Cost <= @.ComparePrice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

END

ELSE

PRINT 'The prices for all products in this category exceed

$'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'

-

Partial Result Set

-

--Product List Price

-

--Road-750 Black, 58 539.99

--Mountain-500 Silver, 40 564.99

--Mountain-500 Silver, 42 564.99

--...

--Road-750 Black, 48 539.99

--Road-750 Black, 52 539.99

--

--(14 row(s) affected)

--

--These items can be purchased for less than $700.00.

Well, OUT and OUTPUT keywords are synonyms, just as INT and INTEGER are.|||

Really?

AAAAAAAARGH it's all clear now. I can't see why in the example they mix and match however though - it just brings confusion into the equation.

No point in answering the other part of my Q: I 'm sure the example iterates throught the execution of the SP, but doesn't show it.

Thank you Sergey.

OSQL spelling mistake?

Hi,
has anybody else noticed a spelling mistake when using OSQL utility with the
-E -L switches. It reads The -L parameter can not be used in combineation
with others parameters
Robin
Robin (Robin@.discussions.microsoft.com) writes:
> has anybody else noticed a spelling mistake when using OSQL utility with
> the -E -L switches. It reads The -L parameter can not be used in
> combineation with others parameters
Apparently :-) It has been fixed in SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.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

osql and line spacing

I am importing a query to a .txt file and for some reason my file is being
written double spaced! Is there a parameter I am overlooking that will
control this' I do not know how long my lines will be so I am setting them
to be arbitrarily long so that I know that the data will not force a carrage
return.
The statement I am using to excecute my query looks like this:
"osql -SServer -Uusername -Ppassword -h-1 -o C:\Test\test.txt -s","
-w"2000" -Q"Select ' + @.flds + ' from Mytable"
I build my query in the @.flds variable based on values in another table. I
know that that part works. I just can not figure out why my output ends up
double spaced.
Thanks in advance.swag: is word wrap on in your text viewer?
Rick wrote:
> I am importing a query to a .txt file and for some reason my file is being
> written double spaced! Is there a parameter I am overlooking that will
> control this' I do not know how long my lines will be so I am setting the
m
> to be arbitrarily long so that I know that the data will not force a carra
ge
> return.
> The statement I am using to excecute my query looks like this:
> "osql -SServer -Uusername -Ppassword -h-1 -o C:\Test\test.txt -s","
> -w"2000" -Q"Select ' + @.flds + ' from Mytable"
> I build my query in the @.flds variable based on values in another table.
I
> know that that part works. I just can not figure out why my output ends u
p
> double spaced.
> Thanks in advance.
>|||No, But I was using note pad. You made me curious so I tried word pad, all
was better.
THANKS!
"Trey Walpole" wrote:

> swag: is word wrap on in your text viewer?
> Rick wrote:
>