Difference between Stored Procedure and Function in SQL

In SQL, Stored Procedures and Functions are both used to encapsulate reusable logic, but they serve different purposes and have key differences in how they are used, structured, and executed. Understanding these differences is essential for designing efficient and maintainable database systems.

What is a Stored Procedure?

A Stored Procedure is a precompiled collection of one or more SQL statements that perform a specific task. It can accept input parameters, perform operations such as INSERT, UPDATE, DELETE, and SELECT, and can also return output parameters or a result set.

Key Features of Stored Procedures:

• Can perform DML (Data Manipulation Language) operations.

• Can return multiple values via output parameters.

• Can call other procedures and functions.

• Can handle exceptions using TRY...CATCH.

• Execution does not return a value directly (unless using output parameters).

What is a Function?

A Function in SQL is a database object that accepts parameters, performs actions (usually calculations or transformations), and returns a single value. Unlike stored procedures, functions must return a value.

Key Features of Functions:

• Must return a value (scalar or table).

• Cannot perform INSERT, UPDATE, DELETE operations (in most DBMS).

• Cannot use transactions (COMMIT/ROLLBACK).

• Cannot call procedures but can call other functions.

• Primarily used for computations and data transformation.
  
  Watch my YouTube on this topic:-

 

How to backup SQL Server database using command prompt ?

In this video, I have explained how to backup SQL Server database using Command Prompt. 

Steps:-

1) Open command prompt.

2) Type following command in command prompt.

3) Hit enter.

SqlCmd -S BOSS-PC\SQLEXPRESS -U sa -P 1234 -Q "Backup Database MyDb_18122018 Το Disk='D:\Backup\MyDb_18122018.bak"

SqlCmd - It will use SQL server command tool.

-S BOSS-PC\SQLEXPRESS(It's SQL server name.)

-U sa (SQL server username)

-P 1234(SQL server password.)

-Q "Backup Database- It's SQL command MyDb_18122018(My database name.)

To Disk='D:\Backup\MyDb_18122018.bak" (Destination for storing database backup.)

Watch my YouTube video on same topic:- 

How to configure and send email using SQL server ?

 In this video, I have explained how to configure and send email using SQL Server.  

Steps:-

1) Unblock SQL server to send email.

USE MASTER

GO

SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

SP_CONFIGURE 'Database Mail XPs', 1

RECONFIGURE WITH OVERRIDE

GO

SP_CONFIGURE 'show advanced options', o

RECONFIGURE WITH OVERRIDE

GO

2)Add mail account to SQL Server.

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = 'xyz_Mail_Account'

, @description = 'Send mails through SQL Server'

, @email_address = 'Your Email ID'

, @display_name = 'xyz'

, @replyto_address = 'Your Email ID'

, @mailserver_name = 'Host Name'

, @username = 'Your Email ID'

, @password = 'Account Password'

, @port = 587

, @enable_ssl = 1

GO

3) Create User account Profile using inbuilt stored procedure

EXEC msdb.dbo.sysmail_add_profile_sp 

    @profile_name = 'xyz_Email_Profile', 

    @description = 'Send emails using SQL Server'

    GO

4)Add User account to Profile using inbuilt stored procedure

EXEC msdb.dbo.sysmail_add_profileaccount_sp 

    @profile_name = 'xyz_Email_Profile',

    @account_name = 'xyz_Mail_Account',

    @sequence_number = 1

    GO

5) Send email using inbuilt stored procedure

EXEC msdb.dbo.sp_send_dbmail 

    @profile_name = ' xyz_Email_Profile', 

    @recipients = 'Recipient Email ID', 

    @subject = 'Email from SQL Server', 

    @body = 'This is my test email.', 

    @importance = 'HIGH'

    GO

        Detail explanation is available in below video

How to grant permissions to user account in SQL server ?

In this video, I have explained how to grant permissions to user account in SQL Server.

Steps:-

1) Login into SQL server.

2) Expand databases, select database and expand security.

3) Expand users, all the users of database will appear.

4) Select particular user, right click on it and go to properties. New window will         appear.

5) Select Securable page.

6) Click on search button and add objects of all types

7) Select object type whose access you want to give to user. (for e.g. table or           stored procedures) and click on ok.

8) Select permissions for that object and click on ok.

    Above steps are explained in detail in below video

How to change SQL Server account password ?

In this video, I have explained How to change SQL Server account password. This video is helpful for beginners.

Steps:-

1) Open SQL server.

2) Enter your current username and password.

3) Click on connect.

4) On your left hand side tree will appear, expand security.

5) Expand Logins

6) Right click on sa, open properties. Login properties will appear.

7) Type password and confirm password. Both should be same.

8) Click on Ok.

Watch this video on same topic:-

Different Types Of Clauses In SQL Server

 
In this video, I have explained different types of clauses in SQL server. This video is helpful for beginners to learn SQL Server concept. 

• Clauses are used with queries to find out exact data from table.
• Clauses can be used with all join types
  
  Following are types of Clauses:-

1. Where Clause
2. Order By Clause
3. Group By Clause
4. Having Clause

How to import excel data into SQL server table ?

Hi guys, in this blog post we will see how to import excel data into SQL server. This post is useful for SQL server beginners. 

    If you want to insert excel data into SQL server table manually then it will take more time. So, you can directly import bulk excel  data into SQL server table using following steps:-

1)

Video Tutorial :-

SQL insert query

 Hi guys, in this blog post we will see how to insert data into SQL table. This blog post is for SQL beginners. Let's see it practically.

SQL insert statement is used to insert new row in SQL table.

Syntax for SQL Insert Query:-

INSERT INTO [TableName]

           (columnName1

           ,columnName2

           ,columnName3

           ,columnName4)

     VALUES

           (columnValue1

           ,columnValue2

           ,columnValue3

           ,columnValue4)

GO 

For e.g. We have table tblEmployee. We want to insert data into following table.

Following SQL query is used to insert data into SQL table tblEmployee. Type the following query in query window and execute the query.

SQL Insert Query:-

INSERT INTO tblEmployee

           (EmployeeFirstName

           ,EmployeeLastName

           ,CreatedDate

           ,ModifiedDate)

     VALUES

           ('Sam'

           ,'Sharma'

           ,CURRENT_TIMESTAMP

           ,CURRENT_TIMESTAMP)

GO

After executing above query data in table will look like:-

For retrieving data from table, you can use following select query:-

SELECT * FROM tblEmployee

Create table in SQL server

  Hi guys , in this blog post we will see how to create table in SQL server. This post is for SQL server beginners.

We can create table in SQL server using UI or by using SQL query.

Create table using UI:-

Please follow below steps:-

1)Go to start menu and search for SSMS(SQL server management studio) and click on SSMS.

2)Login into SQL server. Select server name then enter username and password. Click on connect button.

3) Go to object explorer. Expand databases then expand database. Select tables and right click on it. Select new table option.

4) Enter table column names and data type of column. Specify identity specification column. In my case EmployeeId is auto increment column.

5) Set table primary key.

6) Save table with proper name.

Create table using SQL query:-

SQL Script:-

CREATE TABLE [dbo].[tblEmployee](

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[EmployeeFirstName] [varchar](100) NOT NULL,

[EmployeeLastName] [varchar](100) NOT NULL,

[CreatedDate] [datetime] NOT NULL,

[ModifiedDate] [datetime] NOT NULL

) ON [PRIMARY]

GO