-1

I have a stored procedure which returns 2 result sets according to the two different inputs.

If I am calling the same stored procedure from different C# methods to retrieve result sets how is it working?

Is it working as an OO concept? Like whenever I call the stored procedure, is it creating a new stored procedure object in SQL Server & return the result set?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fernandos fernandos
  • 47
  • 8
  • 1
    @fernandosfernandos If someone is posting comments you think are out of line, the proper thing to do is flag them for moderator attention. Not to respond in kind. – Andrew Barber Sep 26 '14 at 12:36

1 Answers1

3

Short answer: No.

There isn't such a thing as a "SP object". Also, calling it from C# doesn't impact the answer.

You can think of a Stored Procedure as more like a simple procedure before the days of object orientation. There isn't an object within SQL Server that holds state or anything else. You provide input, it gives you a result and the code for the SP ends. At this point SQL server considers the request to have been fulfilled and, functionally, forgets that the request was even made.

Now, C# will wrap the results of the SP call into a SqlReader object - this is client side. The reason is to provide a bit more flexibility to your code, such as being able to iterate the rows of a datatable, or get the properties of particular column.

However all of that occurs within C# and is wholly dependent on the framework you use for calling the SP in the first place. For example, LINQ and various ORM's can automatically convert each row as an object.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Thanks. i have one more question , Please tell me how SPs are synchronize their execution. If i called my 2 methods as i mentioned previously , is sql server synchronous access to SP , like it will execute one procedure call at a time while other one is waiting. – fernandos fernandos Sep 26 '14 at 03:47
  • @fernandosfernandos: SQL Server is multi-threaded. So multiple procedures can run at the same time. However, a sql query can cause locks (table, page or row) which blocks other processes from modifying or even reading that particular data until the initial query is complete. See this question for a little more info: http://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries – NotMe Sep 26 '14 at 13:26
  • Pls. note i am calling same SP, from different methods at same time from different threads. – fernandos fernandos Sep 26 '14 at 14:10
  • If they are select statements, the default is to lock the table for shared reads. This means that both procedures should be able to execute at the same time. If one (or both) of them is an update statement, then the first query will block the other. Meaning they will be sequential. – NotMe Sep 26 '14 at 15:28