decalre @jsdh as char(20)
set @jsdh = 'dd1'--这样写就可以了:
exec('
DECLARE test CURSOR FOR
select cgls,jsz,cglf from '+@jsdh+' where bz=6OPEN test
')FETCH NEXT FROM test into ……WHILE @@FETCH_STATUS = 0
BEGIN
…………
FETCH NEXT FROM test into ……
END
set @jsdh = 'dd1'--这样写就可以了:
exec('
DECLARE test CURSOR FOR
select cgls,jsz,cglf from '+@jsdh+' where bz=6OPEN test
')FETCH NEXT FROM test into ……WHILE @@FETCH_STATUS = 0
BEGIN
…………
FETCH NEXT FROM test into ……
END
运行到下划线的地方,提示将 varchar转换成float 出错。
我的意思是要cgls=(cgls/@cgls)*@llcgls
应该怎么写呢?exec('
DECLARE 超时超公里摊分 CURSOR FOR
select cgls,llcgls,jsz from '+@jsdh+' where bz=6 OPEN 超时超公里摊分
')-- Perform the first fetch.
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @execstr ='update ' + @jsdh + ' set cgls=(cgls /' + @llcgls + ' * ' +
@cgls + ' where jsz = @jsz and bz <>6' ~~~~~~~~~~~~~~~~~~~~ exec(@execstr)
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
END
DECLARE 超时超公里摊分 CURSOR FOR
select cgls,llcgls,jsz from '+@jsdh+' where bz=6 OPEN 超时超公里摊分
')-- Perform the first fetch.
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @execstr ='update ' + @jsdh + ' set cgls=(cgls /' +cast( @llcgls as varchar)+ ' * ' +
cast(@cgls as varchar) + ' where jsz ='''+cast( @jsz as varchar)+''' and bz <>6' exec(@execstr)
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
END
服务器: 消息 156,级别 15,状态 1,行 1
[Microsoft][ODBC SQL Server Driver][SQL Server]在关键字 'where' 附近有语法错误。
顺便问一句,这种写法是你的经验积累来得还是在什么书上有介绍?
呵呵,感觉好强大啊,但是从书上没看到过这样的……
羡慕啊
DECLARE 超时超公里摊分 CURSOR FOR
select cgls,llcgls,jsz from '+@jsdh+' where bz=6 OPEN 超时超公里摊分
')-- Perform the first fetch.
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @execstr ='update ' + @jsdh + ' set cgls=(cgls /' +cast( @llcgls as varchar)+ ' * ' +
cast(@cgls as varchar) + ') where jsz ='''+cast( @jsz as varchar)+''' and bz <>6' exec(@execstr)
FETCH NEXT FROM 超时超公里摊分 into @cgls,@llcgls,@jsz
END
好像我见到的书讲的都比较简单……
很多语句都不知道怎么写
有个错误提示:
名为 '超时超公里摊分' 的游标已存在。
服务器: 消息 16905,级别 16,状态 1,行 3
游标已打开。
服务器: 消息 16943,级别 16,状态 2,过程 订单结算,行 34
未能完成游标操作,因为在声明游标后,表架构有更改。
存储过程: 租赁管理.dbo.订单结算
返回代码 = -6你好强悍啊