udf?,isolation ?

• super48

  SSCrazy

  Points: 2682

  More actions

  April 12, 2013 at 6:04 am

  #273331

  i have 2 questions??

  1.can we use insert,delete inside user defined function(i tried myself it showing error 'side-effecting operator 'DELETE' within a function.')?

  if not why?

  2.what is isolation in sql server? where it is used ?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 12, 2013 at 6:17 am

  #1605927

  gurjer48 (4/12/2013)

  ---

  i have 2 questions??

  1.can we use insert,delete inside user defined function(i tried myself it showing error 'side-effecting operator 'DELETE' within a function.')?

  if not why?

  No you can't with the slight exception of manipulating a @Table variable. the reason is in SQL server, FUNCTIONS are a streamlined form of a stored procedure, and can only perform READ operations (ie SELECT.

  it cannot insert/update/delete to any table or #temp table either. if you need to do that, you have to use a stored proc

  2.what is isolation in sql server? where it is used ?

  MS can probably describe it better than me:

  http://msdn.microsoft.com/en-us/library/ms173763.aspx?ppud=4

  basically, the isolation level decides what rules are in place when two people are using the same data at the same time: so if i'm updating some data, lets say your lunch schedule, for 12:00 to 1:00 , do you see the data as it existed how it was before i finish updating(still 12:00, or does your request have to wait you have to wait till i'm done committing the transaction before you can see the data.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!