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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-11-22 : 07:42:00
|
| Aaron writes "Hi i recently created a web application in .NET that allows customers to submit an enquiry to a database. This enquiry is then inserted into the enquiry Table.I then retrieve the id of this record using "Select @@identity". Then using some for loops i construct an sql query eg customers are alowed to search on mutiple values by splitting the values with a "," my code then splits there up and the generated code looks a little like this INSERT INTO tempTable(BusID) SELECT BUSID FROM BusCapReg WHERE BusProducts_Services_BrandNames LIKE '%steel%' AND BusProducts_Services_BrandNames LIKE '%roofing%' This string is then passed to a stored procedure, with the @@identity of the first insert.THe procedure creates the table tempTable executes the string, loops for howmany records in tempTable inserting each record into another table called EnquiryDetailsMy question is do stored procedurs lock ie No 2 instances of a stored procedure my run at the same time.The resion i want to know this is because the code is called from a web form they may execute at the same time Also would it be more eficent and safe for the code to be run in my asp page and not use a storded procedure even tho the select from BusCapReg may return hundeds of rows which i would have to loop through and insert on each oneThank uAza" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-22 : 07:52:14
|
| Stored procs are run independantly. i.e. the same sp can be run many times on different connections at the same time (or recursively on the same connection).It's up to you to code so that the instances don't block each other but that will be due to the statements in the SPs not the sp itself.It's easier to maintain, optimise, change structure and implement security if the code is in stored procs rather than embedded in applications.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Do stored procedure lock on recources