首页 > 代码库 > 获取存储过程返回值的几种方式
获取存储过程返回值的几种方式
1 正常存储过程带RETURN(只能返回整型)
CREATE PROCEDURE p_test1 AS DECLARE @int intSET @int = 102400;RETURN @int;--这里只能返回整型--执行DECLARE @p1return INT --声明一个变量EXECUTE @p1return= p_test1 --使用变量来接收 return回来的值
SELECT @p1return
2 带OUTPUT参数的存储过程
CREATE PROCEDURE p_2 @str NVARCHAR(20)=NULL OUTPUT,@int INT=NULL OUTPUTAS SET @str=‘say hi‘;SET @int =1024;DECLARE @p1 NVARCHAR(20),@p2 INT --声明两个变量--执行EXECUTE p_2 @p1 OUTPUT,@p2 OUTPUT --变量后有OUTPUTSELECT @p1,@p2--结果------------------------(无列名) (无列名)say hi 1024--执行EXECUTE p_2 @p1 ,@p2 --注意这里没有OUTPUTSELECT @p1,@p2--结果-----------------------(无列名) (无列名)NULL NULL
3 存储过程中产生了一个结果集(注: select c1 from table 在存储过程中 执行两次,其实也算是一个结果集)
CREATE PROCEDURE p_3AS SELECT 1 id,‘xiaoli‘ nameUNIONSELECT 2 id,‘xiaowang‘ name--注意如果有两次查询,那么这两次查询返回的数据表的结构必需是一样的.SELECT 3 id,‘xiaozhang‘ nameUNIONSELECT 4 id,‘xiaozhao‘ name--执行--这里使用的是表变量,当然使用临时表,实体表都是可以的.DECLARE @tab AS TABLE(id int ,name nvarchar(20))INSERT INTO @tab(id,name)EXECUTE p_3SELECT * FROM @tab--结果----------------------id name1 xiaoli2 xiaowang3 xiaozhang4 xiaozhao
4存储过程中产生了两个结果集
这个就很麻烦了,因为两个结果集数据表结构不一样,所以无法使用第3种方法. 这里使用了xml技术进行变通.
In the life of every software engineer, there should be at least (I’d love to say “at most" actually) one project where bussiness logic is implemented on database side. My current project is one of that kind. We have all logic designed as stored procedures, then there’s a middleware where we use LINQ2SQL for database API bindings, then there are iPhone application and a web-site.Well, it works. But it’s a hell.First of all, T-SQL itself is a very primitive low-level language. There are just no features to make components reusable. So, as long as your procedures are just couple of select statements, everything’s fine. But the more execution flows you have to implement, the more unreadable your procedure becomes. I do firmly believe that being extremely pedantic can make things better, but it doesn’t solve the problem completely. It’s just fine to make mistakes when you write a program. In most cases you have an easy way to cover your code with automated tests, at least it’s pretty straighforward in high-level languages. With T-SQL, you just can’t.In our project, I had to take testing tasks as one of my concerns. I finally came out with idea that the easiest approach technically is just to test the middleware+database together. Test suite is just another client like iPhone application or web-site. This approach proved its adequacy, but the problem is, when Teamcity says things like Test "CanTransferLotsOfMoneys" failed, database guys have no idea what it means.So, for a long time I’ve been trying to find out a solution to write tests for stored procedure in pure T-SQL. In this case, I could have gotten rid of being the only person to intepret test failure. The major point was, in T-SQL it’s just impossible to access multiple result sets if stored procedure returns them. In our project, there are 2 or may be 3 procedures, which only return 1 result set. That’s the issue.Always return only one result setSure this will never work. Simple example is, I need to get data to render a blog post page. I’ll need:Current user’s detailsPost detailsComments and their detailsThere are at least 3 result sets.Use temporary tablesThis will probably work, but what do you do if some procedures call themselves recursively?Table variablesTable variables is a great idea:declare @Users table( UserId int, UserName nvarchar(256))exec DoSomething @UsersBut unfortunately stored procedures can’t return them.I finally discovered that T-SQL has features to work with XML. The solution consists of few simple ideas.There’s a type xml in T-SQL. This type allows storing XML documents and accessing their nodes.Objects of this type can be either converted to nvarchar() or constructed from it.Objects of this type can be passed to stored procedures as output parameters.You can easily generate XML from select queries.Let’s say we have 2 tables:create table Blog( BlogId int identity(1,1) not null, BlogName nvarchar(256))create table Post( PostId int identity(1,1) not null, BlogId int not null, -- yes, this should be FK BlogName nvarchar(256))The task is:Create a testable stored procedure GetBlogWithDetails @blogId, that will return BlogId, BlogName and all its posts.Definition of “testable" is: I can access all the data returned and based on this data make some conclusions.Normally, this procedure would look like this: (no error handling - sorry)create procedure GetBlogWithDetails(@BlogId int) as beginselect BlogId, BlogName from Blog where BlogId = @BlogId select PostId, BlogId, PostName from Post where BlogId = @BlogIdendYou can’t access its second result set. The solution is to render same data as XML and return this XML as output parameter.create procedure GetBlogWithDetailsXml( @blogId int, @xml xml output) as begin -- first, create 2 table variables for blogs and for posts declare @blogRows table(BlogId int, BlogName nvarchar(256)) declare @postRows table(PostId int, BlogId int, PostName nvarchar(256)) -- (#1) this is where you implement your useful logic insert into @blogRows select BlogId, BlogName from Blog where BlogId = @blogId insert into @postRows select PostId, BlogId, PostName from Post where BlogId = @blogId -- here you render your 2 tables containing useful data as XML declare @blogRowsXml xml = ( select BlogId, BlogName from @blogRows as Blog for xml auto) declare @postRowsXml xml = ( select PostId, BlogId, PostName from @postRows as Post for xml auto) -- here you build a single XML with all the data required set @xml = cast(@blogRowsXml as nvarchar(max)) + cast(@postRowsXml as nvarchar(max)) -- (#2) here you return the data as "raw" result sets. select * from @blogRows select * from @postRowsendAs you see, “useful" code is only at #1 and #2. The rest is bunch of stuff for XML. You can now play with GetBlogWithDetailsXml like this:For my test data,declare @xml xmlexec GetBlogWithDetailsXml 1, @xml outputselect @xmlreturns XML like this:<Blog BlogId="1" BlogName="Blog #1" /><Post PostId="1" BlogId="1" PostName="Post #1 in Blog #1" /><Post PostId="2" BlogId="1" PostName="Post #2 in Blog #1" />It’s now pretty easy to extract all the data returned:select Col.value(‘@BlogId‘, ‘int‘) as BlogId, Col.value(‘@BlogName‘, ‘nvarchar(256)‘) as BlogNamefrom @xml.nodes(‘/Blog‘) as Data(Col)select Col.value(‘@PostId‘, ‘int‘) as PostId, Col.value(‘@BlogId‘, ‘int‘) as BlogId, Col.value(‘@PostName‘, ‘nvarchar(256)‘) as PostNamefrom @xml.nodes(‘/Post‘) as Data(Col)with my test data, it returns:BlogId BlogName1 Blog #1PostId BlogId PostName1 1 Post #1 in Blog #12 1 Post #2 in Blog #1I’m not really sure if returning “raw" result sets even makes sense with this approach. From the viewpoint of LINQ2SQL, yes, you won’t be able to utilize its mapping feature, but deserializing object from XML is a primitive task, so it shouldn’t be an issue.
获取存储过程返回值的几种方式
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。