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
BUY REAL PASSPORTS,VISA,DRIVERS LICENSE,GMAT,IELTS,PLAB 1&2 CERTIFICATES, BUY IELTS,GRE,TOEIC,TOEFL,PET, FCE,CAE, CPE, BEC,YLE, KET,BULA TS,ILEC, ICFEWhatsApp:+1 (951) 480-4136
Telegram: @Approvedocs356days
Website: https://approvedocs.com/
Email: approvedocuments237@gmail.com
buy real passport, genuine passport for sale, fake passport for sale online, buy real and fake passport online, buy real genuine fake passport online, purchase passport online, buy legal american passport, purchase fake US passport online, purchase fake EU passport,
Buy Second Passport Buy Citizenship IELTS/TOEFL IDP, GMAT, ESOL,NE BOSH, DIPLOMAS,ETC
Buy Driver’s License | New legal driving license?
BUY QUALITY FAKE/REAL Passport-USA ,UK , GERMANY
We are the world number one independent group of specialize IT professionals and database technicians base in the USA, we are specialized in the making of genuine passport SSN, ID card, Birth Certificate, Visa, PR, Diplomas and many other documents of very unique quality. We have produced passports, Drivers license, SSN, ID card, Birth certificates, diplomas and other documents for over 150 countries.
We produce genuine registered documents which are legally used and we also produce Fake or Novelty documents which are just use for camouflage and can not be use legally, these types of document are not important so we produce on high demand and order We also work with agents from top embassies within the world who have all our clients information processed from within and have everything authenticated in the supposed database system. So everything we do regarding the production of a genuine passport, SSN, ID card, Birth Certificate, Visa, PR, Diplomas and many other documents are genuine and all the real documents that can be legally used. With over 17 years of service with our expertise in producing genuine passport, SSN, ID card, Birth Certificate, Visa, PR, Diplomas and many other documents, over 23 millions of our produced documents are circulating the world also with people facing problems to cross international boundaries and also help some to get jobs both nationally and internationally and for complicated cases we have immigration lawyers who help our clients if they have any difficulties. We have produce documents for many Celebrities and Politicians and top Government officials from different continents.
We quite know the risk in carrying or using a fake document that is why we invest our technologies, professionalism and skills to put in a company so as to aid people who find it difficult to have a particular document
We process passports for Canada, USA, Germany, UK, Belgium, EU countries, South Africa and other parts of the world. Feel free to contact us anytime you wish. We have Asian Brothers to sort any documentation issue you may have. Whether Genuine or Novelty Document
BUY REGISTERED PASSPORTS AND DRIVERS LICENSES
Identity cards, resident permits, visas::
Buy IELTS/TOEFL IDP, GMAT, ESOL,NE BOSH, DIPLOMAS,ETC
How do we do it? trade secret! but we can assure you that you’ll have no problems using our documents. Information on how to send us the data needed(scanned signature, biometric image etc) will be given after receiving your order.
We also have counterfeit banknotes(Eur, cad, aid, GB, used etc) for sale. Most recent designs and the quality of our banknotes are rare to find. We ship internationally with no hassles. ID cards,buy passport, fake passport, Driver’s License, Banknotes,OUR KEY WORDS
buy fake documents online, where can i buy fake passport, buy real fake passports, buy real and fake passport online, fake passport online maker, genuine passports for sale, purchase passport online, buy UK driving licence online, buy real driving license UK, buy EU driving licence without test, quickest way to get driving licence, fake Bulgarian driving licence, buy real driver’s license, buy fake UK driving licence, fake uk driving licence, buy genuine driving licence, buy driving licence, fake drivers license online, fake driving licence, fake driver’s license, quickest and easiest way to get bike licence, fastest way to get a full UK driving licence, buy fake id online, scan-able fake id, fake id maker online, birth certificate online, apply for birth certificate online, I’ve lost my birth certificate, birth certificate prices, full birth certificate uk, how do i get a copy of my birth certificate UK, instant bachelor’s degree, buy degree from any university, buy a degree uk, verifiable degree, fake degree from real university, fake college diploma maker, buy a degree from a real university, fake diploma high school, fake diplomas that look real, fake certificates online, fake certificate maker, best fake money, where can i buy counterfeit money, buy fake notes online, buy undetectable counterfeit money, buy counterfeit money, high quality counterfeit money for sale, buy fake money that looks real.
-IDs Scan-yes…
-HOLOGRAMS: IDENTICAL
-BAR CODES: IDS SCAN
-UV: YES
-Passports
FAKE IDS WITH FAST SHIPPING – EMAIL SUPPORT
Fake ID’s Over state drivers license cards and State ID, stamps, Visa, school Diplomas and other products for a number of countries like:USA, Australia, Belgium,Brazil, Canada, Italy,Finland, France,Germany, Israel, Mexico, Netherlands, South Africa,Spain, United Kingdom+1 (951) 480-4136
Telegram: @Approvedocs356days
REGISTERED AND UNREGISTERED BRITISH PASSPORT.
REGISTERED AND UNREGISTERED CANADIAN PASSPORT.
REGISTERED AND UNREGISTERED FRENCH PASSPORT.
REGISTERED AND UNREGISTERED AMERICAN PASSPORT.
REGISTERED AND UNREGISTERED RUSSIAN PASSPORT.
REGISTERED AND UNREGISTERED JAPANESE PASSPORT.
REGISTERED AND UNREGISTERED CHINESE PASSPORT.
Buy Registered and unregistered USA(United States) passports,
Buy Registered and unregistered Australian passports,
Buy Registered and unregistered Belgium passports,
Buy Registered and unregistered Brazilian(Brazil) passports,
Buy Registered and unregistered Canadian(Canada) passports,
Buy Registered and unregistered Finnish(Finland) passports,
Buy Registered and unregistered French(France) passports,
Buy Registered and unregistered German(Germany) passports,
Buy Registered and unregistered Dutch(Netherlands/Holland) passports,
Buy Registered and unregistered Israel passports,
Buy Registered and unregistered UK(United Kingdom) passports,
Buy Registered and unregistered Spanish(Spain) passports,
Buy Registered and unregistered Mexican(Mexico) passports,
Buy Registered and unregistered South African passports.
Buy Registered and unregistered Australian driver licenses,
Buy Registered and unregistered Canadian driver licenses,
Buy Registered and unregistered French(France) driver licenses,
Buy Registered and unregistered Dutch(Netherlands/Holland) driving licenses,
Buy Registered and unregistered German(Germany) driving licenses,
Buy Registered and unregistered UK(United Kingdom) driving licenses,
Buy Registered and unregistered Diplomatic passports,
IELTS certificate,
Express Canadian citizenship documents
Verified id cards
Passport registered
Canada ID Cards
United States ID Cards
Student ID Cards
International Cards
Private Cards
Adoption Certificates
Baptism Certificates
Birth Certificates
Death Certificates
Divorce Certificates
Marriage Certificates
Custom Certificates
High School Diplomas
G.E.D. Diplomas
Home School Diplomas
College Degrees
University Degrees
Trade Skill Certificates
Social Security We have a group of IT professionals and data base technicians operating worldwide with practical experience in the creation of outstanding documents, We issue database registered documents that are registered into the system and bypass all police and custom checkpoints,
Validate SSN Number Delivery is by one of a following,WhatsApp:+1 (951) 480-4136
Telegram: @Approvedocs356days
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply