Achraf Ben Alaya
No Result
View All Result
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy
SUBSCRIBE
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy
No Result
View All Result
Achraf Ben Alaya
No Result
View All Result
ADVERTISEMENT
Home Blog

Sql tips and tricks

achraf by achraf
April 26, 2020
in Blog, sql
5 min read
0
Sql tips and tricks
0
SHARES
218
VIEWS
Share on FacebookShare on Twitter

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

ShareTweet
Previous Post

Boxing and Unboxing in C#

Next Post

Font Awesome ,Bootstrap and Material Font Icons For Xamarin.Forms

Related Posts

AI

Model Context Protocol (MCP): The Future of AI Integration

April 21, 2025
94
Azure

Step-by-Step Guide: Azure Front Door + Storage Account Static Website + Custom Domain with Terraform

March 11, 2025
212
Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet
Azure

Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

February 3, 2025
132
Understanding Generative AI and RAG Benefits
AI

Understanding Generative AI and RAG Benefits

January 12, 2025
95
Azure Communication Services Email Sending Simplified: From Setup to Execution and Monitoring
Azure

Azure Communication Services Email Sending Simplified: From Setup to Execution and Monitoring

December 8, 2024
1.5k
PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis
Azure

PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis

November 2, 2024
495
Next Post
Font Awesome ,Bootstrap and Material Font Icons For Xamarin.Forms

Font Awesome ,Bootstrap and Material Font Icons For Xamarin.Forms

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Terraform

Certifications

Microsoft certified trainer (MCT)

Recommended

Configure postman / newman API tests in Azure DevOps

Configure postman / newman API tests in Azure DevOps

October 25, 2021
3.5k
Where is my Money ! The Proper way To Shutdown Azure VM

Where is my Money ! The Proper way To Shutdown Azure VM

November 2, 2020
854
Finally Azure Static Web Apps no more in Preview!

Finally Azure Static Web Apps no more in Preview!

May 15, 2021
1k
Tools I use with Database

Tools I use with Database

November 7, 2021
799
Azure Policy for governance

Azure Policy for governance

August 29, 2020
1.4k
Migrate and modernize your applications on Azure

Migrate and modernize your applications on Azure – Part – 00 (creating .Net 5.0 application )

March 29, 2021
307
Facebook Twitter LinkedIn Youtube

Model Context Protocol (MCP): The Future of AI Integration

April 21, 2025

Step-by-Step Guide: Azure Front Door + Storage Account Static Website + Custom Domain with Terraform

March 11, 2025
Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

February 3, 2025

Categories

  • AI (2)
  • Apps (1)
  • Azure (63)
  • blazor (2)
  • Blog (91)
  • c# (7)
  • Cloud (65)
  • Courses (3)
  • Dapr (4)
  • docker (4)
  • Games (1)
  • General Tips & Fix (1)
  • Home (1)
  • Kubernetes Service (AKS) (1)
  • motivation (2)
  • Motivation (3)
  • News (9)
  • Resume (1)
  • sql (4)
  • Terrafrom (1)
  • Tricks, Tips and Fixes (4)
  • xamarin (5)
No Result
View All Result
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy