在C#中如何把一个“带单引号的sql长语句” 赋值给 sql server存储过程的参数

Question

如题，如何处理参数问题啊，我使用的是邹建的分页存储过程如果sql语句带有单引号，而且比较短，如qry1 ，可以直接将qry1赋给存储过程的参数，在C#中执行正常。
qry1 = "select * from at_t_staff where staff_id like 'E%'";但如果qry1太长的话，就会出错。
在查询分析器中 执行exec zj_page "select ... from ... where  ... and staff_id like 'E%' ",10,1 提示“标识符太长”查询分析器里边以如下方式执行，结果是正常的：
declare @qry nvarchar(4000)
set @qry2= 如'select ... from ... where  ... and staff_id like ''E%'' '
exec zj_page @qry,10,1

Answer 1

付存储过程
--------------------------------------------------------------------------------
ALTER  Proc zj_Page
@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10,   --每页的大小(行数)
@Index int=1,   --要显示的页
@List nvarchar (1000)='''', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)='''', --排序字段列表
@Count int = null OUTPUT      -- 输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@Source)
 ,@List=case isnull(@List,'''') when '''' then '' *'' else '' ''+@List end
 ,@Order=case isnull(@Order,'''') when '''' then '''' else '' order by ''+@Order end
 ,@Source=case when @Obj_ID is not null then '' ''+@Source else '' (''+@Source+'') a'' end--如果显示第一页，可以直接用top来完成
if @Index=1 
begin
 if @Count is null
begin
declare @lbtop1 nvarchar(1000)
set @lbtop1 = ''select @Count = count(*) from ''+@Source
exec sp_executesql @lbtop1, N''@Count int out'', @Count out
end
 select @Id1=cast(@Size as varchar(20))
 exec(''select top ''+@Id1+@List+'' from ''+@Source+@Order)
 return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,''IsTable'')=1
begin
 select @Id1=cast(@Size as varchar(20))
  ,@Id2=cast((@Index-1)*@Size as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype=''PK'')
   goto lbusetemp  --如果表中无主键,则用临时表处理  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype=''PK'' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='''',@strjoin='''',@strwhere=''''
   select @strfd=@strfd+'',[''+name+'']''
    ,@strjoin=@strjoin+'' and a.[''+name+'']=b.[''+name+'']''
    ,@strwhere=@strwhere+'' and b.[''+name+''] is null''
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype=''PK'' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
if @Count is null
begin
declare @lbuseidentity nvarchar(1000)
set @lbuseidentity = ''select @Count = count(*) from ''+@Source
exec sp_executesql @lbuseidentity, N''@Count int out'', @Count out
end
 exec(''select top ''+@Id1+@List+'' from ''+@Source
  +'' where ''+@FdName+'' not in(select top ''
  +@Id2+'' ''+@FdName+'' from ''+@Source+@Order
  +'')''+@Order
  )
 return/*--表中有复合主键的处理方法--*/
lbusepk:  
if @Count is null
begin
declare @lbusepk nvarchar(1000)
set @lbusepk = ''select @Count = count(*) from ''+@Source
exec sp_executesql @lbusepk, N''@Count int out'', @Count out
end exec(''select ''+@List+'' from(select top ''+@Id1+'' a.* from
  (select top 100 percent * from ''+@Source+@Order+'') a
  left join (select top ''+@Id2+'' ''+@strfd+'' 
  from ''+@Source+@Order+'') b on ''+@strjoin+''
  where ''+@strwhere+'') a''
  )
 return/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName=''[ID_''+cast(newid() as varchar(40))+'']''
 ,@Id1=cast(@Size*(@Index-1) as varchar(20))
 ,@Id2=cast(@Size*@Index-1 as varchar(20))declare @lbusetemp nvarchar(4000)
set @lbusetemp = ''select ''+@FdName+''=identity(int,0,1),''+@List+''
  into #tb from''+@Source+@Order
+case when @Count is null then '' set @Count = @@rowcount '' else '''' end + ''
 select ''+@List+'' from #tb where ''+@FdName+'' between ''
 +@Id1+'' and ''+@Id2
 exec sp_executesql @lbusetemp,  N''@Count int out'', @Count outGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Answer 2

为什么要把那么长的 sql 传到存储过程去
因为那段 sql 是动态的吗
那么就应该把一些和生成 sql 直接相关的变量传到存储过程
在存储过程里使用各种逻辑方法生成长的 sql
而不是在程序里生成了传进去
存储过程本来参数限制就多另外，对于拥有复杂逻辑的 sql，建议考虑使用 clr 存储过程，用 C# 完成逻辑，好爽

Answer 3

@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10,   --每页的大小(行数)
@Index int=1,   --要显示的页
@List nvarchar (1000)='''', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)='''', --排序字段列表
@Count int = null OUTPUT      你自己看看存储规程的参数，有让你传查询语句的吗？
而且就算可以，在sql中判断逻辑也会麻烦死。可以叫楼上的兄弟帮你用clr存储过程，顺便我也学习学习。

Answer 4

关于 CLR 存储过程，贴一段我项目里复杂一点的，给你参考，简单入门示例 MSDN 就有：
        /// <summary>
        /// by vengair
        /// </summary>
        /// <param name="groupsInfo"></param>
        [SqlProcedure]
        public static void sp_Account_GetCustomPropertyUserRelationsByGroupsInfo(string groupsInfo)
        {
            StringBuilder myBuilder = new StringBuilder();            myBuilder.AppendLine("SELECT pu.*, p.*, d.*");
            myBuilder.AppendLine("FROM (");            List<string> groupInfoList = new List<string>(groupsInfo.Split(';'));
            foreach (string groupInfo in groupInfoList)
            {
                if (groupInfoList.IndexOf(groupInfo) > 0)
                {
                    myBuilder.AppendLine("INTERSECT");
                }                myBuilder.AppendLine("SELECT DISTINCT u.[UserId]");
                myBuilder.AppendLine("FROM dbo.[Account.Users] u");
                myBuilder.AppendLine("\tINNER JOIN dbo.[Account.UserGroupRelations] ug");
                myBuilder.Append("\tON u.[UserId] = ug.[UserId]");                List<string> groupInfoItemList = new List<string>(groupInfo.Split('|'));
                if (groupInfoItemList[1] == "1")
                {
                    myBuilder.AppendLine(" AND ug.[GroupId] = '" + groupInfoItemList[0] + "'");
                }
                else
                {
                    myBuilder.AppendLine();
                    myBuilder.AppendLine("\tINNER JOIN dbo.[Account.Groups] g");                    int leftValue = 0;
                    int rightValue = 0;                    using (SqlConnection myConnection = new SqlConnection("context connection=true"))
                    {
                        myConnection.Open();                        SqlCommand myCommand = new SqlCommand("SELECT [LeftValue], [RightValue] FROM dbo.[Account.Groups] WHERE [GroupId] = @groupId", myConnection);
                        myCommand.Parameters.AddWithValue("@groupId", groupInfoItemList[0]);                        SqlDataReader myReader = myCommand.ExecuteReader();
                        using (myReader)
                        {
                            while (myReader.Read())
                            {
                                leftValue = myReader.GetInt32(0);
                                rightValue = myReader.GetInt32(1);
                            }
                        }
                    }                    myBuilder.AppendLine("\tON ug.[GroupId] = g.[GroupId] AND g.[LeftValue] >= " + leftValue + " AND g.[RightValue] <= " + rightValue);
                }
            }            myBuilder.AppendLine(") u");
            myBuilder.AppendLine("\tINNER JOIN dbo.[Account.CustomPropertyUserRelations] pu");
            myBuilder.AppendLine("\tON u.[UserId] = pu.[UserId]");
            myBuilder.AppendLine("\tINNER JOIN dbo.[Account.User.CustomProperties] p");
            myBuilder.AppendLine("\tON pu.[CustomPropertyId] = p.[CustomPropertyId]");
            myBuilder.AppendLine("\tINNER JOIN dbo.[System.CustomProperty.DataTypes] d");
            myBuilder.AppendLine("\tON p.[DataTypeId] = d.[DataTypeId]");            using (SqlConnection myConnection = new SqlConnection("context connection=true"))
            {
                myConnection.Open();                SqlCommand myCommand = new SqlCommand(myBuilder.ToString(), myConnection);                SqlContext.Pipe.ExecuteAndSend(myCommand);
            }
        }

Answer 5

谢谢两位，我认为可能还是 双引号和单引号的问题。slr的方法以前没听说过，看了一下老兄贴的代码，大概意思好像是拼接sql语句，
两位的提供的方法可能不是很适用，因为很多页面都要使用到分页处理，对应很多不同的sql，如果在存储过程中拼接，实现不了。我访问存储过程的方法如下，各位再帮看看：
//使用存储过程分页检索数据库数据
/// <summary>
/// 
/// </summary>
/// <param name="source">--表名、视图名、查询语句</param>
/// <param name="pageSize">每页的大小(行数)</param>
/// <param name="index">要显示的页</param>
/// <param name="listField">要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段</param>
/// <param name="order">排序字段列表</param>
/// <param name="count">输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出</param>
/// <returns></returns>
public DataTable SelectDataBasePage(string source,int pageSize,int index,string listField,string order,int count)
{

this.myConnection = new SqlConnection(this.connectionstring);
string sql="zj_page"; //存储过程
            SqlCommand comm=new SqlCommand(sql,this.myConnection);
   
           //把Command执行类型改为存储过程方式，默认为Text。 
           comm.CommandType=CommandType.StoredProcedure;
    comm.Parameters.Add(new SqlParameter("@Source",SqlDbType.VarChar)); 
    comm.Parameters["@Source"].Value = source; 
    comm.Parameters.Add(new SqlParameter("@Size",SqlDbType.Int));
            comm.Parameters["@Size"].Value = pageSize;
comm.Parameters.Add(new SqlParameter("@Index",SqlDbType.Int));
comm.Parameters["@Index"].Value = index;
comm.Parameters.Add(new SqlParameter("@List",SqlDbType.VarChar));
comm.Parameters["@List"].Value = listField;
comm.Parameters.Add(new SqlParameter("@Order",SqlDbType.VarChar));
            comm.Parameters["@Order"].Value = order;
comm.Parameters.Add( new SqlParameter("@Count",SqlDbType.Int));
comm.Parameters["@Count"].Value = 0;
comm.Parameters["@Count"].Direction = ParameterDirection.Output; DataSet tempDataSet = new DataSet();
try
{
this.da = new SqlDataAdapter(comm);
this.da.Fill(tempDataSet);
this.countInAllPage = Convert.ToInt16(comm.Parameters["@Count"].Value);//在执行完存储过程之后，可得到输出参数 
}
catch (Exception e1)
{
this.message("存储过程执行错误：" + e1.Message+ sql);
}
return tempDataSet.Tables[0];
}

Answer 6

你就按这样不就行了吗?
declare @qry nvarchar(4000)
set @qry2= 如'select ... from ... where  ... and staff_id like ''E%'' '
exec zj_page @qry,10,1
在查询分析器里,字符串不能太长,不过在代码里不有这个限制,就是Text类型的也没有关系.而Text类型的不能直接在查询分析器里定义使用.

Answer 7

hbxtlhx，你的意思是把以下三行当作一个普通的sql 在C#中执行？declare @qry nvarchar(4000)
set @qry2= 如'select ... from ... where  ... and staff_id like ''E%'' '
exec zj_page @qry,10,1

Answer 8

问题稍微明朗了一些，各位继续帮忙啊把以下三行当作一个普通的sql 在C#中执行
sting temp="
             declare @qry nvarchar(4000)
             set @qry= 'select ... from ... where  ... and staff_id like ''E%'' '
             exec zj_page @qry,10,1
             "当temp的长度在1000左右时候,执行正常了，稍微增加几个字符，执行出错，比如like ''E%''改为like ''EEEE%''，把temp复制到sql server 查询分析器执行结果是对的，

Answer 9

直接告诉你答案，不是引号问题是存储过程中exec sp_executesql ,  N''@Count int out'', @Count out
这句运行的时候，拼成的字符串超长被截断所致不信你在存储过程里
 print @lbusetemp 试一下，就知道了

Answer 10

还没有想明白啊，exec zj_page @qry,10,1 这句也是一个传参数的过程啊，你指的是C#到Sql server的传递过程吗？

Answer 11

你调用的方式好像不对啊:
在存储过程中的参数:
@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10,   --每页的大小(行数)
@Index int=1,   --要显示的页
@List nvarchar (1000)='''', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)='''', --排序字段列表
@Count int = null OUTPUT      -- 输出记录数再有你的参数中可能少了条件参数.1:你传了第一个@Source 为:"select * from at_t_staff where staff_id like 'E%'";
可是这里要求的是表名、视图名、查询语句
所以你只传at_t_staff 就可以了.
2:@List这个参数要求的是显示的字段列表,你应指定是哪些字段值,比如staff_id,...等.当然你可以不指定,默认的是*
3:@Order是排序的字段,你也可以不指定.
4:条件参数你没有指定,觉得你可以改了这个存储过程的参数列表,把条件给删除了.所以这个存储过程大约是这样调用的:set @qry2= 如'select ... from ... where  ... and staff_id like ''E%'' '
exec zj_page '表名',10,1, '','', @count output, '... and staff_id like ''E%'' '
所以这个存储过程是把一个完整Sql语句拆分开成参数而去执行的,不是一次传一个Sql进去.
这样长度就会不是问题了.