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
user-defined functions cannot used perform actions modify database state.
user-defined functions cannot contain output clause has table target.
user-defined functions can not return multiple result sets. use stored procedure if need return multiple result sets.
error handling restricted in user-defined function.
a udf not support try…catch, @error or raiserror.
user-defined functions cannot call stored procedure, can call extended stored procedure.
user-defined functions cannot make use of dynamic sql or temp tables. table variables allowed.
set statements not allowed in user-defined function.
the xml clause not allowed
look @ bullet 8.
Comments
Post a Comment