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
*/



转载于:https://www.cnblogs.com/kudosharry/articles/2418028.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值