SP - output parameter not coming back

Hi

I have a stored procedure that returns a recordset with search results.
I also want it to return the number of records in an output parameter.
The problem is it does not seem to transmit anything to the server page
- what could be wrong? (just for test purposes I tried to assign a
fixed value, or change the order of parameters but it is simply not
read) - instead it seems to assign Null no matter what I tried. The
recordset is returned as expected though. I changed the cursor type to
client side in order to get the correct count.

Also is there any way I can test output params in SQL Query Analyzer?

thanks in advance
Axel

---------- Code follows --------

Stored Proc code:
CREATE PROCEDURE dbo.spFindProducts
@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.ProductID is not null'

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

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

SELECT @SQL = 'SELECT TblProduct.ProductID, TblProduct.ProductName,
TblProduct.ChapterCode, tblProduct.ProductGroupCode' +
' FROM (TblProduct LEFT JOIN TblStockItem ON TblProduct.ProductID =
TblStockItem.ProductID) ' +
' LEFT JOIN tblSupplier ON TblProduct.SupplierCode =
tblSupplier.SupplierCode' +
' WHERE 1=1 ' + @whereString +
' GROUP BY TblProduct.ProductID, TblProduct.ProductName,
TblProduct.ChapterCode, TblProduct.ProductGroupCode'

SELECT @mycount = 200; -- test

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

Connection code (global.asa)


search result page (*)
<%@ Language=VBScript %>


<%
Dim strSQL
Dim sSearch

Dim sWhere
Dim sWhereStart
Dim sSP

sSearch = Request("txtSearch")

Dim sArg

dim arr
arr=Split(Trim(sSearch)," ")

sWhere = ""
' AG insert ' ' as escape sequence to avoid false positives
' e.g. 'ab'+'cd' would be caught when searching for 'bc'
' ab cd does not create a false positive with this
sWhereStart = " AND (((UPPER([TblProduct].[ProductName]+'
'+[ProductGroupCode]+' '+[AttributeValue1] +" & _
" [SearchWords]+' '+[tblSupplier].[SupplierCode]+'
'+[SupplierDesc])) Like "


for x=0 to UBound(arr)
%>
arr(<%=x%>)=<%=arr(x)%>

SQL String: <%=strSQL%>

Re: SP - output parameter not coming back

Axel wrote:
> Hi
>
> I have a stored procedure that returns a recordset with search
> results. I also want it to return the number of records in an output
> parameter. The problem is it does not seem to transmit anything to
> the server page - what could be wrong? (just for test purposes I
> tried to assign a fixed value, or change the order of parameters but
> it is simply not read) - instead it seems to assign Null no matter
> what I tried. The recordset is returned as expected though. I changed
> the cursor type to client side in order to get the correct count.
>
> Also is there any way I can test output params in SQL Query Analyzer?

http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.general/msg/2c935bd7c531d82b

>
> thanks in advance
> Axel
>
> ---------- Code follows --------
>
> Stored Proc code:
> CREATE PROCEDURE dbo.spFindProducts
> @mycount integer OUTPUT,
> @whereString varchar (1000)
> AS
>
> --SET NOCOUNT ON

Why do you have this commented out? You need to have this turned on to avoid
having your procedure return extra resultsets containing the number of
records affected by each statement. This statement being turned on will NOT
affect the contents of @@ROWCOUNT



>
> Set CmdSP = Server.CreateObject("ADODB.Command")
> '-- Make an ODBC connection to the (local) SQL server,
>

Don't use ODBC:
http://www.aspfaq.com/show.asp?id=2126

>
> Dim rs
> Dim lRecCount
> Set rs = Server.CreateObject("ADODB.Recordset")
>
> CmdSP.ActiveConnection = Application("cnn")

If you are storing a Connection object in Application, that is a bad idea:
http://www.aspfaq.com/show.asp?id=2053


If Application("cnn") is a connection string, then you are still using a bad
practice: ALWAYS use an explicit Connection object so as not to disable
connsection/session pooliing:

set cn=createobject(adodb.connection")
cn.open Application("cnn")
Set CmdSP.ActiveConnection = cn

> CmdSP.CommandText = "spFindProducts"
> CmdSP.CommandType = 4 ' adCmdStoredProc
>

You forgot to append the return parameter. Even though you are not using it,
it must be the first parameter appended to the collection.

>
> ' CmdSP.Parameters.Append _
> ' CmdSP.CreateParameter("@mycount", adInteger, adParamOutput)
> CmdSP.Parameters.Append _
> CmdSP.CreateParameter("@mycount", 3, 2)
>
> ' CmdSP.Parameters.Append
> CmdSP.CreateParameter("@whereString",adVarChar=200, 1, len(sWhere),
> sWhere) ' adVarChar, adParaminput, ...)
> CmdSP.Parameters.Append _
> CmdSP.CreateParameter("@whereString", adVarChar, _
> adParamInput, len(sWhere), sWhere)
>
>
> ' RECORDSET OBJECT
> ' Open recordset (from stored Proc)
> Dim dummyArr
> Set rs = CmdSP.Execute( ) ' RecordCount can not be used
> ' set cursor type to clientside for this to work? =>
> ' @mycount: THIS PARAMETER EXISTS, BUT IT HAS NO VALUE FOR SOME
> ' UNKNOWN REASON!
> ' tried: changing param order
> ' assigning constant in SP
> ' not executing & filling the recordset
> ' seems to always pass back empty
> ' append identifier ".Value"
> lRecCount = CmdSP.Parameters("@mycount").Value
>
>
> ' in order to use recordcount we need to
> ' set the cursortype to something different than forward only
> ' then movelast - get RecordCount and MoveFirst...
>

Output and Return parameter values are not sent until all the resultsets
generated by the procedure are sent to the client. This means that you must:
1. When using forward-only cursors, close your recordset before attempting
to read the output and return parameters
2. When using a client-side cursor, the entire static resultset is sent when
the recordset is opened, so the output and return parameters are immediately
sent.

You may be interested in my free stored procedure code generator available
here:
http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links .asp&c=&a=clear

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SP - output parameter not coming back

Thanks Bob,

lots of very interesting reading in all the links that you sent. A lot
of the code I use is copy and paste so I will try and follow your
advice on OLEDB vs ODBC and the one about not using objects in session
variables, should be easy enough to change these. I also enabled SET
NOCOUNT ON again, I had tested it with both options.

I also added the return parameter to my command object (1st param
added)
CmdSP.Parameters.Append _
CmdSP.CreateParameter("RETURN_VALUE", adInteger, _
adParamReturnValue, 4)
and I thought that this would actually solve my problem. but still I do
not get anything useful back from @mycount.

Also I have tested the SP using SQL Query Analyzer and it does not seem
to set the output parameter. Looks like there is either something wrong
in my SP Syntax or some underlying setting of the Server (is there a
setting that disables output parameters - or maybe it has to do with
the passed back recordset?)

I do not want to close the recordset because I want to display the
number of results before the table of results. I also do not want to
copy to a Array. The client side cursor did not help.

If I do not find out about the output parameter I am considering
running 2 separate stored Procedures (1 to get the count of my query in
a separate resultset, the other for the actual search results) but I
know that this is a most unfortunate and expensive solution. At least
this is only a product catalogue so the info in it is not highly
volatile. However it might even be technically better than using a
client side cursor - what do you think?

tia again
Axel

Re: SP - output parameter not coming back

Just one more info = just managed to test with SQL Query Analyzer, I
changed the test script to include output and I can now verify that the
SP is working correctly, filling in the output parameter; this means
the problem is reading it back. Am I missing a step between

Set rs = CmdSP.Execute( ) ' RecordCount can not be used

and

lRecCount = CmdSP.Parameters("@mycount").Value

maybe a refresh of sorts?

thx
Axel

Re: SP - output parameter not coming back

Axel wrote:
> Thanks Bob,
>
> lots of very interesting reading in all the links that you sent. A lot
> of the code I use is copy and paste so I will try and follow your
> advice on OLEDB vs ODBC and the one about not using objects in session
> variables, should be easy enough to change these. I also enabled SET
> NOCOUNT ON again, I had tested it with both options.
>
> I also added the return parameter to my command object (1st param
> added)
> CmdSP.Parameters.Append _
> CmdSP.CreateParameter("RETURN_VALUE", adInteger, _
> adParamReturnValue, 4)
> and I thought that this would actually solve my problem. but still I
> do not get anything useful back from @mycount.
>
> Also I have tested the SP using SQL Query Analyzer and it does not
> seem to set the output parameter. Looks like there is either
> something wrong in my SP Syntax or some underlying setting of the
> Server (is there a setting that disables output parameters - or maybe
> it has to do with the passed back recordset?)

If it does not work in query analyzer, it has no chance of working in asp

>
> I do not want to close the recordset because I want to display the
> number of results before the table of results. I also do not want to
> copy to a Array.

Not sure why you have an objection to this. See:
http://www.aspfaq.com/show.asp?id=2467
and
http://www.aspfaq.com/show.asp?id=2193

> The client side cursor did not help.

If it does not work in query analyzer, it has no chance of working in asp

>
> If I do not find out about the output parameter I am considering
> running 2 separate stored Procedures (1 to get the count of my query
> in a separate resultset, the other for the actual search results) but
> I know that this is a most unfortunate and expensive solution. At
> least this is only a product catalogue so the info in it is not highly
> volatile. However it might even be technically better than using a
> client side cursor - what do you think?
>

You definitely need to work on the stored procedure so that you can see the
result of the output parameter in query analyzer.

Oh wait! I just looked at your stored procedure. You're using dynamic sql in
your stored procedure?!? That is so wrong!
(http://www.sommarskog.se/dynamic_sql.html)

You don't even need it for your purpose! Get rid of it! Forget about this
Wherestring stuff! You are leaving yourself open to hackers using sql
injection attacks:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf


Using exec(@sql) causes the sql statement to be executed in a different
scope. That is why your @mycount variable isn't getting populated. Read this
article for alternatives to dynamic sql:
http://www.sommarskog.se/dyn-search.html

There are several other good articles on that site so make sure you do some
browsing.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SP - output parameter not coming back

Axel wrote:
> Just one more info = just managed to test with SQL Query Analyzer, I
> changed the test script to include output and I can now verify that
> the SP is working correctly, filling in the output parameter; this
> means the problem is reading it back. Am I missing a step between
>
> Set rs = CmdSP.Execute( ) ' RecordCount can not be used
>
> and
>
> lRecCount = CmdSP.Parameters("@mycount").Value

Yes, you're failing to retrieve the entire resultset, either by moving to
the last record or by closing the recordset. SQL Server will not send the
output parameter value UNTIL all the resultsets generated by your stored
procedure have been sent (if your procedure returns multiple resultsets, for
example, if you don't SET NOCOUNT OFF - you will need to use NextRecordset
to cycle through the resultsets until you've retrieved the last one. Only
then will SQL Server send the output parameter value)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SP - output parameter not coming back

Thanks a lot again,

More valuable information, but also more complication. Basically I need
to go through the whole recordset before I can access the output
parameter; I might as well copy all the data and count myself. Or is
there another alternative? (The reason I am still shying away from
copying the rs into a local array is the typeless programming in ASP at
least the data is nicely structured and neatly packaged while in the
recordset)

I still do not understand the NextRecordset bit, I am only asking for
one resultset (my search results), are you saying all output parameters
are in a separate recordset as well? If this is the case is there any
way to return this recordset FIRST? All I want is the sum of records
displayed before the table of results, which to the newbie (or the
seasoned ADO programmer used to working in C++ / VB / Access
environment) may sound pretty trivial. Also might it be possible to use
the returnvalue instead, would that buy me anything? I think it is
already safe to say that normal functional and object oriented
programming is a lot easier; when a function returns it returns, and
you can trust its parameters... ;-)

I think the main problem is time as well I have already spent about 1.5
days at this and it is getting out of scope, On the other hand I am
eager to learn so I will spend my weekend reading up on some of the
other links you provided. A lot of my code is developed from other MVPs
/ Microsoft and different articles (Extreme UltraDev, Aspin,
aspfree.com etc.) and it is not always easily possible for the newbie
to distinguish good from mediocre advice, so thanks again for you
patience.

cheers

Re: SP - output parameter not coming back

Axel wrote:
> Thanks a lot again,
>
> More valuable information, but also more complication. Basically I
> need to go through the whole recordset before I can access the output
> parameter; I might as well copy all the data and count myself. Or is
> there another alternative?
One of the links I provided earlier has the answer to this.
http://www.aspfaq.com/show.asp?id=2193


> (The reason I am still shying away from
> copying the rs into a local array is the typeless programming in ASP
> at least the data is nicely structured and neatly packaged while in
> the recordset)

I suspect you are putting the recordset to more use than it should be put in
ASP if you are worrying so much about datatypes. In ASP, recordsets should
only be used to retrieve read-only data for display/calculation purposes
only. A GetRows array lends itself quite nicely for this purpose.
http://www.aspfaq.com/show.asp?id=2467

>
> I still do not understand the NextRecordset bit, I am only asking for
> one resultset (my search results), are you saying all output
> parameters are in a separate recordset as well?

No. I am saying that IF the stored procedure returns multiple resultsets,
then all the resultsets must be sent to the clietn before output and return
parameter values are sent. Not using "SET OCOUNT ON" is a good way of
inadvertantly guaranteeing that multiple resultsets will be generated,
requiring the use of NextRecordset to process them all. NextRecordset ONLY
needs to be used if the procedure generates multiple resultsets.

> If this is the case

No, it is not the case, but ...

> is there any way to return this recordset FIRST?

No. I'm repeating myself, yet again. SQL Server waits until all resultsets
generated by the procedure are completely sent to the client before sending
output and return parameter values. There is no way to alter this behavior.

With a read-only server-side cursor, the entire resultset is NOT sent until
the last record in the resultset is loaded in the cursor. With a client-side
static cursor, the output and retrn parameters are available immediately
after opening the recordset. If you have a repro, perhaps using Northwind of
a situation where this is not the case, I would be interested in seeing it.

> All I want is the
> sum of records displayed before the table of results, which to the
> newbie (or the seasoned ADO programmer used to working in C++ / VB /
> Access environment) may sound pretty trivial.

Please re-read the article about recordset iteration I cited in my previous
reply (http://www.aspfaq.com/show.asp?id=2467). Using a GetRows array is a
very convenient way of achieving both of your goals. My usual procedure is
to open a recordset, immediately read its contents into an array using
GetRows, close the recordset, read te output and return parameter values
(which you would not need in this instance because you are only using the
output parameter to return the rowcount, something which can easily be
determined by using ubound on your GetRoes array), close and destroy te
connection and command objects, then process the array

> Also might it be
> possible to use the returnvalue instead, would that buy me anything?

No, it will not buy you anything. Again: SQL Server waits until all
resultsets generated by the procedure are completely sent to the client
before sending output and return parameter values. There is no way to alter
this behavior.

In general, while the return value can be used to return data, the general
convention is to use output parameters to return data, and return parameters
to return status/error codes.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SP - output parameter not coming back

>Oh wait! I just looked at your stored procedure.
>You're using dynamic sql in your stored procedure?!?
> That is so wrong!
I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/ultradev/tutorial12.html
so are you saying this is probably all b*s*...?

> You don't even need it for your purpose! Get rid of it!
I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.

Here is what I have achieved in this (as you stated, wrong) way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.

Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (e.g. "AND f1+' '+f2+' '+f3... LIKE %%)
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins,
resultset format and grouping.)

I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.

What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.

Cheers
Axel

Re: SP - output parameter not coming back

Axel wrote:
>> Oh wait! I just looked at your stored procedure.
>> You're using dynamic sql in your stored procedure?!?
>> That is so wrong!
> I got this technique from an Extreme Ultradev tutorial by Rick Curtis
> it looked quite ok:
> http://www.princeton.edu/~rcurtis/ultradev/tutorial12.html
> so are you saying this is probably all b*s*...?

I would say "ill-advised" rather than "b*s*". It works of course, but as
you'll see later on, you are leaving yourself open to unintendd
consequences.

>
>> You don't even need it for your purpose! Get rid of it!

> I have to admit, one of the main reason for passing the WHERE string
> is that I do not know how to do the string splitting / parsing and
> putting together in a Stored Procedure. I bet T-SQL would be just as
> powerful as VBScript if I just knew it well enough.
>


> I bet there is a way to move 1. 2. and 3. into the SP (and I would
> feel better if it was) but I don't have the expertise to do this. If
> anybody wants to help me this is very welcome.

See Erland's article about dynamic search conditions on this page:
http://www.sommarskog.se/

Erland is a well-respected SQL Server MVP so you can be sure his ideas have
the endorsement of SQL Server professionals both at Microsoft and in the
Usenet community.

>
> What I liked about having built them on the web script was the
> flexibility allowing to potentially build an advanced search without
> having to change the stored procedure -

And that's what hackers like about this technique too. See:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"