为什么输出参数不起作用？ [英] Why is my Output Parameter not working?
问题描述
我目前正在创建一个ASP页面，它返回一个搜索记录集
基于多个关键字的结果。 where字符串是在服务器页面上构建的动态
，该部分工作得很好。但是我想要
也能返回记录集中的记录数量而我无法使用
设置任何输出参数。即使我在SP中指定SELECT
@ mycount = 100（或SET @ mycount = 100），
mycount中设置的唯一值也始终为NULL。我测试了各种理论（例如提前退出，
命令和命名参数等等但我不能让SP设置
输出参数 - 有什么可做的吗与执行？）。 @mycount
如果我在SQL QA中测试它，
也会返回NULL。
这个SP有什么问题（关于mycount）：
CREATE PROCEDURE dbo.spFindProducts
@mycount整数输出，
@whereString varchar（1000）
AS
- 支付NOCOUNT ON
- 设置Wherestring的默认值，它将返回所有记录
如果Wherestring是空白的
如果@whereString为空
SELECT @whereString =''AND TblProduct.ProductID不为空''
--Declare一个变量来保存连接的SQL字符串
DECLARE @SQL varchar（2500）
- AND（（（UPPER（ [TblProduct]。[ProductName] + [ProductGroupCode] +
[AttributeValue1] +
- [SearchWords] + [tblSupplier]。[SupplierCode] + [SupplierDesc]） ）
喜欢''％screw％''））
SELECT @SQL =''SELECT TblProduct.ProductID，TblProduct.ProductName，
TblProduct.ChapterCode，tblProduct.ProductGroupCode''
>
''FRN（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; - 测试
执行（@SQL）;
- 下一行似乎被忽略了？
SELECT @mycount = @@ rowcount;
GO
tia
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.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 ?
SELECT @mycount = @@rowcount;
GO
tia
Axel
推荐答案
Axel（re*********** @ hotmail.com）写道：
Axel (re***********@hotmail.com) writes:
我正在创建一个返回记录集的ASP页面搜索结果基于多个关键字的结果。 where字符串是在服务器页面上动态构建的，并且该部分工作得很好。但是我想要
也返回记录集中的记录数量，我无法设置任何输出参数。即使我在SP中指定SELECT
@ mycount = 100（或SET @ mycount = 100），
mycount中设置的唯一值也始终为NULL。我测试了各种理论（例如提前退出，
命令和参数命名等，但我不能让SP设置
输出参数 - 它与执行有什么关系吗？）。 @mycount
如果我在SQL QA中测试它也会返回NULL。
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.
这更可能与您调用该过程的方式有关。你要
必须将参数指定为OUTPUT。在T-SQL中：
EXEC spFindProducts @cnt OUTPUT，@ string
我可以告诉你应该在ASP中这样做，因为我不喜欢你不知道如何调用
程序。假设你使用ADO，那里有一个adDirectionOutput
。
然后再说一遍，如果这是你对存储过程的想法，我想你是
最好停止使用存储过程并在客户端生成所有
SQL。这只会增加你的复杂性，但你什么都不赢。
我在我的网站上有一篇文章讨论了各种技术
来实现动态搜索，可能对您有用：
http：//www.sommarskog。 se / dyn-search.html
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/ books.asp
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, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Axel，
试试这个...
返回@@ rowcount
存储过程返回一个整数作为返回码，用于
你想要的任何东西!!!
Axel,
Try this...
Return @@rowcount
Stored procedure returns an integer as a return code, use it for
anything you want!!!
对不起，
我刚注意到你哟你正在执行一个sql语句。
在这种情况下，请记住为这个
执行创建一个全新的环境，并且与你当前存储的程序无关。 />
因此@@ rowcount上的空值...
执行此操作的唯一方法是将输出发送到#table，然后执行
从#table中选择count（*）。
所以，选择#table进行选择它会起作用......
是的，执行sql非常棘手.. 。
祝你好运！
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!
这篇关于为什么输出参数不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
