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

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.

Stored procedure returning selected values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Stored procedure returning selected values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us