Why is my Output Parameter not working?

[Axel]

Hi,

I am currently creating an ASP page that returns a recordset of search
result based on multiple keywords. The where string is dynamically
built on the server page and that part work quite well. However I want
to also return the number of records in the recordset and I can not
manage to get any output parameter working. Even if I assign SELECT
@mycount=100 (or SET @mycount=100) in the SP the only value set in
mycount is always NULL. I tested various theories (e.g. early exit,
order & naming of parameters etc. but I can not make the SP set the
output parameters - has it anything to do with the execute?). @mycount
also returns NULL if I test it in SQL QA.

What's wrong with this SP (as regards mycount):

CREATE PROCEDURE dbo.spFindProdu cts
@mycount integer OUTPUT,
@whereString varchar (1000)
AS

--SET NOCOUNT ON

--Set a Default value for the Wherestring which will return all records
if the Wherestring is blank
IF @whereString is Null
SELECT @whereString = 'AND TblProduct.Prod uctID is not null'

--Declare a variable to hold the concatenated SQL string
DECLARE @SQL varchar(2500)

-- AND (((UPPER([TblProduct].[ProductName] + [ProductGroupCod e] +
[AttributeValue1] +
-- [SearchWords] + [tblSupplier].[SupplierCode] + [SupplierDesc]))
Like '%screw%'))

SELECT @SQL = 'SELECT TblProduct.Prod uctID, TblProduct.Prod uctName,
TblProduct.Chap terCode, tblProduct.Prod uctGroupCode' +
' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.Prod uctID =
TblStockItem.Pr oductID) ' +
' LEFT JOIN tblSupplier ON TblProduct.Supp lierCode =
tblSupplier.Sup plierCode' +
' WHERE 1=1 ' + @whereString +
' GROUP BY TblProduct.Prod uctID, TblProduct.Prod uctName,
TblProduct.Chap terCode, TblProduct.Prod uctGroupCode'

SELECT @mycount = 200; -- test

execute (@SQL);
-- next line seems to be ignored ?
SELECT @mycount = @@rowcount;
GO

tia
Axel

[Erland Sommarskog]

Re: Why is my Output Parameter not working?

Axel (realraven2000@ hotmail.com) writes:[color=blue]
> I am currently creating an ASP page that returns a recordset of search
> result based on multiple keywords. The where string is dynamically
> built on the server page and that part work quite well. However I want
> to also return the number of records in the recordset and I can not
> manage to get any output parameter working. Even if I assign SELECT
> @mycount=100 (or SET @mycount=100) in the SP the only value set in
> mycount is always NULL. I tested various theories (e.g. early exit,
> order & naming of parameters etc. but I can not make the SP set the
> output parameters - has it anything to do with the execute?). @mycount
> also returns NULL if I test it in SQL QA.[/color]

That is more likely to have to with how you call the procedure. You
must specify the parameter as OUTPUT. In T-SQL:

EXEC spFindProducts @cnt OUTPUT, @string

I can tell you should do it in ASP, since I don't know how you call the
procedure. Assuming that you use ADO, there is an adDirectionOutp ut
somewhere.

Then again, if this is your idea about stored procedures, I think you
are better off stop using stored procedures at all and generate all
SQL in the client. This only adds to your complexity, but you win nothing.

I have an article on my web site which discusses various techniques
to implement dynamic searches, that may be useful to you:

http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mario]

Re: Why is my Output Parameter not working?

Axel,

Try this...

Return @@rowcount

Stored procedure returns an integer as a return code, use it for
anything you want!!!

[Mario]

Re: Why is my Output Parameter not working?

Sorry,
I just noticed that you are executing a sql statement.
In that case remember that a brand new environment is created for that
execution and has nothing to do with your currently stored procedure.
Hence the nulls on @@rowcount...
The only way to do this is to send your output to a #table, then do a
select count(*) from #table.
So, do a select into #table and it will work...
Yes, executing sql is very tricky...

Good luck!

[Axel]

Re: Why is my Output Parameter not working?

> > output parameters - has it anything to do with the execute?). @mycount[color=blue][color=green]
>> also returns NULL if I test it in SQL QA.[/color][/color]
[color=blue]
>That is more likely to have to with how you call the procedure. You
>must specify the parameter as OUTPUT. In T-SQL:[/color]
[color=blue]
> EXEC spFindProducts @cnt OUTPUT, @string[/color]

thanks Erland, now I know that my SP works at least. That already helps
a great deal!
[color=blue]
>I can tell you should do it in ASP, since I don't know how you call the[/color]
procedure. Assuming that you use ADO, there is an adDirectionOutp ut[color=blue]
>somewhere.[/color]

here is the code I am using on the ASP page. I am building only the
where string in HTML (as I want to keep the number of fields searched
variable without having to change the SP or having lots of parameters
in it), from the results I am quite happy with that solution. If I get
time I will read your article which I am sure has even better ways to
do this.

Attached is the code of the ASP page - at this stage myu theory is I am
missing a step between CmdSP.Execute() and reading the parameter -
maybe a refresh of some sorts? Any idea?

tia Axel

Dim CmdSP

Set CmdSP = Server.CreateOb ject("ADODB.Com mand")
'-- Make an ODBC connection to the (local) SQL server,


Dim rs
Dim lRecCount
Set rs = Server.CreateOb ject("ADODB.Rec ordset")

CmdSP.ActiveCon nection = Application("cn n")
CmdSP.CommandTe xt = "spFindProducts "
CmdSP.CommandTy pe = 4 ' adCmdStoredProc

' first parameter is always the return value!!!!!!!!!
CmdSP.Parameter s.Append _
CmdSP.CreatePar ameter("RETURN_ VALUE", adInteger, _
adParamReturnVa lue, 4)

CmdSP.Parameter s.Append _
CmdSP.CreatePar ameter("@mycoun t", adInteger, adParamOutput)


CmdSP.Parameter s.Append _
CmdSP.CreatePar ameter("@whereS tring", adVarChar, _
adParamInput, len(sWhere),
sWhere)
Set rs = CmdSP.Execute( )

lRecCount = CmdSP.Parameter s("@mycount").V alue

===> lRecCount is always empty!!! why?

[Axel]

Re: Why is my Output Parameter not working?

Thanks Mario,

Using SQL QA, I found out in the meantime that the rowcount actually
does work. Its the ASP page that is not able to read it for some
reason. Maybe it works once I close the recordset but I need to display
the search results _after_ displaying the number of records, e.g.

we found XX items
1..
2..
3..
etc.

Only difference in QUery Analyzer is that it displays the search
recordset first, then the contents of @mycount. So it might still be a
chicken and egg situation (SQL Server not being able to count the
records without MoveLast and closing recordset ???????)

[Erland Sommarskog]

Re: Why is my Output Parameter not working?

Axel (realraven2000@ hotmail.com) writes:[color=blue][color=green]
>>I can tell you should do it in ASP, since I don't know how you call the
>> procedure. Assuming that you use ADO, there is an adDirectionOutp ut
>>somewhere.[/color][/color]

That was supposed to read "I can't tell". Nevermind, it's an ADO problem,
and ADO I know something about.

I believe that you are using a server-side cursor. At least when I tried
an example I was not able to retrieve the value of the output parameter
with a server-side cursor. Unless you have very compelling reasons, you
should anyway use client-side cursors, which use specify by setting
cnn.CursorLocat ion to adUseClient. In this case you get the output
parameter.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Axel]

Re: Why is my Output Parameter not working?

Thanks Erland,

I had tried changing tro a client side cursor but it still produces the
same result

(from my global.asa)
<SCRIPT LANGUAGE=VBScri pt RUNAT=Server>
Sub Application_OnS tart

dim cnnDem

Set cnnDem = Server.CreateOb ject("ADODB.Con nection")

cnnDem.CursorLo cation=3 ' adUseClient=3 (Server=2)
Call cnnDem.Open("DS N=TestDB;UID=sa ;PWD=;DATABASE= TestDB")

Application("cn n") = cnnDem

End Sub
</SCRIPT>

it doesn't seem to make any difference. How can I force SQL Server to
fully transmit the recordset (I read in another post that the output
params are not marshalled back until all rs are fully transmitted
[according to Bob Barrows on microsoft.publi c.inetserver.as p.db])? Do I
need to MoveLast or even close the recordset for this? Kind of defeats
the purpose (because I want to iterate after displaying mycount) -
looks like I have to do GetRows and copy to an array if all else fails.

tia
Axel

[Erland Sommarskog]

Re: Why is my Output Parameter not working?

Axel (realraven2000@ hotmail.com) writes:[color=blue]
> I had tried changing tro a client side cursor but it still produces the
> same result
>
> (from my global.asa)
> <SCRIPT LANGUAGE=VBScri pt RUNAT=Server>
> Sub Application_OnS tart
>
> dim cnnDem
>
> Set cnnDem = Server.CreateOb ject("ADODB.Con nection")
>
> cnnDem.CursorLo cation=3 ' adUseClient=3 (Server=2)
> Call cnnDem.Open("DS N=TestDB;UID=sa ;PWD=;DATABASE= TestDB")
>
> Application("cn n") = cnnDem
>
> End Sub
></SCRIPT>[/color]

Is adUseClient really 3? Does ASP not support the use of the constants
instead?

If you use client-side cursor, there is another solution which may be
more palatable: use the .RecordCount property on the recordset.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Axel]

Re: Why is my Output Parameter not working?

>Is adUseClient really 3?
yes, according to Microsoft's ADOVBS_Short.IN C
[color=blue]
>Does ASP not support the use of the constants instead?[/color]
Yes. Either by including aformentioned file (best practise to clip it
down to not have 15 kbyte waste per page / session) or simply declaring
yourself
Const adUseClient = 3

however, it did not support using them "out of the box" - the Server
simply did not understand them, that's why I originally used numerals
in this code. I dislike unreadable code myself so I am sure to change
it once the prototype phase is over.
[color=blue]
> If you use client-side cursor, there is another solution which may be[/color]
more palatable: use the .RecordCount property on the recordset.

I had tried the RecordCount solution as well (didn't work), but can not
remember if I tried it with the client side cursor. I remember under VB
it was a no brainer to use this property but in ASP you probably have
to MoveLast to fill it with a meaningful value - a bit like in DAO
under Access (shudder). I will do some tests once I am back in the
office (monday).

As the bottom line my prior knowledge of ADODB helped with the syntax
but its actual behavior in ASP / IIS varies...

one learns... hopefully I will have time to read the longish article on
dynamic searches until then - somebody from
microsoft.publi c.inetserver.as p.db pointed me to the same one.

regards & have a nice weekend
Axel

[Alejandro Mesa via SQLMonster.com]

Re: Why is my Output Parameter not working?

Axel,

SQL Server send the output parameters and the return value in the last packet
it returns to the client, so in order to acces them, you have to process all
resultsets (or abort pulling the data). See "Parameter Markers" in BOL for
more info.


AMB

--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Re: Why is my Output Parameter not working?

Axel (realraven2000@ hotmail.com) writes:[color=blue]
> I had tried the RecordCount solution as well (didn't work), but can not
> remember if I tried it with the client side cursor. I remember under VB
> it was a no brainer to use this property but in ASP you probably have
> to MoveLast to fill it with a meaningful value - a bit like in DAO
> under Access (shudder). I will do some tests once I am back in the
> office (monday).[/color]

I don't see why ADO would work differently in ASP than i Visual Basic,
but all tests I ever will conduct with ADO will be in Visual Basic, since
ASP is nothing I know.

..RecordCount should work with a static cursor, and as well with a keyset.
Don't remember off-hand which is the default cursor type when you use
server-side cursor, but I would guess for fast forward, and you can of
course not get a direct .RecordCount with these. Client-side cursors
are always static.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp