0

I'm working in a project for my university about security based on the design of a relational database, using SQL Server, and I'm currently working in stored procedures specifically.

After doing some research about them I have found that there is more than one way to obtain an output. There is the possibility of using output parameters, but also you can do it using a select statement (what will end with the stored procedure returning a record set if I'm not wrong)

So talking into account that I'm working in how to design a robust data base, what is the best choice to retrieve output? Are there some situations where one option is better than the other?

Thank you in advance for your answers :)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Antonio
  • 23
  • 7
  • Output parameters cannot be used to output rowsets at all (you can only pass in tables as `READONLY` parameters) so that's one big restriction. Support for output parameters is not consistent across data layers either (most object relational mappers are much more comfortable mapping result sets than accessing output parameters). None of this has anything to do with *robustness*, however -- in either case, if the stored procedure fails you should discard the results retrieved so far (and the server will begin returning results as soon as possible). – Jeroen Mostert Mar 26 '18 at 23:33

1 Answers1

0

If you expect to have a list as a result it is better to return your SELECT as a result, because you could have a lot of data.

Returning outputs as a parameter is more recommended when you have a few values to return, like in a programming language, when you want to return the result of a process.

Rodrigo Werlang
  • 2,118
  • 1
  • 17
  • 28