Younes Koupaie Younes Koupaie - 17 days ago 6
ASP.NET (C#) Question

Stored procedure returns 0 record with persian text with ی

I have a stored procedure shown below. When I send a parameter that contains persian letter 'ی' it does not return any record.
I used N before but it did not work.

ALTER PROCEDURE [itnarenj.ir_DB].[sp_Acceptor_SelectByDynamicField_ForChart]
@ProjectType NVARCHAR(50)=NULL,
@FromDate datetime=NULL,
@ToDate datetime=NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Where VARCHAR(MAX),
@SQL VARCHAR(MAX)

SET @Where = ' WHERE 1=1 '

IF ( @ProjectType IS NOT NULL )
SET @Where = @Where + ' AND ProjectType = N''' + @ProjectType + ''' '

IF ( @FromDate IS NOT NULL AND @ToDate IS NOT NULL )
SET @Where = @Where + ' AND PmDate BETWEEN ''' + CONVERT(nvarchar(50),(@FromDate),101) + ''' AND ''' + CONVERT(nvarchar(50),@ToDate, 101) + ''' '

SET @SQL = 'SELECT * From Acceptor ' + @Where

EXEC (@SQL)

END

Answer

Not sure it this will fix the problem you are asking about, but there is a better way to write this stored procedure. As is, your stored procedure is vulnerable to SQL Injection attacks.
Try this instead:

ALTER PROCEDURE [itnarenj.ir_DB].[sp_Acceptor_SelectByDynamicField_ForChart]
    @ProjectType    NVARCHAR(50)=NULL,  
    @FromDate datetime=NULL,
    @ToDate datetime=NULL
AS
BEGIN   
    SET NOCOUNT ON;


    SELECT *
    FROM Acceptor
    WHERE (ProjectType = @ProjectType OR @ProjectType IS NULL)
    AND (
            @FromDate IS NULL OR @ToDate IS NULL
            OR
            (
            PmDate >= @FromDate
            AND PmDate <= @ToDate
            )        
        )

END
Comments