Iโm super excited to write my first article in tips and tricks section about sql . if youโre pursuing a job as a back-end developer or if you simply work with data (data scientist, data engineer.., whatever you want to call it) it means you will work with sql , tables , procedure,views etcโฆ
In this short article Iโm going to share with you few tips I learned past days.
Last week , and after I have updated an old version of source code and procedures at work , I thought i finished my work , but nah I didnโt .
Of course there is no problems In code (hopefully , cause test team is gonna start testing the code next week ๐ค ) when we push the source code , it will be the same for all the clients , but , I only have updated the procedure in one database for one client , and there is like 15 to 20 client ! I forget about that !
So , what should I do here , should I go to each database, look for the procedures inside the stored procedures folder and do ALTER PROCEDURE ?
Thatโs just waste of time and thank to one of my college at work , I learned how to do that In a better way .
for that Iโm gonna show you how to create a table , insert data , create procedure , look for that procedure and update it in easy way .
so letโs get started .
Create Table
Here we are going to create a Table called person and we are going to fill it with random data :
CREATE TABLE person ( firstname VARCHAR(50), lastname VARCHAR(50), gender VARCHAR(50), phonenumber VARCHAR(50), city VARCHAR(50), urd DATETIME DEFAULT (Getdate()) )
INSERT INTO person (firstname, lastname, gender, phonenumber, city) VALUES (โheeroโ, โyuyโ, โmaleโ, โ888888โ, โlondonโ) INSERT INTO person (firstname, lastname, gender, phonenumber, city) VALUES (โrelenaโ, โdarlianโ, โfemaleโ, โ888888โ, โlondonโ) INSERT INTO person (firstname, lastname, gender, phonenumber, city) VALUES (โmessiโ, โlionel โ, โmaleโ, โ888888โ, โrosarioโ) INSERT INTO person (firstname, lastname, gender, phonenumber, city) VALUES (โcristianoโ, โronaldoโ, โmaleโ, โ888888โ, โ funchalโ)
Now after populating the data if we see whatโs inside our Table by using :
select * from Person
we will find that our table now contain this data :
now letโs say we want to get all the persons where city = London , itโs easy to write
SELECT * FROM person WHERE city = โLondonโ
but as a backend developer you know that you are going to create a Stored Procedure or you are going to use Entity Framework .
Stored Procedure
To write a simple stored procedure that will return the persons where city is London we have to write this simple procedure :
CREATE PROCEDURE Selectallcustomers @city NVARCHAR(30) AS SELECT * FROM person WHERE city = @city
To test this procedure all you have to do is to run it using EXEC , how is that ? itโs simple !
EXEC SelectAllCustomers โLondonโ
Excec will execute the procedure SelectAllCustomers using the parameter โLondonโ which is the
city we are looking for , and as result we will get :
Easy ? right !!
Now , letโs say that this procedure exist in 20 database ,like for 20 client, the project manager now ask you to changed that procedure cause they need not only persons where city equals London but also the gender of those person is Male .
Now your not gonna start looking for that procedure manually in each database cause you are going to loose a lot of time in something that will only take few minutes (I used to loose all that time , donโt be shy , weโre all still learning )
Now first step is to create or update that procedure as we are asked for :
The old procedure is :
CREATE PROCEDURE Selectallcustomers @city NVARCHAR(30) AS SELECT * FROM person WHERE city = @city
now Letโs change it and we add that the Gender is Male :
CREATE PROCEDURE Selectallcustomers @city NVARCHAR(30) AS select * FROM person WHERE city = @city AND gender =โmaleโ
now if you try to execute this procedure again you will get an error like this :
That mean that the procedure does exist in the database and you can not insert that procedure with the same name , now donโt think we are going to rename that database , and like that we will have a ton of unused procedures in our database .
to update this procedure , we need to drop it first than replace it with the new one .
First , letโs check with a simple command if the procedure exist in our database .
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Nโ[SelectAllCustomers]') AND type in (NโPโ, NโPCโ,โFNโ,โTFโ)) SELECT โfoundโ AS search_result ELSE SELECT โnot foundโ AS search_result;
Now ,what will this code do is , to look inside the sys.objects and see if the object or the procedure SelectAllCustomers exist , if exist it will return found , else it will return as search result not found . We can make the result of this as we want it , for example we can return 1 if exist and 0 if not , but it is nicer to get a clean result that another person can understand , cause other may not understand 0 and 1 and their meaning .
Now , after checking that the procedure exist , letโs update it :
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Nโ[SelectAllCustomers]') AND type in (NโPโ, NโPCโ,โFNโ,โTFโ)) DROP PROCEDURE SelectAllCustomers GO CREATE PROCEDURE SelectAllCustomers @city nvarchar(30) AS SELECT * FROM person WHERE city = @city and gender ='Maleโ
Now , donโt get overwhelmed , I will explain it all .
The first line will check if the procedure SelectAllCustomers is a stored procedure (P) or a assembly stored procedure (PC).. that exist in the db . If that procedure exist the command DROP PROCEDURE will delete that procedure and after it , we will create a new one .
Easy ? right !
More tricks !
If you want to see if a table exist in the database you can execute this block and donโt forget to change the name of the table with yours
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = โPersonโ ) SELECT โfoundโ AS search_result ELSE SELECT โnot foundโ AS search_result;
If you want to get all the columns of a table you can execute this block :
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = โPersonโ
If you are looking if there is a table โpersonโ witha column name like โGenderโ you can type :
IF EXISTS( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = โPersonโ and COLUMN_NAME='genderโ) SELECT โfoundโ AS search_result ELSE SELECT โnot foundโ AS search_result
Now that was the first post , more are coming next weeks .
Happy SQL day =)