我有一个存储过程,就是从数据库库中读取数据,然后判断数据是不是自己需要的,但是我只会读一条,而不会用类似do while not eof的方法把每一条数据都读出来,大家帮忙给写一下,谢谢!
下面是我的存储过程
ALTER PROCEDURE [dbo].[xxnews]
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 0
while (@i < 2)
begin
set @s = N'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+ltrim(@UserID)
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if (@UserName = @UserID1)
set @UserName=@i
begin
break;
end
set @i=@i+1
end
下面是我的存储过程
ALTER PROCEDURE [dbo].[xxnews]
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 0
while (@i < 2)
begin
set @s = N'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+ltrim(@UserID)
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if (@UserName = @UserID1)
set @UserName=@i
begin
break;
end
set @i=@i+1
end
declare @i int
set @i=1
while(@i<5)
begin
select top 1 * from tb where [主键] not in (select top (@i-1) [主键] from tb)
set @i=@i+1
end
ALTER PROCEDURE [dbo].[xxnews]
@UserID int,
@UserID1 int,
@UserName int output,
@UserName1 int output
as
declare @i int,@s nvarchar(4000),@ret int,@get int
set @i = 0
while (@i < 2)
begin
set @s = N'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+ltrim(@UserID)
exec sp_executesql @s,N'@UserName int out,@UserName1 int out',@UserName out,@UserName1 out
if (@UserName = @UserID1)
set @UserName=@i
begin
break;
end
set @i=@i+1
end后来我才发现符合“N'select @UserName = word2 ,@UserName1 = word3 from num'+ltrim(@i)+' where word1 = '+ltrim(@UserID)”的数据有好几条,而我这样就只取了最后一条,我想逐一的读取数据进行“if (@UserName = @UserID1) ”这样的判断,具体应该怎么写呀?
比如下面的存储过程名, sp_object_type_list,stmt = conn.prepareCall("{call sp_object_type_list()}");因为我要从数据库遍历出很多存储过程名,然后想用循环去调存储过程,然后我想把存储过程名参数化。
比如String a=" sp_object_type_list";stmt = conn.prepareCall("{call a()}");思路这样,但是我这样写肯定不对。大家谁有办法啊。
或者 exec sp_executesql (sql) --以下示例将创建并执行一个简单的 SELECT 语句,其中包含名为 @level 的嵌入参数。EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109;
与使用 EXECUTE 语句执行字符串相比,有下列优点:因为在 sp_executesql 字符串中,Transact-SQL 语句的实际文本在两次执行之间并未改变,所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。因此,SQL Server 不必编译第二条语句。Transact-SQL 字符串只生成一次。整数参数按其本身格式指定。不需要转换为 Unicode。