In my SQL example you'll see that I'm declaring my SQL statement as an nvarchar variable. My webserver communicates with our business system via a linked server connection. Because of this I have to use OpenQuery() to query our business system. SPs are
not very friendly with the OpenQuery process but it will execute the command if it's stored as a variable and then issued the exec(command) statement.
Assuming you're not using linked servers you don't need to store the command in a string and execute it, you can just simply place your query in your SP. You'll see that all I'm doing is issuing two different select statements which returns two different
recordsets. Then I fill a dataset with the SP and datatable(0) is the first record set returned and dataset(1) is the second returned and then you just databind. I've filled two gridviews, with two different select statements, but I've only called the connection
to my DB once.
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 07:29 PM|LINK
In my SQL example you'll see that I'm declaring my SQL statement as an nvarchar variable. My webserver communicates with our business system via a linked server connection. Because of this I have to use OpenQuery() to query our business system. SPs are not very friendly with the OpenQuery process but it will execute the command if it's stored as a variable and then issued the exec(command) statement.
Assuming you're not using linked servers you don't need to store the command in a string and execute it, you can just simply place your query in your SP. You'll see that all I'm doing is issuing two different select statements which returns two different recordsets. Then I fill a dataset with the SP and datatable(0) is the first record set returned and dataset(1) is the second returned and then you just databind. I've filled two gridviews, with two different select statements, but I've only called the connection to my DB once.