CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
hashim
hashim

Reputation: 69

Create and execute stored procedure in SQL Server

I have four tables:

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.

Upvotes: 1

Views: 67

Answers (2)

Romeo Gresta
Romeo Gresta

Reputation: 116

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'

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 2

Related Questions