Create and execute stored procedure in SQL Server

Question

I have four tables:

dbo.Projects (id, ProjectName, Areas, PaymentSystem, Districts.id, purpose.id, types.id, etc)
dbo.Districts(id, DistrictsName)
dbo.Purpose (id, PurposeName) - has residential & commercial
dbo.Types (id, typName)

I want to select DistrictsName where PurposeName = 'residential'

I tried this procedure :

CREATE PROCEDURE [dbo].[SearchResidentialProjects]   
AS
SELECT 
    dbo.Projects.ID,
    dbo.Districts.DistrictName,
    dbo.Purpose.PurposeName
FROM 
    dbo.Projects 
INNER JOIN 
    dbo.Purpose ON dbo.Projects.PurposeID = dbo.Purpose.ID 
INNER JOIN  
    dbo.Districts ON dbo.Projects.DistrictID = dbo.Districts.ID
WHERE 
    dbo.Purpose.PurposeName = N'Residential'

this is the result from this procedure:

ID DistrictsName PurposeName 
 1   District1    residential
 2   District1    residential
 3   District2    residential
 4   District2    residential

i want display the DistrictsName without duplicate or with different values , i a have also one more project per district in projects records . this what i want to display :

ID DistrictsName PurposeName 
 1   District1    residential
 2   District2    residential

how i get this result , any help is appreciated.

What is the purpose of the ID column in your preferred result? You can't link it back to the original record. Do you just need some sort of count? — JeffO
– JeffO, Commented Dec 19, 2016 at 13:38

Gordon Linoff · Accepted Answer · 2016-12-19 13:17:27Z

2

Why do people use stored procedures when views are much more appropriate? I have never understood this. It seems peculiar to SQL Server users.

In any case, you can do what you want with aggregation:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as id, 
       d.DistrictName, p.PurposeName
FROM dbo.Projects pr INNER JOIN 
     dbo.Purpose pu
     ON pr.PurposeID = pu.ID INNER JOIN  
     dbo.Districts d
     ON pr.DistrictID = d.ID
WHERE pu.PurposeName = N'Residential'
GROUP BY d.DistrictName, p.PurposeName;

The use of table aliases makes the query much easier to write and to read.

In addition, I don't understand the id column being output. Why would you want to construct a new id? In any case, that is what your data suggests.

answered Dec 19, 2016 at 13:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hashim Over a year ago

Thank you very much

Panagiotis Kanavos Over a year ago

I haven't met any SQL Server users that prefer stored procedures to views. I have seen questions from users of other databases that migrated to SQL Server that think that they need stored procedures. It's as if people get the idea that "if it's on the server, it's a stored procedure". Or they get bitten by the ancient fallacy that somehow stored procedures are faster because they are stored procedures ...

Romeo Gresta · Accepted Answer · 2016-12-19 13:20:30Z

-1

Use DISTINCT statement for removing the duplicates:

CREATE PROCEDURE [dbo].[SearchResidentialProjects]   
AS
SELECT DISTINCT
    dbo.Projects.ID,
    dbo.Districts.DistrictName,
    dbo.Purpose.PurposeName
FROM 
    dbo.Projects 
INNER JOIN 
    dbo.Purpose ON dbo.Projects.PurposeID = dbo.Purpose.ID 
INNER JOIN  
    dbo.Districts ON dbo.Projects.DistrictID = dbo.Districts.ID
WHERE 
    dbo.Purpose.PurposeName = N'Residential'

answered Dec 19, 2016 at 13:20

Romeo Gresta

1165 bronze badges

1 Comment

Sankar Over a year ago

distinct won't help here. because dbo.Projects.ID is unique.

Collectives™ on Stack Overflow

Create and execute stored procedure in SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related