how to set N prefix before sql parameter

i writed a query like this:

SELECT [FirstName], [LastName] 
FROM [WorkersView] 
WHERE ([FirstName] LIKE '%' + N'علی' + '%' ) AND ([LastName] LIKE '%' + N'مسروری' + '%')

and it’s work very well! and Gives me the desired result!

but i want send parameter to the query like this:

SELECT [FirstName], [LastName] 
FROM [WorkersView] 
WHERE ([FirstName] LIKE '%' + N@FirstName + '%' ) AND ([LastName] LIKE '%' + N@LastName + '%')

that don’t worked!

and also i tried this code:

SELECT [FirstName], [LastName] 
FROM [WorkersView] 
WHERE ([FirstName] LIKE N'%' + @FirstName + '%' ) AND ([LastName] LIKE N'%' + @LastName + '%')

that don’t worked,too! and No result returns!

my collation is Persian_100_CI_AI. and all of data type of columns in nvarchar

meantime, i have this code in asp.net :

            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" Width="645px">
                <Columns>
                    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ResamanConnectionString %>"  SelectCommand="SELECT [FirstName], [LastName] FROM [WorkersView] WHERE (([FirstName] LIKE N'%' + @FirstName + N'%') AND ([LastName] LIKE N'%' + @LastName + N'%'))" >
                <SelectParameters>
                    <asp:ControlParameter ControlID="firstNameTextBox" Name="FirstName" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="lastNameTextBox" Name="LastName" PropertyName="Text" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>

how to send parameter with this code with N prefix?

Answer

First, write the query as:

SELECT [FirstName], [LastName] 
FROM [WorkersView] 
WHERE ([FirstName] LIKE N'%' + @FirstName + N'%' ) AND ([LastName] LIKE N'%' + @LastName + N'%')

The parameters should be set as:

DECLARE @FirstName NVARCHAR(255) = N'علی';
DECLARE @LastName NVARCHAR(255) = N'مسروری';

Declaring the strings to be nvarchar() instead of varchar() is the most important part of the solution.

Advertisement

Answer