Create and execute stored procedure with parameter in SQL Server

debugcn Published at Dev

hashim

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'

This is my code I tried but it does not work ,not sure if it true or false, this is the result from execute this procedure:

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

i want it to be like this :
ID DistrictsName PurposeName 
 1   District1    residential
 2   District2    residential

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'

I'm new to SQL Server so any help is appreciated.

Nick.McDermaid

Based on your sample data you are not interested in the project id - in your sample it has no correlation to any project id. So this is one way to do it.

SELECT 
    dbo.Districts.DistrictName,
    'Residential' As PurposeName
FROM 
    dbo.Districts 
WHERE EXISTS (
    SELECT * 
    FROM 
        dbo.Projects 
    INNER JOIN 
        dbo.Purpose ON dbo.Projects.PurposeID = dbo.Purpose.ID 
    WHERE dbo.Projects.DistrictID = dbo.Districts.ID
    AND dbo.Purpose.PurposeName = N'Residential'
    )

But I think it probably isn't the asnwer you're after, it just helps us highlight what you really want

If you want id to be an arbitrarily increasing number, we can do that but first please tag the version of SQL Server

Collected from the Internet

Please contact [email protected] to delete if infringement.