Microsoft SQL Server Tutorials

SQL - Procedures with Parameters

Procedures with Parameters in SQL Server

Write a program to add the two values with Parameters Procedure. 

create procedure test2(@a int,@b int)

as 

begin

declare @c int set @c=@a+@b;

print 'Addition of two variables are:-'+cast(@c as varchar); 

end
 

Write a program to perform arithmetic operations of two values with Parameters Procedure.

create procedure test3(@a int,@b int) as

begin

declare @x int,@y int,@z int,@s int set @x=@a+@b;

set @y=@a-@b; set @z=@a*@b; set @s=@a/@b;

print 'Add of two variables are:-'+cast(@x as varchar); print 'Sub of two variables are:-'+cast(@y as varchar); print 'Mul of two variables are:-'+cast(@z as varchar); print 'Div of two variables are:-'+cast(@s as varchar); 

end

Create a procedure to display employee details to the user 

Create procedure spselect

as 

begin

select * from Employee 

end

Output: Execute spselect

Create a procedure to accept employee ID and delete the record from employee table.

create procedure spdel @eid int

as 

begin

delete from Employee where EmpID=@eid
 

end

Output: Exec spdel 4

Create a procedure to accept employee ID and update the employee details from employee table.

create procedure spupdate

@eid int,@ename varchar(max),@salary money,@address char(30) as

begin

update Employee set EmpName=@ename,Salary=@salary,Address=@address where EmpID=@eid

end

Output: Exec spupdate 1,'kamal',88000,'vizag' 

Create a procedure to add records in employee table.

create procedure spinst

@eid int,@ename varchar(50),@salary money,@address varchar(50) as

begin

insert into Employee values(@eid,@ename,@salary,@address) end

Output: exec spinst 6 ,'Suman' ,41000 ,'chennai'
 

Create a procedure to insert records in two tables.

Create procedure spinserttwotables

@eid int,@ename varchar(50),@salary money,@Address char(40),@Deptno int,@Dname char(30),@Loc char(20) as

begin

insert into Employee values(@eid,@ename,@salary,@Address,@Deptno) insert into Dept values(@Deptno,@Dname,@Loc)

end

Output: exec spinsert 7,'mohan',62000,'mumbai',10,'dotnet','hyd'

logo

Microsoft SQL Server

Beginner 5 Hours

Procedures with Parameters in SQL Server

Write a program to add the two values with Parameters Procedure. 

create procedure test2(@a int,@b int)

as 

begin

declare @c int set @c=@a+@b;

print 'Addition of two variables are:-'+cast(@c as varchar); 

end
 

Write a program to perform arithmetic operations of two values with Parameters Procedure.

create procedure test3(@a int,@b int) as

begin

declare @x int,@y int,@z int,@s int set @x=@a+@b;

set @y=@a-@b; set @z=@a*@b; set @s=@a/@b;

print 'Add of two variables are:-'+cast(@x as varchar); print 'Sub of two variables are:-'+cast(@y as varchar); print 'Mul of two variables are:-'+cast(@z as varchar); print 'Div of two variables are:-'+cast(@s as varchar); 

end

Create a procedure to display employee details to the user 

Create procedure spselect

as 

begin

select * from Employee 

end

Output: Execute spselect

Create a procedure to accept employee ID and delete the record from employee table.

create procedure spdel @eid int

as 

begin

delete from Employee where EmpID=@eid
 

end

Output: Exec spdel 4

Create a procedure to accept employee ID and update the employee details from employee table.

create procedure spupdate

@eid int,@ename varchar(max),@salary money,@address char(30) as

begin

update Employee set EmpName=@ename,Salary=@salary,Address=@address where EmpID=@eid

end

Output: Exec spupdate 1,'kamal',88000,'vizag' 

Create a procedure to add records in employee table.

create procedure spinst

@eid int,@ename varchar(50),@salary money,@address varchar(50) as

begin

insert into Employee values(@eid,@ename,@salary,@address) end

Output: exec spinst 6 ,'Suman' ,41000 ,'chennai'
 

Create a procedure to insert records in two tables.

Create procedure spinserttwotables

@eid int,@ename varchar(50),@salary money,@Address char(40),@Deptno int,@Dname char(30),@Loc char(20) as

begin

insert into Employee values(@eid,@ename,@salary,@Address,@Deptno) insert into Dept values(@Deptno,@Dname,@Loc)

end

Output: exec spinsert 7,'mohan',62000,'mumbai',10,'dotnet','hyd'

Related Tutorials

Frequently Asked Questions for Microsoft SQL Server

To install Microsoft MySQL Server on Windows, you need to download the installer from the official MySQL website. Follow the on-screen instructions to complete the installation process and set up the server.

To upgrade MySQL Server, download the latest version from the official site, run the installer, and follow the upgrade instructions. Backup your data before starting the process.

You can integrate MySQL Server with web applications written in languages like PHP, Node.js, and Python by using the appropriate MySQL connector libraries.<

Stored procedures are precompiled SQL statements that can be executed repeatedly. They improve performance by reducing the amount of SQL code sent to the server.

The MySQL root user is the default administrator account with full privileges. To secure it, ensure strong password protection, disable remote access, and grant minimal permissions.

Microsoft MySQL Server is an open-source relational database management system (RDBMS) that provides high-performance data storage and retrieval. It is often used for web applications and other data-driven platforms.

The default username for Microsoft MySQL Server is "root". During the installation process, you'll set a password. If you forget it, you can reset the MySQL root password using the command line.

Common MySQL error codes include 1045 (Access Denied), 1062 (Duplicate Entry), and 2002 (Can't Connect to MySQL Server).

Some advantages include its high availability, scalability, open-source nature, and the support of a large community of developers. Microsoft MySQL Server is also compatible with various platforms and provides advanced data security features.

The MySQL query cache stores the result of SELECT queries to speed up response times for identical queries. You can enable it in the MySQL configuration file.

InnoDB supports ACID-compliant transactions, foreign keys, and row-level locking, making it more suitable for high-concurrency environments. MyISAM is faster for read-heavy operations but lacks transaction support.

You can manage users in MySQL using the CREATE USER, GRANT, and REVOKE commands to set permissions for specific users.

To configure Microsoft MySQL Server, you need to access the MySQL Configuration File (my.ini) and make necessary adjustments like setting up users, enabling ports, and optimizing server performance.

You can use the SHOW STATUS and SHOW VARIABLES commands to monitor MySQL performance. Alternatively, use tools like MySQL Workbench and Percona Monitoring and Management (PMM).

To optimize MySQL Server performance, you can tweak configuration settings like innodb_buffer_pool_size, query_cache_size, and tmp_table_size. Indexing and query optimization also play a crucial role.

The primary difference is that MySQL is open-source and cross-platform, while SQL Server is a proprietary product by Microsoft that primarily runs on Windows. MySQL is more popular in the open-source community, while SQL Server is used in enterprise environments.

To repair a corrupt table, you can use the REPAIR TABLE command in MySQL:

REPAIR TABLE table_name;

If you're facing MySQL connectivity issues, ensure that the MySQL service is running, the port is open, and your firewall settings are correctly configured.

You can import data into MySQL using the LOAD DATA INFILE command or by using tools like phpMyAdmin or MySQL Workbench.

To enable foreign keys, ensure your tables use the InnoDB storage engine. Then, define the FOREIGN KEY constraint in your table creation statement.

To check the version, simply open the command prompt or terminal and type:

mysql --version

This will return the current version of Microsoft MySQL Server installed on your system.

MySQL data types define the type of data a column can store. Some common types include VARCHAR, INT, DATE, TEXT, and BLOB.

To create a new database, use the following SQL command:

CREATE DATABASE database_name;

You can back up a MySQL Server database using the mysqldump command. For example:

mysqldump -u username -p database_name > backup_file.sql

Yes, Microsoft MySQL Server can run on various platforms, including Linux, Windows, and macOS, making it a versatile choice for developers.

line

Mail us :

Address :

India
  • Facebook_logo
  • Instagram_logo
  • Linkedin_logo
  • X-twitter_logo
  • Youtube_logo
  • Pinterest_logo

Sub Categories

Copyrights © 2024 letsupdateskills All rights reserved