如何通过存储过程计算输出变量？ [英] How to get count in output variable by store procedure?

问题描述

SELECT COUNT（*）FROM tblRequest GROUP BY RequestStatusID



此查询返回具有总请求的列各自的状态ID。



我希望使用存储过程在输出变量中获得与其statusId相对应的总请求。

即它返回一个列如

______________

| NoNameColumn |

| ----------- |



| - --------- |

6

| ----------- |

14

| ------ ---- |





我想获得每一行的价值在输出变量中单独使用（例如，SET @ pending = 21，Set @ Sent = 6，SET Discarded = 14），使用存储过程和vb.net代码。



简单来说..如何通过sql store程序在变量中获取该行的值？



我该怎么做...？



我尝试了什么：



我有一个带有标签的vb.net表格待发电子邮件，De真实的电子邮件，失败的电子邮件等，它显示了请求中的电子邮件状态tblRequest。

我将创建存储过程，获取所有计数器并将其返回到输出变量。我可以在vb.net表单中将存储过程的返回值分配给标签。

SELECT COUNT(*) FROM tblRequest GROUP BY RequestStatusID

This Query return the Column having total requests respective status Id.

I want to get total requests respective to its statusId in a output variable using store procedure.
i.e. it return a column like
______________
|NoNameColumn|
|-----------|
21
|-----------|
6
|-----------|
14
|------ ----|


I Want to get each row value in a output variable separately(eg. SET @pending=21, Set @Sent=6, SET Discarded=14 ), using store procedure and vb.net code.

In simple words.. How can i get that row's values in a variable by the sql store procedur?

How can I Do It..?

What I have tried:

I have a vb.net form having labels Like Pending Email, Delivered email, failed email etc. which show the status of emails in a request tblRequest.
I am going to make store procedure which get all counters and return it into output variable. which can I assign the the return values from store procedure to lables at the vb.net form.

推荐答案

要将每个结果分配给不同的变量，请逐个执行像这样：

To assign each result to a different variable do it one by one like this:

SELECT @pending=COUNT(*) FROM tblRequest WHERE RequestStatusID='pending'
SELECT @sent=COUNT(*) FROM tblRequest WHERE RequestStatusID='sent'

或者你可以将结果存储在一个表变量的查询中：

Alternatively you can store the results in a table variable a query that:

DECLARE @tmp TABLE(RequestStatusID ???, [Count] INT)

INSERT INTO @tmp
SELECT RequestStatusID, COUNT(*) FROM tblRequest GROUP BY RequestStatusID

SELECT @pending=[Count] FROM @tmp WHERE RequestStatusID='pending'
SELECT @sent=[Count] FROM @tmp WHERE RequestStatusID='sent'

第三个选项是解析数据集i在你的应用层，IMO是最好的解决方案。

A third option would be to parse the dataset in your application layer which IMO is the best solution.

这篇关于如何通过存储过程计算输出变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！