Sybase存储过程例子

CREATE PROCEDURE sp_one
(
@tN varchar(50),
@returnlie varchar(5000) output
)
AS
BEGIN
DECLARE  appeal_num_cursor  CURSOR FOR SELECT name FROM syscolumns where id=object_id(@tN)
--go
--打开一个游标 
DECLARE @lie_tmp varchar(50)
DECLARE @lie varchar(5000)
select @lie = ''
OPEN  appeal_num_cursor
--循环一个游标
FETCH appeal_num_cursor INTO @lie_tmp
--SELECT @lie = [' + @lie_tmp +']'

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @lie != ''
  BEGIN
  SELECT @lie = @lie + ',[' + @lie_tmp +']'
  END
  ELSE
  BEGIN
  SELECT @lie = '['+ @lie_tmp +']'
  END
  FETCH appeal_num_cursor INTO @lie_tmp
END 
CLOSE appeal_num_cursor
DEALLOCATE cursor appeal_num_cursor
--print @lie
SELECT @returnlie = @lie
END




CREATE PROCEDURE SplitPageByLine_NEW
(
@SqlStr         varchar(8000),
@FirstRec       int,        
@LastRec        int,        
@tN             varchar(50),
@isXing         int
)
AS
DECLARE @dt varchar(10)
DECLARE @strZZ varchar(10)
DECLARE @strLieMing varchar(5000)
BEGIN

SELECT @dt= substring(convert(varchar, rand()), 3, 10)
print @dt
SELECT @strZZ = name FROM syscolumns where status=128 and id=object_id(@tN)

if  @strZZ != ''
begin
    if @isXing != 1
    begin
        if exists(select charindex(upper(@strZZ), upper(@SqlStr)))
        begin
            SELECT @SqlStr = stuff(@SqlStr, charindex(upper(@strZZ), upper(@SqlStr)), char_length(@strZZ) ,'convert(numeric,'+@strZZ+') '+@strZZ)
            SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
--print @SqlStr
            EXECUTE (@SqlStr)    
            SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
            EXECUTE (@SqlStr)
            SELECT @SqlStr = 'SELECT * FROM tempdb..Lining'+@dt+' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
            EXECUTE (@SqlStr)    
            SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
            EXECUTE (@SqlStr) 
        end
        else
        begin
            SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
            EXECUTE (@SqlStr)    
            SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
            EXECUTE (@SqlStr)
            SELECT @SqlStr = 'SELECT * FROM tempdb..Lining'+@dt+' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
            EXECUTE (@SqlStr)    
            SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
            EXECUTE (@SqlStr) 
        end
    end
    else
    begin
        EXECUTE sp_one @tN,@strLieMing output
--print @strLieMing
        SELECT @SqlStr = stuff(@SqlStr, charindex('*', upper(@SqlStr)), 1 ,@strLieMing)
        SELECT @SqlStr = stuff(@SqlStr, charindex('['+upper(@strZZ)+']', upper(@SqlStr)), char_length(@strZZ)+2 ,'convert(numeric,'+@strZZ+') '+@strZZ)
        SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
--print @SqlStr
        EXECUTE (@SqlStr)
        SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
        EXECUTE (@SqlStr)
        SELECT @SqlStr = 'SELECT * FROM tempdb..Lining'+@dt+' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
        EXECUTE (@SqlStr)    
        SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
        EXECUTE (@SqlStr) 
    end
end
else
begin
    SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ', upper(@SqlStr)), 6 ,' INTO tempdb..Lining' + @dt + ' FROM ')
    EXECUTE (@SqlStr)    
    SELECT @SqlStr = 'ALTER TABLE tempdb..Lining' + @dt + ' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
    EXECUTE (@SqlStr)
    SELECT @SqlStr = 'SELECT * FROM tempdb..Lining'+@dt+' WHERE TEMPDB_ID > ' + convert(varchar, @FirstRec) + ' and TEMPDB_ID <= ' + convert(varchar, @LastRec)
    EXECUTE (@SqlStr)    
    SELECT @SqlStr = 'DROP TABLE tempdb..Lining'+@dt
    EXECUTE (@SqlStr) 
end

END




set textsize 65536
select * from tt

/*
declare @pageptr varbinary(16)
select @pageptr=textptr(bb) from tt where aa='aa'
readtext tt.bb @pageptr 1 2549
*/