sql server - Set Transaction Isolation Level Disappears From table-valued function T-SQL In Management Studio -


i have table-valued function added:

set transaction isolation level read uncommitted; 

and executed alter function. however, when open said tvf in management studio again, above line not appear, has been removed. design or doing wrong?

here table-valued function i'm trying save above isolation level.

use [playerspace] go  set transaction isolation level read uncommitted  /****** object:  userdefinedfunction [dbo].[udf_get_games_by_date_league_id_group_id]    script date: 3/21/2015 8:59:32 ******/ set ansi_nulls off go  set quoted_identifier off go  alter function [dbo].[udf_get_games_by_date_league_id_group_id]      (@league_id int, @group_id int,      @division_id int, @tournament_division_id int,      @season_id int, @date1 datetime, @date2 datetime) returns table    return         events_by_date_cte (event_type, str_event_address, str_event_title, str_event_description, str_event_location, f_score_set_by, int_game_status, f_home_team_id, f_group_id, f_visitor_team_id, f_season_id, f_league_id, f_member_id, p_event_id, f_division_id, int_visitor_team_score, int_home_team_score, f_zipcode_id, dte_event_start, dte_event_end, gamedaypart, gameday, tme_event_start, f_play_location_id, f_league_schedule_id, f_tournament_division_id, f_play_location_time_slot_id, str_location_name, str_event_address_linked, str_cityname_linked, str_zipcode_linked, str_statecode_linked, str_preferredcityname, str_cityname, str_zipcode, str_statename, str_statecode, visitorgroupurl, str_league_name, str_league_url, str_group_url, int_organization_type, homegroupurl, visitorteamname, hometeamname, str_group_name,int_lon,int_lat,str_division,str_tournament_division,str_season,admin_list,int_timeslot_length_in_minutes,zonename,zoneabbrev,zonegmtoffset,zonedst,zonename_linked,zoneabbrev_linked,zonegmtoffset_linked,zonedst_linked) -- define cte query. (     select event_type,str_event_address,str_event_title,str_event_description,str_event_location,f_score_set_by,int_game_status,f_home_team_id,f_group_id,f_visitor_team_id,f_season_id,f_league_id,f_member_id,p_event_id,f_division_id,int_visitor_team_score,int_home_team_score,f_zipcode_id,dte_event_start,dte_event_end,gamedaypart,gameday,tme_event_start,f_play_location_id,f_league_schedule_id,f_tournament_division_id,f_play_location_time_slot_id,str_location_name,str_event_address_linked,str_cityname_linked,str_zipcode_linked,str_statecode_linked,str_preferredcityname,str_cityname,str_zipcode,str_statename,str_statecode,visitorgroupurl,str_league_name,str_league_url,str_group_url,int_organization_type,homegroupurl,visitorteamname,hometeamname,str_group_name,int_lon,int_lat,str_division,str_tournament_division,str_season,admin_list,int_timeslot_length_in_minutes,zonename,zoneabbrev,zonegmtoffset,zonedst,zonename_linked,zoneabbrev_linked,zonegmtoffset_linked,zonedst_linked     udf_get_events_by_date_group_id_league_id(@league_id,@group_id,@division_id,@tournament_division_id,@season_id,@date1,@date2) ) (     select * events_by_date_cte games_query     1=1 ) go 

the set operator in case not part of udf. can not use set operators in udf's

https://msdn.microsoft.com/en-us/library/ms191320.aspx

  1. user-defined functions cannot used perform actions modify database state.

  2. user-defined functions cannot contain output clause has table target.

  3. user-defined functions can not return multiple result sets. use stored procedure if need return multiple result sets.

  4. error handling restricted in user-defined function.

  5. a udf not support try…catch, @error or raiserror.

  6. user-defined functions cannot call stored procedure, can call extended stored procedure.

  7. user-defined functions cannot make use of dynamic sql or temp tables. table variables allowed.

  8. set statements not allowed in user-defined function.

  9. the xml clause not allowed

look @ bullet 8.


Comments

Popular posts from this blog

c++ - Delete matches in OpenCV (Keypoints and descriptors) -

java - Could not locate OpenAL library -

sorting - opencl Bitonic sort with 64 bits keys -