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.


.

Relevant Pages

  • Newbie - Stored Procedures/ASP Question
    ... I've created and tested a Stored Procedure in SQL Server 2000, ... It stores data to 2 tables, and returns an output parameter and result code. ... However, when I try to call it from ASP, nothing seems to happen. ...
    (microsoft.public.inetserver.asp.db)
  • Re: sp does not return right value but query analyzer does
    ... -- the first statement in the stored procedure should be "SET NOCOUNT ... -- Create an output parameter in the stored procedure instead ... How can I see what's being sent to SQL ... > Dim conMyData As SqlConnection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OLEDB consumer and for xml SELECT
    ... Query Analyzer uses ODBC, ... In SQL 2005, there is a new command-line tool SQLCMD which is implemented ... If the stored proc generates a FOR XML ... That creates a class ready to run the stored procedure and return ...
    (microsoft.public.sqlserver.xml)
  • Re: "IF" test in SP evals to True and False at same time using DataAdapter.FillSchema()
    ... see that the SQL that's printed doesn't look like it should produce the ... I would try two things - run the sp from query analyzer, ... >interaction issue between MS-SQL and MS .NET FillSchema() ... >Code Snip of Stored Procedure that is failing ...
    (microsoft.public.sqlserver.programming)
  • Re: Ad Hoc Query in C#
    ... Rather than using SQL like this just build a stored procedure that contains ... > This command exceptions with an "Incorrect syntax near 'GO'". ... when I put the EXACT same query into Query Analyzer like this: ...
    (microsoft.public.dotnet.general)