一个存储过程练习,如下:CREATE proc book_query
@bookName char(100), -----This is the input parameter as the name of Book
@bookAuthor char(40), -----This is the input parameter as the author of Book
@bookPublisher char(100), -----This is the input parameter as the book concern of Book
@bookId char(16) output -----This is the output parameter as the ID of Book
as
if(@bookName ='' and @bookAuthor ='' and @bookPublisher ='')
select @bookId='输入数据出错!'
else
begin
if(@bookName !='' and @bookAuthor ='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_name like @bookName
if(@bookName ='' and @bookAuthor !='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_author like @bookAuthor
if(@bookName ='' and @bookAuthor ='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor !='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_name like @bookName and b_author like @bookAuthor
if(@bookName ='' and @bookAuthor !='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_author like @bookAuthor and b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor ='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_name like @bookName and b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor !='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_name like @bookName and b_author like @bookAuthor and b_publisher like @bookPublisher
end
执行如下:declare @bookId char(16)
EXECUTE book_query '数据%','电子%','李%',@bookId Output
--select @bookId
print @bookId
但是,没有结果集,但数据库存在输入条件的记录。
请教,为什么?没有结果?
@bookName char(100), -----This is the input parameter as the name of Book
@bookAuthor char(40), -----This is the input parameter as the author of Book
@bookPublisher char(100), -----This is the input parameter as the book concern of Book
@bookId char(16) output -----This is the output parameter as the ID of Book
as
if(@bookName ='' and @bookAuthor ='' and @bookPublisher ='')
select @bookId='输入数据出错!'
else
begin
if(@bookName !='' and @bookAuthor ='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_name like @bookName
if(@bookName ='' and @bookAuthor !='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_author like @bookAuthor
if(@bookName ='' and @bookAuthor ='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor !='' and @bookPublisher ='')
select @bookId=b_id from book1 where b_name like @bookName and b_author like @bookAuthor
if(@bookName ='' and @bookAuthor !='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_author like @bookAuthor and b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor ='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_name like @bookName and b_publisher like @bookPublisher
if(@bookName !='' and @bookAuthor !='' and @bookPublisher !='')
select @bookId=b_id from book1 where b_name like @bookName and b_author like @bookAuthor and b_publisher like @bookPublisher
end
执行如下:declare @bookId char(16)
EXECUTE book_query '数据%','电子%','李%',@bookId Output
--select @bookId
print @bookId
但是,没有结果集,但数据库存在输入条件的记录。
请教,为什么?没有结果?
select @bookId=isnull(b_id,'null') from book1 where b_name like @bookName and b_author like看下有没有结果。
但是
这样执行 就有唯一的一行记录.declare @bookId char(16)
EXECUTE book_query '数据库程序设计','','',@bookId Output
print @bookId
那用什么?
什么参数值都不传递的时候,返回的的确是 “输入数据出错!”
而且,这样有返回一行记录啊?declare @bookId char(16)
EXECUTE book_query '数据库程序设计','','',@bookId Output
print @bookId糊涂了,请教。