You can't order by state in the row_number because it isn't assessed before the row_number column. The aliases are applied after the query.
You have two options:
Create another cte before the tempProfile cte that does the alias assignments first:
SET @SqlString='
WITH
aliases as (
SELECT
[ID],
[Name],
state=(select Name from State_District where City.StateId=State_District.ID),
District=(select Name from State_District where City.DistrictId=State_District.ID),
[PinCode],
[status] = CASE [IsActive]
WHEN 1 THEN ''label label-sm label-success''
WHEN 0 THEN ''label label-sm label-danger'' END,
[commandname] = CASE [IsActive]
WHEN 1 THEN ''Active''
WHEN 0 THEN ''InActive'' END
FROM [dbo].[City]
),
tempProfile AS
(
SELECT
[ID],
[Name],
state,
District,
[PinCode],
[status],
[commandname],
ROW_NUMBER() OVER (ORDER BY '+ @SortExpression+' ) AS RowNumber
FROM aliases
)
SELECT
[ID],
[Name],
state,
District,
[PinCode],
[status],
[commandname]
FROM
tempProfile
WHERE
RowNumber > ' + CONVERT(VARCHAR,@LowerBand) + 'AND RowNumber <' +CONVERT(VARCHAR, @UpperBand)
+ 'ORDER BY ' + @SortExpression
Or use the entire
(select Name from State_District where City.StateId=State_District.ID)
in the sort expression. That won't really suit your use in this case, though.
Hope that helps
Andy ^_^
UPDATE: Missed a comma