SQL动态查询 - '@WhereClause'附近的语法不正确

//_whereclause is: where (lastName like '%Davis%')
public static MyList GetAll(string _whereclause)
{         
    using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
    {
        string selectSQL = "";               
        selectSQL += "SELECT @RecordCount = COUNT(*) FROM [PersonnelTable]";

        if (_whereclause != string.Empty)
        {                   
            selectSQL += " @WhereClause";
        }            

        using (SqlCommand myCommand = new SqlCommand(selectSQL, myConnection))
        {
            myCommand.CommandType = CommandType.Text;                    
            SqlParameter whereClauseParam = new SqlParameter("@WhereClause", SqlDbType.NVarChar, 4000);
            whereClauseParam.Value = _whereclause;

            myConnection.Open();

            using (SqlDataReader myReader = myCommand.ExecuteReader())
            {..............

If I run it with the @WhereClause I get error:

如果我用@WhereClause运行它我得到错误:

Incorrect syntax near '@WhereClause'.

'@WhereClause'附近的语法不正确。

1 个解决方案

#1

Your select query should be like

您的选择查询应该是这样的

selectSQL += "SELECT @RecordCount = COUNT(*) FROM [PersonnelTable] where (lastName like '%" + @WhereClause + "%')";

Assuming that:

SqlParameter whereClauseParam = new SqlParameter("@WhereClause", SqlDbType.NVarChar, 4000);
whereClauseParam.Value = _whereclause;  //Here you are getting the value as 'Davis'

But in case you are getting the value in it as where (lastName like '%Davis%') then you simply need to add a space after

但是如果你把它的值变为where(lastName就像'%Davis%')那么你只需要在之后添加一个空格

selectSQL += "SELECT @RecordCount = COUNT(*) FROM [PersonnelTable] ";
                                                                  ^^ here

#1