c# - 在SQL Server中的存储过程中进行查询，但发生一些错误

我的存储过程如下所示：

Alter PROCEDURE [dbo].[productfilter_whatsnew]
   @query1 nvarchar(max),
   @query2 nvarchar(max),
   @date date,
   @pid varchar(5)
AS
   select *
   FROM Productcolorimage pci
   where entry_date > @date
     and selectproduct = @pid + ' ' + @query1
   order by @'@query2'
GO

我的函数传递价值

public DataTable productfilter_whatsnew(ProductBAL objbal)
{
    DataTable dt = new DataTable();

    using (SqlConnection conn = new SqlConnection(strconn))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("productfilter_whatsnew", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] para = {
                               new SqlParameter("@query1","and colorid in('3')"),
                                new SqlParameter("@query2","pid ASC"),
                                new SqlParameter("@date","14-09-2013"),
                                new SqlParameter("@pid","1")
                              };
        try
        {
            cmd.Parameters.AddRange(para);
            SqlDataAdapter d = new SqlDataAdapter(cmd);
            d.Fill(dt);
            conn.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    return dt;
}

但是在放置@query1和@query2时显示不同的错误...

最佳答案

如果确实需要，可以更改过程以调用sp_executesql。您确定不能使用Entity Framework或NHibernate或任何其他ORM库吗？

Alter PROCEDURE [dbo].[productfilter_whatsnew]
@query1 nvarchar(max),
@query2 nvarchar(max),
@date date,
@pid varchar(5)
AS
BEGIN
    declare @sql nvarchar(1000)
    declare @ParmDefinition nvarchr(1000)
    SET @ParmDefinition = N'@date date, @pid varchar(5)';

    set @sql = N'select * FROM Productcolorimage pci  where entry_date>@date and selectproduct=@pid ' + @query1 + ' order by ' + @query2

    EXECUTE sp_executesql @sql, @ParmDefinition, @date=@date, @pid=@pid;
END
GO