declare @ID int
declare @field1 float
declare @field2 floatDECLARE sql_cursor CURSOR FOR SELECT ID
,field1
,field2
FROM tablename
OPEN sql_cursor
FETCH NEXT FROM sql_cursor INTO @ID, @field1, @field2
WHILE (@@FETCH_STATUS <> -1)
BEGIN -- do your actions here eg;
-- UPDATE tablename SET field3 = @field1 * @field2 WHERE ID = @ID
FETCH NEXT FROM sql_cursor INTO @ID, @field1, @field2
END
DEALLOCATE sql_cursor-- return your modified records
SELECT * FROM tablename
declare @field1 float
declare @field2 floatDECLARE sql_cursor CURSOR FOR SELECT ID
,field1
,field2
FROM tablename
OPEN sql_cursor
FETCH NEXT FROM sql_cursor INTO @ID, @field1, @field2
WHILE (@@FETCH_STATUS <> -1)
BEGIN -- do your actions here eg;
-- UPDATE tablename SET field3 = @field1 * @field2 WHERE ID = @ID
FETCH NEXT FROM sql_cursor INTO @ID, @field1, @field2
END
DEALLOCATE sql_cursor-- return your modified records
SELECT * FROM tablename
解决方案 »
- 关于set nocount on的作用域问题!请教
- 存储过程求助
- 关于向SQL数据库中两表同时插入数据,如何同步的问题(急)
- 高手帮帮忙之疑惑篇:为什么会是no clumn name???
- 在企业管理器中建表,怎么设VARCHAR类型字段的默认值为空字符?不是“NULL”,是“”
- 在INTERNET上如何隐藏我的SQL SERVER?
- 求一简单sql语句
- 求助,求助,关于字符串里的SQL语句(带'Like %%'的)
- csdn的搜索怎么不能用了!
- 请问系统表systypes中的sysname是什么数据类型?为什么建表设置字段类型时没有他,而且他和nvarchar的xtype是一样的。
- 自动编号,出现知序
- ****字符串分割问题
@uid int
,@num1 int output
,@num2 int output
,@num3 int output
,@num4 int output
,@num5 int output
,@num6 int output
as
begin
select @num1=count(*) from TaMessages where AcceptId=@i and State=0 and Type=0 --新短消息条数
select @num2=count(*) from TaSysMessages where AcceptId =@i and state = 0 --新系统消息条数 select @num3=count(*) from TaOnLines where UserId in (select FriendId from TaFriendsList where MyId=@i) --用户在线人数 select @num4=count(*) from TaOnLines --总的在线人数 select @num5=count(*) from TaMessages where AcceptId=@i and State=0 and type=1 --好友请求 select @num6=count(*) from TaSysMessages where state=0 and block='礼物' and acceptid=@i --最新收的新礼物 select * from TaUsers where [Id]=@uid --用户资料 select id,content,time from TaMessages where [Id]=@uid --消息资料
end
GO
@i是哪儿来的,存储过程传入参数?
然后在开发软件配置上再做一些优化之类的.所以才会有上面的问题.上面所有的查询就是我那个页面的所有查询语句,我想写在一个存储过程中然后调用它,这样会提交N多人访问的速度.
select @num2=count(*) from TaSysMessages where AcceptId =@uid and state = 0 --新系统消息条数 select @num3=count(*) from TaOnLines where UserId in (select FriendId from TaFriendsList where MyId=@uid) --用户在线人数 select @num4=count(*) from TaOnLines --总的在线人数 select @num5=count(*) from TaMessages where AcceptId=@uid and State=0 and type=1 --好友请求 select @num6=count(*) from TaSysMessages where state=0 and block='礼物' and acceptid=@uid --最新收的新礼物 select * from TaUsers where [Id]=@uid --用户资料 select id,content,time from TaMessages where [Id]=@uid --消息资料 就上面的select语句写一存储过程,不但返回@num1,@num2,@num3,@num4,@num5,@num6,还要返回用户资料和消息资料的结果集.存储过程传入的参数只有一个@uid.
返回结果集的存储过程我不会写,所以才来问问.谢谢大家帮忙,最好说的具体点.貌似4楼的答案.
1.
--把存储过程返回的记录集插入到表中.
insert 表
exec 存储过程
--对表进行处理
select * from 表
2.
--直接对存储过程返回的记录进行处理
select a.*
from openrowset('SQLOLEDB','服务器名称';'用户名';'密码',
'exec 存储过程名') AS a
网上有这样解决问题的.就不知能不能返回两个结果集和6个int参数呢?希望高手帮忙解决,说得具体点.
select *,@num1 num1,@num2 num2,.... from ..不知道你想问什么/
你说的是.net的程序.呵呵,我是做java程序的.还没找到你说的用程序解决问题的方法.等待答案.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'pr_aa' AND type = 'P')
DROP PROCEDURE pr_aa
GO
USE DBA
GOcreate proc pr_aa
@uid int
as
SET XACT_ABORT on
BEGIN TRANSACTION
begin
select num1=(select count(*) from TaMessages where AcceptId=@uid and State=0 and Type=0),
num2=(select count(*) from TaSysMessages where AcceptId =@uid and state = 0 ),
num3=(select count(*) from TaOnLines where UserId in (select FriendId from TaFriendsList where MyId=@uid)),
num4=(select count(*) from TaOnLines),
num5=(select count(*) from TaMessages where AcceptId=@uid and State=0 and type=1),
num6=(select count(*) from TaSysMessages where state=0 and block='礼物' and acceptid=@uid),
* from TaUsers where id=@uid
end
COMMIT TRANSACTION
SET XACT_ABORT off
GOselect a.* from openrowset('SQLOLEDB','127.0.0.1';'sa';'','exec DBA.dbo.pr_aa @uid=19') as a返回一条记录结果集的问题基本解决.
如果能有方法解决返回多条记录的更好了...