Create and execute stored procedure in SQL Server with parameter

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 projects where DistrictsName='District1' and PurposeName = 'residential'

I tried this procedure :

alter PROCEDURE GetProjects
    @districtName nvarchar(50),
    @purposeName nvarchar(50)
as
    SELECT 
        p.ID, p.ProjectName,  p.Areas, 
        p.PaymentSystem, p.ReceivedDate,    
        p.PropertyClassification, 
        p.ProjectImage,         
        dis.DistrictName,
        Pur.PurposeName            
    FROM 
        dbo.Projects AS p 
    LEFT JOIN 
        dbo.Districts dis ON p.DistrictID = dis.ID   
    LEFT JOIN  
        dbo.Purpose pur ON p.PurposeID = pur.ID 
    WHERE 
       (dis.DistrictName = @districtName) 
       AND (Pur.PurposeName = @purposeName)

exec GetProjects @districtName='District1', @purposeName='residential '

When I execute this procedure, it returns with null - no data is returned.

Note: database supported Arabic language

When I execute this query it returns the result I think no data returned because Arabic language

SELECT 
    p.ID, p.ProjectName, p.Areas, 
    p.PaymentSystem, p.ReceivedDate,    
    p.PropertyClassification, 
    p.ProjectImage,         
    dis.DistrictName,
    Pur.PurposeName            
    FROM 
        dbo.Projects AS p 
    LEFT JOIN 
        dbo.Districts dis ON p.DistrictID = dis.ID   
    LEFT JOIN  
        dbo.Purpose pur ON p.PurposeID = pur.ID 
    WHERE 
        (dis.DistrictName = N'arabic value') 
        AND (Pur.PurposeName = N'arabic value')

How about remove the last blank character in purposename? ...,@purposeName='residential' — DVT
– DVT, Commented Dec 20, 2016 at 13:58
And you might as well change the two LEFT JOIN to INNER JOIN. The WHERE clause turn them to INNER JOIN any way. — DVT
– DVT, Commented Dec 20, 2016 at 14:00
still no data return when i remove ...,@purposeName='residential' — hashim
– hashim, Commented Dec 20, 2016 at 14:04
Do you have any data where the districtname = 'District1'? I don't see anything wrong with your query so it must be data. — Sean Lange
– Sean Lange, Commented Dec 20, 2016 at 14:05
Then run your stored procedure with this: exec GetProjects @districtName=N'arabic value',@purposeName=N'arabic value' and see if you get result? The problem might be in the Unicode things. — DVT
– DVT, Commented Dec 20, 2016 at 14:19

DVT · Accepted Answer · 2016-12-20 14:26:50Z

1

Use this execution command

exec GetProjects @districtName=N'arabic value',@purposeName=N'arabic    value'

Put the N in front to indicate you are using Unicode.

answered Dec 20, 2016 at 14:26

DVT

3,1274 gold badges16 silver badges25 bronze badges

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

2 Comments

hashim Over a year ago

this command return the result i want but i want to remove this static value and make it parameter

DVT Over a year ago

Keep the stored procedure "as is" (as in as before you posted the question). Then run it using the parameter @districName=N'<<your value here>>>', @purposeName=N'<<<your value here>>>'. If it does not return value, you do not have value for that parameter. Simple as that.

Collectives™ on Stack Overflow

Create and execute stored procedure in SQL Server with parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related