November 11, 2024 at 5:39 pm
The setup: 3 tables: core_users, core_roles, and core_user_roles. core users is the "base" table, core roles is a "look up" table, and core_user_roles contains 1-to-many foreign key references. To fix an oversight from when this was originally coded... the job spec says we need to insert any missing roles for a user if that user is made into an administrator. To do this we need to alter a stored procedure named 'dbo.std_proc_admin_user_role_create'. The proc has 3 inputs which are (nullable) JSON documents. The user making the request (who MUST be an administrator in order to assign 'admin' to another user) has their user_guid identifier sent in the @header input. The user (to be upgraded to admin) info (user_guid, role_name) is contained in the @body parameter.
Option 1: make the WHERE clause of the existing INSERT handle the situation using OR, i.e. r.role_name<>'Administrator' OR EXISTS...
Option 2: Add WHERE r.role_name='Administrator' and r.role_name=json_value(@body, N'strict $.role_name') and then make a new INSERT statement to separately insert the missing roles
Not looking for code just opinions and reasons why. I'm inclined to add the 2nd query but 1 is probably more efficient.
Here's the query
/* insert dbo.core_user_roles */
insert dbo.core_user_roles(u_id, r_id, created_dt)
output inserted.ur_id, inserted.u_id, inserted.ur_guid into @urid
select u.id, r.r_id, sysutcdatetime()
from dbo.core_users u
cross join dbo.core_roles r
where u.user_guid=json_value(@body, N'strict $.user_guid')
and u.is_blocked=0
and r.role_name=json_value(@body, N'strict $.role_name')
and exists(select 1
from dbo.core_users uu
join dbo.core_user_roles ur on uu.id=ur.u_id
join dbo.core_roles r on ur.r_id=r.r_id
where uu.user_guid=json_value(@headers, N'strict $.user_guid')
and uu.version_guid=json_value(@headers, N'strict $.version_guid')
and uu.is_blocked=0
and r.role_name=N'Administrator');
Here's the most of the proc
create or alter proc dbo.std_proc_admin_user_role_create
@headersnvarchar(max)=null,
@paramsnvarchar(max)=null,
@bodynvarchar(max)=null,
@test_idbigint output,
@responsenvarchar(max) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare
@version_guid uniqueidentifier=null,
@error_msgnvarchar(4000)=N'Invalid JSON:';
declare
@u_countint=0,
@ur_countint=0;
declare
@hash_saltuniqueidentifier=newid();
declare
@uridtable(ur_idbigint primary key not null,
u_id bigint,
ur_guid uniqueidentifier);
/* insert dbo.core_user_roles */
insert dbo.core_user_roles(u_id, r_id, created_dt)
output inserted.ur_id, inserted.u_id, inserted.ur_guid into @urid
select u.id, r.r_id, sysutcdatetime()
from dbo.core_users u
cross join dbo.core_roles r
where u.user_guid=json_value(@body, N'strict $.user_guid')
and u.is_blocked=0
and r.role_name=json_value(@body, N'strict $.role_name')
and exists(select 1
from dbo.core_users uu
join dbo.core_user_roles ur on uu.id=ur.u_id
join dbo.core_roles r on ur.r_id=r.r_id
where uu.user_guid=json_value(@headers, N'strict $.user_guid')
and uu.version_guid=json_value(@headers, N'strict $.version_guid')
and uu.is_blocked=0
and r.role_name=N'Administrator');
select @ur_count=@@rowcount;
if (@ur_count=0)
begin
select @version_guid=u.version_guid
from dbo.core_users u
join dbo.core_user_roles ur on u.id=ur.u_id
join dbo.core_roles r on ur.r_id=r.r_id
where u.user_guid=json_value(@headers, N'strict $.user_guid')
and u.is_blocked=0
and r.role_name=N'Administrator';
throw 70001, N'User role creation by admin failed', 1;
end
/* if the inserted role is 'Administrator' then insert all other available roles */
/* update user version and invalidate any existing jwt */
update u
set version_guid=newid()
from dbo.core_users u
join @urid ur on u.id=ur.u_id;
/* output: test_id */
select top(1) @test_id=cast(sign(abs(ur_id)) as bigint)
from @urid;
/* output: response */
select @response=(select N'Ok' reply_message, ur.ur_guid
from @urid ur
for json path, without_array_wrapper);
commit transaction;
end try
begin catch
select @test_id=cast(0 as bigint),
@response=(select *, @version_guid version_guid from dbo.get_error_metadata()
for json path, without_array_wrapper);
rollback transaction;
end catch
set xact_abort off;
set nocount off;
go
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 12, 2024 at 1:08 am
Yes so I went with 2 queries. Just because it's possible to jimmy horn different sets in the WHERE clause doesn't mean it's a good idea
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 12, 2024 at 1:02 pm
Guess it depends on how the optimizer likes it. Either approach can be valid, depending.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 12, 2024 at 5:05 pm
Yes so I went with 2 queries. Just because it's possible to jimmy horn different sets in the WHERE clause doesn't mean it's a good idea
It's frequently referred to as "Divide'n'Conquer" and it can definitely be faster and less resource intensive. The only way to be sure, though, is to use something like SET STATISTICS TIME,IO ON (only if there are no scalar functions involved) or SQL Profiler or (not my favorite) Extended Events.
Here's the article that explains the thing about scalar (and so mTVF's, as well) function thing.
https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle
It doesn't actually explain how to make the faster... it "just" explains that they appear to be a whole lot slower because of the improper methods people use to measure their performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2024 at 2:28 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply