2008年6月10日随笔档案 - bjh

sql 不用游标的循环

sql 不用游标的循环
   select @min=min(xh) from gzxm
   while @min is not null
   begin

    。。。。。
    select @min=min(xh) from gzxm where xh>@min
   end

posted @ 2008-06-10 17:42 bjh 阅读(182) 评论(0) 编辑

sql 多表联合分页存储过程例子

CREATE PROCEDURE page_fjhz --分级汇总分页存储过程，返回符合条件的信息总数和记录
(
@jcxz char(1),
@jclen char(1),
@str   varchar(500),
@strXM varchar(500),
@strSUM varchar(500),
@PageSize   int = 10,          -- 页尺寸
@PageIndex int = 1,           -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(500) = '' -- 查询条件 (注意: 不要加 where)
)
AS
SET NOCOUNT ON
declare @strSQL   nvarchar(4000)       -- 主语句
declare @tmpstrSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
declare @tmpjcstr varchar(100)
set nocount on
if @jcxz='0'--第几级
set @tmpjcstr= ' leve='+@jclen
else  --前几级
set @tmpjcstr= ' leve<='+@jclen
declare @cnt int
set @strSQL ='select @cnt=count(*) from zy where '+@tmpjcstr
EXEC sp_executesql @strSQL,N'@cnt int output',@cnt output
select @cnt as 'infocount'
set @strSQL=''
if @OrderType != 0
begin
    set @strTmp = '<(select min'
  set @strOrder = ' order by dm desc'--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
  set @strTmp = ' >(select max'
  set @strOrder = ' order by dm asc'
end
if @PageIndex = 1
begin
  set @strSQL = 'select top ' + str(@PageSize) +'* from (select dm,mc,leve,isnull(sum(cc.cnt),0) as cnt,'+@strXM+' from zy left outer join (SELECT DISTINCT zydm as zydm, count(distinct xszh) as cnt,'+@strSUM+' FROM jfjl where ('+@strWhere+') GROUP BY zydm) cc on cc.zydm like zy.dm+''%'' group by dm,mc,leve) mm   where '+ @tmpjcstr+ @strOrder
  set @tmpstrSQL = 'select mc,cnt,'+@str+' from (select dm,mc,leve,isnull(sum(cc.cnt),0) as cnt,'+@strXM+' from zy left outer join (SELECT DISTINCT zydm as zydm, count(distinct xszh) as cnt,'+@strSUM+' FROM jfjl where ('+@strWhere+') GROUP BY zydm) cc on cc.zydm like zy.dm+''%'' group by dm,mc,leve) mm   where '+ @tmpjcstr+ @strOrder
end
else
begin
  set @strSQL ='select top ' + str(@PageSize) +'* from (select dm as dm,mc,leve as leve,isnull(sum(cc.cnt),0) as cnt,'+@strXM+' from zy left outer join (SELECT DISTINCT zydm as zydm, count(distinct xszh) as cnt,'+@strSUM+' FROM jfjl where ('+@strWhere+') GROUP BY zydm) cc on cc.zydm like zy.dm+''%'' group by dm,mc,leve) mm   where '+ @tmpjcstr +'and dm '
   + @strTmp + '(dm) from (select top ' + str((@PageIndex-1)*@PageSize) + 'dm from   zy where '+ @tmpjcstr+ @strOrder+' ) as tblTmp) '+ @strOrder
  set @tmpstrSQL = 'select mc,cnt,'+@str+' from (select dm,mc,leve,isnull(sum(cc.cnt),0) as cnt,'+@strXM+' from zy left outer join (SELECT DISTINCT zydm as zydm, count(distinct xszh) as cnt,'+@strSUM+' FROM jfjl where ('+@strWhere+') GROUP BY zydm) cc on cc.zydm like zy.dm+''%'' group by dm,mc,leve) mm   where '+ @tmpjcstr+ @strOrder
end
exec (@strSQL)
select @tmpstrSQL as 'sql'
--取得合计数
if @strWhere!=''
set @strSQL='select ''合计：'' as sm,count(distinct xszh) as cnt,'+@strSUM+' from jfjl where '+@strWhere
else
set @strSQL='select ''合计：'' as sm,count(distinct xszh) as cnt,'+@strSUM+' from jfjl '
select @strSQL as 'sumsql'
if @cnt>0
begin
if ( @cnt/@PageSize=@PageIndex-1) or (@cnt%@PageSize>0 and @cnt/@PageSize=@PageIndex-1) or ( @cnt/@PageSize=@PageIndex)
begin
  if @strWhere!=''
   set @strSQL='select count(distinct xszh) as cnt,'+@strSUM+' from jfjl where '+@strWhere
  else
   set @strSQL='select count(distinct xszh) as cnt,'+@strSUM+' from jfjl '
  exec (@strSQL)
end
end

GO

posted @ 2008-06-10 17:33 bjh 阅读(578) 评论(0) 编辑

sql填充前置字符

update tablename set colname=replicate(0,'+cast(@strlength-len(colname) as varchar)+')+colname

posted @ 2008-06-10 17:31 bjh 阅读(241) 评论(0) 编辑

使用表变量排序

declare @tbl table(id int identity(1,1),nid int) --定义表变量
  insert into @tbl(nid) select [id] from sfxm order by xh asc
  update sfxm set sfxm.xh=s.[id] from sfxm,@tbl s where sfxm.[id]=s.nid

posted @ 2008-06-10 17:27 bjh 阅读(76) 评论(0) 编辑

执行动态sql语句获得单个值

在sqlserver中执行动态sql语句获得单个值
CREATE PROCEDURE getdsqlvalue @sqlstr nvarchar(500),@result nvarchar(150) output
AS
    set @sqlstr='set @result=('+@sqlstr+')'
    EXEC sp_executesql @sqlstr,N'@result varchar(500) out',@result out
    set @result = isnull(@result,'')
    return
GO

posted @ 2008-06-10 17:25 bjh 阅读(75) 评论(0) 编辑

sql ado command

                                                ado parameter command
1:
infoid=request("infoid")
set conn=server.createobject("adodb.connection")
conn.open connstring
sql="select * from test where id=?"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1    ' 1 means adCmdText
cmd.Prepared = true
cmd.Parameters.Append cmd.CreateParameter("", adVarChar, adParamInput,150,infoid)
set rs=cmd.execute
2:

infoid=request("infoid")
set conn=server.createobject("adodb.connection")
conn.open connstring
sql="select * from test where id>?"
Set cmd = server.CreateObject("ADODB.Command")

cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1    ' 1 means adCmdText
cmd.Prepared = true
cmd.Parameters.Append cmd.CreateParameter("p1", adVarChar, adParamInput,150,infoid)
set rs=server.CreateObject("adodb.recordset")
rs.CursorLocation = adUseClient

rs.open cmd
pagesize=2
curpage=1
if not rs.eof then
    infocount=rs.RecordCount
         rs.PageSize=pagesize
         pagecount=rs.PageCount
         if cint(curpage)>pagecount then
        curpage=cstr(pagecount)
         end if
         rs.AbsolutePage=cint(curpage)
         do while (not rs.EOF) and (i<pagesize)
             i=i+1
         response.write rs("name")
       rs.movenext
       loop

    end if
conn.close

3：

infoid=request("infoid")
set conn=server.createobject("adodb.connection")
conn.open connstring
conn.execute "update test set name='bjh' where name='tt'"
sql="select * from test where name like '%' + ? +'%' or name like '%'+?+'%'"
Set cmd = server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.CommandType = 1    ' 1 means adCmdText
cmd.Prepared = true

cmd.Parameters.Append cmd.CreateParameter("@p1", adVarChar, adParamInput,150,infoid)
cmd.Parameters.Append cmd.CreateParameter("@p2", adVarChar, adParamInput,150,infoid)

set rs=server.CreateObject("adodb.recordset")
rs.CursorLocation = adUseClient

rs.open cmd
pagesize=2
curpage=1
response.write rs.eof
if not rs.eof then
    infocount=rs.RecordCount
         rs.PageSize=pagesize
         pagecount=rs.PageCount
         if cint(curpage)>pagecount then
        curpage=cstr(pagecount)
         end if
         rs.AbsolutePage=cint(curpage)
         do while (not rs.EOF) and (i<pagesize)
             i=i+1
         response.write rs("name")
       rs.movenext
       loop

    end if
conn.close

4：
sqlstr="exec pagination3 'bgt','id,title,ifcheck,putdate','id',"&pagesize&","&curpage&",1,1,"&chr(34)&querystr&chr(34)&""     set cn=server.CreateObject("ADODB.Connection")
    cn.ConnectionString=Application("connstr")
    cn.Open
    set rs=server.CreateObject("adodb.recordset")
    rs.CursorLocation = 3
    rs.CursorType=3
    rs.ActiveConnection=cn
    rs.Open sqlstr
    infocount=rs("infocount")
    set rs=rs.NextRecordset()
    if infocount>0 then
          If infocount mod pagesize = 0 Then
               pagecount=infocount\pagesize
          Else
              pagecount = cint(infocount\pagesize) +1
          End If

          do while (not rs.EOF)
             i=i+1
             if (i mod 2)=0 then
                response.write "<tr bgcolor='#e2edf7'>"
              else
                response.write "<tr bgcolor='#fffeee'> "
              end if
              response.write "</tr>"
              rs.MoveNext
          loop

    end if
    rs.Close()
    cn.Close()

posted @ 2008-06-10 09:18 bjh 阅读(248) 评论(0) 编辑

加菲猫

公告