How to get count in output variable by store procedure?

Question

0.00/5 (No votes)

See more:

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.

Posted 24-Apr-16 19:40pm

Vikas Hire

Updated 24-Apr-16 23:41pm

Add a Solution

Comments

Prasad Khandekar 25-Apr-16 2:04am

Hello,

Create a stored procedure and loop through the cursor and assign values to the output variable. I do hope that you have a finite set of RequestStatusID and hence you know the number of output parameters to be passed.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2016-04-24T23:41:00

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

SQL

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:

SQL

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'

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