Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Store procedure in asp.net c# SQL Server

Store procedure in ms sql


In this tutorial I am going to Talk About "Store procedure in asp.net c# SQL Server". A stored procedure (SP) is a precompiled set of T-SQL statements, the basic purpose of which is to group T-SQL statements together to perform a task or set of multiple tasks. It is stored in the data dictionary and can be executed either from the SQL Server Management Studio or from within an application as required.

store-procedure-in-ms-sql


Stored procedures can execute batches of T-SQL statements, and return multiple result sets as well. It can accept input parameters and can return one or more output parameters to the calling procedure or batch. A stored procedure can call other stored procedures and can also return status information to the calling procedure to indicate whether they succeeded or failed.

Benefits of a Stored Procedure

Modular Programming – After a Stored Procedure is created, it can be invoked multiple times from multiple places from any application. If any modification is needed in the SP, it needs to be done only at one place. It increases code reusability.

Improved Performance – Stored Procedures executes the code faster and reduces the network traffic.

Faster execution – Stored procedures are precompiled i.e; parsed and optimized as soon as they are created and are stored in memory. Thus it executes a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimize your SQL code every time it runs.

Reduced network traffic – Sending many lines of SQL code from an application over the network to SQL Server, impacts network performance. This is especially true if the SQL code is lengthy and is accessed frequently in the application. Running the code on the SQL Server (as a Stored Procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied ti invoke the SP and the results of any query. Stored Procedures limit data with WHERE clauses, ensuring that your application sends just the necessary data over the network wire.

Security – Users can execute a stored procedure without needing to execute any of the SQL statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way. You can grant rights to the stored procedure without granting rights to the underlying objects.

Stored Procedures can be used to make SQL injection attacks more difficult to execute successfully.
Stored Procedures enable you to perform error-handling at the server.

Syntax of a Stored Procedure:

CREATE PROCEDURE procedure_name

sql_Parameter

AS

Begin
sql_statement

End



Types of Stored Procedures

There are three types of Stored Procedures:

User Defined Stored ProceduresUser Defined stored procedures are created by normal users like us.

SQL Server System Stored Procedures – System stored procedures usually begin with sp_ and most of them live in the Master database. They handle much of what is considered to be the nuts-and-bolts of SQL Server administration, and are written in T-SQL just like user defined SPs. Many vital functions of SQL Server rely on these SPs to remain intact!

Extended Stored Procedures – Extended stored procedures usually begins with xp_ or sp_ and also live in the Master database. They are not written in T-SQL. They are in fact compiled DLLs which open up a world of functionality to the SQL Server environment.


Read More

How to execute function in sql with parameters - Solved

Execute function in SQL with parameters: In this tutorial, I am going to show with you how you can execute the function of SQL with parameter. We will also talk about the single parameter as well as multiple parameters. 

execute function in sql with multiple parameters


Steps

1. 
Make a SQL Function with parameter
2. Call it With select keyword

Let's See step by step process to execute SQL function with parameters. In the below function, I have a create a function which will find the date of birth. You can make any function but the function calling process will be the same.




Execute function in SQL with Single parameters

Create function age
(
@dob date
)
RETURNS int
AS
BEGIN
     declare @age int
     set @age=DATEDIFF(YEAR,@dob,GETDATE())-
     case
         when (month(@dob) > month(GETDATE())) or
               (month(@dob) = month(GETDATE()) and day(@dob) > day(GETDATE()))
         THEN 1
         ELSE 0
     END  
     RETURN @age
END

select dbo.age('01/02/2014')
select dbo.age('01/02/2014') as age



Execute function in SQL with Multiple parameters

In below function, I have a created function which is used to count record from a table, In this function, you will see the use of multiple parameters.

Calling of SQL function with multiple parameters

  select DBO.Login('Sana',123)

Create a table and Insert some Record then make a function. Definitely, You will be able to understand better.


CREATE TABLE [dbo].[Mytbl]
(
          [Name] [varchar](20) Primary Key Identity,
          [PhoneNo] [int] NULL,
          [Gender] [varchar](5) NULL,
          [Pass] [int] NULL,
          [Address] [varchar](20) NULL
) ON [PRIMARY]


INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sana', 9125, 'F', 123, 'Noida')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sana', 9125, 'F', 123, 'Noida')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sanjay', 8989,'M', 0, 'Noida')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Manu', 8978, 'M', 0, 'Gzbd')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ram', 8958, 'M', 78945, 'Mayur Vihar')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Sanjay Yadav', 121,'M', 0, 'noida')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ramesh ', 343, 'M', 23, 'AshokNagar')
INSERT [dbo].[Mytbl] ([Name], [PhoneNo], [Gender], [Pass], [Address]) VALUES ('Ramesh ', 343, 'M', 23, 'AshokNagar')
  

Create a function with multiple parameters


Create function [dbo].[Login](@n varchar(20),@pwd int)
returns int
as
  begin
       declare @temp int
             select @temp=count(*) from Mytbl where Name=@n and Pass=@pwd
             return @temp
  end

  select DBO.Login('Sana',123)


Read More

Important Sql complex queries interview questions

Introduction: In this tutorial, I am going to explain the SQL complex queries which is asked in every interview questions. So let's start today's tutorial. So let's start SQL complex queries fo practice with answers.


Important Sql complex queries interview questions


1. To add new 
Alter table employee add gender varchar(5)

2.Drop a column
Alter table employee drop column gender

                       Some Ms. Sql commands with examples

3.Or operator  in SQL
SELECT * from employee where employeeid=or employeeid=1

4.IN function in SQL 
SELECT * from employee where employeeid in(2,3)

5. Find record Not in SQL table using  IN function
SELECT * from employee where employeeid not in (1,2)

6.Between function in SQL
SELECT * from employee where employeeid between 1 and 2

7.Select commands with order by clause
SELECT * from employee order by name 
SELECT * from employee order by name desc
SELECT * from employee order by name asc

8.Max Function
SELECT max(name) from employee
SELECT max(salary) from employee

9.Max Function
SELECT min(salary) from employee
SELECT min(name) from employee

10.Avg Function
SELECT AVG(salary) from emp

11.LEFT Function
SELECT LEFT('Manish',3)
Result:Man


12.RIGHT Function
SELECT RIGHT('Manish',3)
Result:ish

13.CHARINDEX Function- Is use to find the index(position) of any character
SELECT CHARINDEX('i','Manish')
Result:4

14.LEN Function- Is use to find the length of character
SELECT LEN('Manish')
Result:6


15.GETDATE ()
Returns a datetime data type which contains current system data and time: 2018-09-24 09:51:36.568.
SELECT GETDATE()

16.Use of AND Operator
SELECT * from employee where employeeid=2 and employeeid=5
SELECT * from employee where employeeid=2 and salary=600

17.Use of OR Operator
SELECT * from employee where employeeid=2 or employeeid=5
SELECT * from employee where employeeid=2 or salary=4000

18.Use of IN Operator
SELECT * from employee where employeeid in (2,5,8)

19.Use of Between Operator
SELECT * from employee where employeeid between 3 and 7

20.Use of WildCard Operator
SELECT * from employee where name not like 'a%'
SELECT * from employee where name like '%a'
SELECT * from employee where name like '%m%'
SELECT * from employee where name like '_a%'
SELECT * from employee where name like '%a_'
SELECT * from employee where name like '[a,b]%'
SELECT * from employee where name like '[a-p]%'

21.Use of SUBSTRING() Function
SELECT SUBSTRING('Mohan',1,3)
Result:Moh





Read More