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 =)
