Younes Koupaie Younes Koupaie - 20 days ago 5
ASP.NET (C#) Question

stored procedure return 0 record with persian text with ی

I have one procedure store when I send a parameter that contains persian letter 'ی' is no record does not return

i use N before but it's not worked

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