|
|
Posted by Erland Sommarskog on 06/15/05 13:51
Axel (realraven2000@hotmail.com) writes:
> 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.
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 adDirectionOutput
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@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|