CODEWITHSUNDEEP
sqlsql-serversql-server-2012
generaluser
generaluser

Reputation: 153

Real time application/benefit of Denali's With Result Set

What are the real time uses of the Denali's With Result Set so far Sql Stored Procs are concern apart from renaming the column names and data types at runtime.

Even what is the benefit of changing the datatypes at runtime in With Result Set

e.g.

Alter PROCEDURE test_Proc
AS
BEGIN 
      SELECT  * FROM tbl_Test
END
GO
EXEC test_Proc 
WITH RESULT SETS 
(
       (      Id int,
              EmpName varchar(50),
              PNo varchar(50) 
       )    
)

Even if the column datatypes has been changed, what will we do with that?

however this article gives some idea about it's benefit in SSIS. But I am more interested in Sql Server stored Proc talking to any front end application(e.g. c#) and the like prespective.

Upvotes: 0

Views: 234

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280500

Well, for one, say your application is calling sp_who2, and it is storing SPID in an int32. sp_who2 returns SPID as a char, requiring you to perform special handling in all of your apps to convert the output to an int32. If you create a wrapper procedure, you can do this in one place, and without having to dump the results into a temp table first. One more curious case with sp_who2 is that it returns two identical SPID columns - with WITH RESULT SETS you can rename one of them (say, to redundant_SPID) so that your apps never see multiple columns with the same name.

Another use case is say you are changing a data type from int64 to int32 or int32 to varchar, but you can't change all of your apps at once. You can change the "modern" apps to use the new data type while leaving the other "not changeable right now" apps to use the old data type. This means you can split out the deployment and testing of your apps one by one instead of making a wholesale data type change across all of the apps.

Upvotes: 2

Related Questions