Stored procedure returning selected values

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have Two Tables In Database One is Group table and the other is Status table.Both have GroupId common.So I will inner Join Them I will Get all the records. Status Table has Status column in the table, that status column has some 6 columns like 1)active 2)not active 3)pending 4)approved 5)placed 6)unplaced Now all the records has one column related to status column..

I need a stored procedure that will get the records like if it is active we will get the record on inner joining both tables, if it is placed status we will get the placed column records so on,, if user doesn't select anything we will get all records. This i will bind to dropdown list and those values will be displayed in gridview in front end..

can u please help me with the stored procedure to do like dis please help?? any suggestions ??

What I have tried:

--if status = Active select a.GroupName,b.Brand,b.model,b.serialno , b.Status from status b inner join Group a on a.GroupId = b.GroupId where Status = 'Active'

Posted 19-Feb-17 20:55pm

Mahesh2223

Updated 20-Feb-17 0:20am

v2

Add a Solution

2 solutions

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)

#realJSOP · Answer 1 · 2017-02-20T00:18:00

0) You don't need the if clause:

SQL

SELECT a.Brand,
       a.model,
       a.serialno, 
       a.Status,
       b.GroupName
FROM status a 
INNER JOIN Group b ON a.GroupId = b.GroupId 
WHERE a.Status = 'Active'

1) Your table names are the same as some of your column names. That's confusing.

2) Your aliases are not real indicative of which table they represent. I suggest using the first letter of the name of the table instead.

kambingkoh · Answer 2 · 2017-02-20T00:20:00

Solution 2

CREATE PROCEDURE GetGroupStatus
/* Pass in Status to this Stored Procedure. By default set to empty string (''). If no Status is passed in, @Status = '', and we will
then return all the results for different Status. */
@Status AS VARCHAR(10) = ''
  
AS  

IF (@Status <> '')
	SELECT A.GroupName, B.Brand, B.model, B.serialno, B.Status from [Status] B
	INNER JOIN [Group] A ON A.GroupId = B.GroupId WHERE B.Status = @Status
ELSE
	SELECT A.GroupName, B.Brand, B.model, B.serialno, B.Status from [Status] B
	INNER JOIN [Group] A ON A.GroupId = B.GroupId

Posted 20-Feb-17 0:20am

kambingkoh

Comments

Mahesh2223 20-Feb-17 8:35am

Like Active I have 5 more Cases, How We Write For That?

kambingkoh 20-Feb-17 9:48am

try union.

kambingkoh 20-Feb-17 9:49am

Something like:
SELECT A.GroupName, B.Brand, B.model, B.serialno, B.Status from [Status] B
INNER JOIN [Group] A ON A.GroupId = B.GroupId WHERE B.Status = 'Active'
UNION
SELECT A.GroupName, B.Brand, B.model, B.serialno, B.Status from [Status] B
INNER JOIN [Group] A ON A.GroupId = B.GroupId WHERE B.Status = 'Pending'

kambingkoh 20-Feb-17 9:59am

But you said you want a stored procedure, where are you going to pass in the Status such as "Active", "Not Active", etc...? I assume you are using ASP.NET WebForm, so in your code-behind, just call this stored procedure by passing in a value for the @Status if you want records of a particular status to be returned, if not, just call the stored procedure as it is and all records will be returned.