Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Billkamm
Posting Yak Master
124 Posts |
 Posted - 2006-01-27 : 11:58:26
|
| Is there a way to execute a SQL batch via ADO.Net? I will be using temporary tables and need them throughout one batch.Would I just make one long SQL statement including all my INSERT INTOs and UDPATEs and do .ExecuteNonQuery? |
|
|
jhermiz
3564 Posts |
Posted - 2006-01-27 : 14:32:17
|
quote:
Originally posted by Billkamm
Is there a way to execute a SQL batch via ADO.Net? I will be using temporary tables and need them throughout one batch.Would I just make one long SQL statement including all my INSERT INTOs and UDPATEs and do .ExecuteNonQuery?
Thats one possibility, or you could seperate the logic into seperate procedures and within your main procedure calls these other sprocs:exec sprocnameJon
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
Billkamm
Posting Yak Master
124 Posts |
Posted - 2006-01-27 : 15:26:34
|
| Will the temporary tables be in scope across stored procedures?I thought EXEC created a new scope |
|
|
|
jhermiz
3564 Posts |
Posted - 2006-01-27 : 15:45:20
|
quote:
Originally posted by Billkamm
Will the temporary tables be in scope across stored procedures?I thought EXEC created a new scope
Instead of asking me how about trying it for yourself?Create a temporary table, call that same temp table from a sproc...see what happens. You learn much more this way.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
Billkamm
Posting Yak Master
124 Posts |
Posted - 2006-01-27 : 16:18:56
|
| I didn't try it for 2 reasons.1. Based on what I read I thought this was the case2. My problem is that the temporary table is generated dynamically. I'm pulling values off of several controls on an ASP.Net page and then createing an SQL statement to insert them into a temporary table.However once you exceute the query through ADO.Net and it has finished SQL server will drop the temporary table as it thinks you are finished. Therefore if I then called a stored procedure I would not be able to refer to it.I asked the question as "via ADO.Net", because I believed there is no way that I know of to get all that dynamic data into the stored procedure. The number of controls is variant and is generated by a loop |
|
|
|
|
|
|
Batches via ADO.Net