CREATE PROCEDURE [dbo].[sp_get_user_configs]
@system_id int,
@config_table_version int,
@specified_uids nvarchar(max)
AS
BEGIN
if(@system_id < 1)
return
if(@config_table_version =0)
set @config_table_version = null
-- 取得 system_type
declare @system_type int
select @system_type=system_type from t_system where system_id = @system_id
;with _x as (
SELECT user_id
,[config_item_id]
,[config_item_value]
,[config_deadline]
,[config_item_title]
,[config_item_name]
,[config_item_type]
,[config_table_id]
,[system_type]
,[config_table_version]
,0 as [type]
,'用户私有' as [type_name]
,config_item_parent_id
FROM [v_user_config]
right join (select item from f_str_split(@specified_uids)) as b
on b.item = user_id
where system_type=@system_type and config_table_version=ISNULL(@config_table_version,config_table_version)
union all
SELECT [user_id]
,[config_item_id]
,[config_item_value]
,[config_deadline]
,[config_item_title]
,[config_item_name]
,[config_item_type]
,[config_table_id]
,[system_type]
,[config_table_version]
,[role_id] as [type]
,[role_name] as [type_name]
,config_item_parent_id
FROM [v_role_config]
right join (select item from f_str_split(@specified_uids)) as c
on c.item = user_id
where system_type=@system_type and config_table_version=ISNULL(@config_table_version,config_table_version)
)
select
[user_id],
[config_item_id],
[type],
[type_name],
[config_item_title],
[config_item_name],
[config_item_value],
[config_item_type],
[config_deadline],
[config_table_id],
[system_type],
[config_table_version],
config_item_parent_id
from _x
order by user_id desc
END