Question 1: Create Function without Parameter?
Ans: create function AddFun()
returns int
as begin
Declare @Result as int
Set @Result=2+3
return @Result
end
For O/P: Print test.dbo.AddFun() is 5
Question 2: Create Function with Parameter?
Ans: create function AddFun1(@NumIst int,@NumpIInd int)
returns int
as begin
Declare @Result as int
Set @Result=@NumIst+@NumpIInd
return @Result
end
For O/P: Print test.dbo.AddFun1(10,20) is 30
Question 3: How to Create Procedure?
Ans: A) for Insert
create procedure sp_NameofProc
(
@Id int,
@Name varchar(50),
@Age Varchar(50),
@City Varchar(50)
)
as begin
insert into tableName(Id,[Name],Age,City)values(@Id,@Name,@Age,@City)
end
B) for Update
create procedure sp_NameofProc1
(
@Id int,
@Name varchar(50),
@Age Varchar(50),
@City Varchar(50)
)
as begin
update tableName set Name='Anil' where Id=@Id
end
C) For select
create procedure sp_NameofProc2
as begin
select * from TableName
end
Question 4: How to call a function in Procedure?
Ans: Step1: create a function
create function AddFun_sp(@NumIst int,@NumpIInd int)
returns int
as begin
Declare @Result as int
Set @Result=@NumIst+@NumpIInd
return @Result
end
Step2: Call a function in store procedure
create procedure sp_CallAfunction
(
@IstVar int,
@IIndVar int
)
as begin
Declare @sp as int
set @sp=dbo.AddFun1(@IstVar,@IIndVar)
print @sp
end
Step3: Execute Store procedure for Output
execute sp_CallAfunction '10','20'
OR
execute dbo.sp_CallAfunction '10','20'
Ans:
Step1: Declare a cursor
declare @EmpName varchar(50)
declare @EmpDepartment varchar(50)
Step2: Define a cursor
declare Cursor_Emply cursor for
select EmpName,EmpDepartment from Emply
Step3: Open a cursor
open Cursor_Emply
Step4: fetch a cursor
fetch Cursor_Emply into @EmpName,@EmpDepartment
while(@@fetch_status=0)
begin
print '* EmpName= '+@EmpName
print ' EmpDepartment= '+@EmpDepartment
--fetch cursor for next rows
fetch Cursor_Emply into @EmpName,@EmpDepartment
end
Step5: Close a cursor
close Cursor_Emply
Step6: deallocate a cursor
deallocate Cursor_Emply
Question 6: How to Call a Cursor in Store procedure?
Ans:
create procedure SPCursor_Emply
as begin
declare @EmpName varchar(50)
declare @EmpDepartment varchar(50)
declare Cursor_Emply cursor for
select EmpName,EmpDepartment from Emply
open Cursor_Emply
fetch Cursor_Emply into @EmpName,@EmpDepartment
while(@@fetch_status=0)
begin
print '* EmpName= '+@EmpName
print ' EmpDepartment= '+@EmpDepartment
--fetch cursor for next rows
fetch Cursor_Emply into @EmpName,@EmpDepartment
end
close Cursor_Emply
deallocate Cursor_Emply
end
Question 7: How to Create trigger?
Ans: ---> Example: view in details Trigger in sql server
CREATE TRIGGER myTrigger3
ON employee
FOR UPDATE, INSERT, DELETE
AS
SELECT 'Before INSERT'
INSERT Employee (ID, Name) VALUES (31, 'Rick')
go
SELECT 'Before UPDATE'
UPDATE Employee
SET Name = 'Test'
WHERE ID = 1
go
SELECT 'Before DELETE'
DELETE FROM Employee WHERE ID = 1
Question 8: How to Create View ‘virtual table’?
Ans: View is a virtual table, which not create physically, it create logically only. We insert, update & delete the records from a view.
create view View_Employee
as
select * from Employee
--select view
select * from View_Employee
Question 9: How to Create Index?
Ans: create index index_Employee on Employee(Id, Name)
Question 10: How to Create Nonclustered Index?
Ans:
CREATE NONCLUSTERED INDEX Indexname_Employee ON Employee
(
[EmpName] ASC or desc,
[EmpDepartment] ASC or desc
)
OR
CREATE NONCLUSTERED INDEX [Indexname_Anil1] ON [dbo].[Emply]
(
[EmpName] ASC or desc,
[EmpDepartment] ASC or desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
Question 11: How to Create Clustered Index?
Ans:
CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee
(
[EmpName] ASC or desc,
[EmpDepartment] ASC or desc
)
CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee
(
[EmpName] ASC,
[EmpDepartment] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
Question 12: What is joining? What type? How to create join?
Ans:
Join:-
Join are used to retrieve data from more than one
Table simultaneously.
Types of join are:-
1. Outer join
*--Right Outer join
*--Left Outer join
*--Full Outer join
2. Multiple join
3. Self join
4. Inner join
5. Cross join
Example: join
Question: How to Error Handling in StoreProcedure ?
Ans:1
create proc sp_emprecord
As begin
select * from Emp_Record1 //Emp_Record1 table name are not in database
end
begin try
execute sp_emprecord
end try
begin catch
select
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine;
end catch
Ans:2
create Proc sp_ErrorInsert
(
@Emp_id nvarchar(50),
@Emp_name nvarchar(50),
@Emp_salary int,
@Emp_age int,
@Emp_qua nvarchar(50)
)
As begin
insert into Emp_Record
(
Emp_id,
Emp_name ,
Emp_salary ,
Emp_age ,
Emp_qua
)values
(
@Emp_id,
@Emp_name ,
@Emp_salary ,
@Emp_age ,
@Emp_qua
)
End
go
Begin Try
execute sp_ErrorInsert
End Try
Begin Catch
SELECT
ERROR_MESSAGE() as ErrorMessage,
ERROR_LINE() as ErreoLine,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_NUMBER() as ReeoeNumber;
End Catch
Note:
Function Description are:
ERROR_NUMBER() Returns the number of the error
ERROR_SEVERITY() Returns the severity
ERROR_STATE() Returns the error state number
ERROR_PROCEDURE() Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE() Returns the line number inside the routine that caused the error
ERROR_MESSAGE() Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
ERROR_NUMBER() Returns the number of the error
ERROR_SEVERITY() Returns the severity
ERROR_STATE() Returns the error state number
ERROR_PROCEDURE() Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE() Returns the line number inside the routine that caused the error
ERROR_MESSAGE() Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
Or
Question: How to ErrorHandl in StoreProcedure with @@Error & @@ROWCOUNT ?
Ans: Use Of @@ROWCOUNT
insert into Emp_Record values('ak04','aks',11111,26,'Master Degree')
if @@ROWCOUNT=0
print'Error !: No inserted record here..'
else
print'Sucsesfully Inserted Record !'
OutPut: (1 row(s) affected)
Sucsesfully Inserted Record !
Again Execute this query the Output is:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Emp_Record'. Cannot insert duplicate key in object 'dbo.Emp_Record'.
The statement has been terminated.
Error !: No inserted record here..
-- ============================================================
-- Author: Anil Singh
-- Create date: 1/10/2011
-- Description: Returns [ListEvent]
-- Parameters: FromDate datetime, ToDate datetime
-- =============================================================
ALTER PROCEDURE [spx].[ListEvent] --'2011-09-26 20:17:20.357' ,'2011-10-06 20:17:20.357'
(
@FromDate datetime,
@ToDate datetime
)
AS BEGIN
BEGIN TRY
SELECT EventName,
EventDesc,
EventDate,
ProhibitEntriesTime,
ClaimTime,
RedrawUnclaimed
FROM pr.PromoEvents
WHERE EventDate = @FromDate AND EventDate = @ToDate
END TRY
BEGIN CATCH
declare @Error varchar(8000)
set @Error= Convert(varchar,ERROR_NUMBER()) + '*****' + Convert(varchar(4000),ERROR_MESSAGE()) + '*****' + isnull(Convert(varchar,ERROR_PROCEDURE()),'GetPromotionList') + '*****' + Convert(varchar,ERROR_LINE()) + '*****' + Convert(varchar,ERROR_SEVERITY()) + '*****' + Convert(varchar,ERROR_STATE())
RAISERROR (@Error, -- Message text.
16, -- Severity.
1 -- State.
);
END CATCH
END
Use Of @@ERROR
Using @@ERROR
@@ERROR is raised only for errors, not for warnings; batches, stored procedures, and triggers cannot use @@ERROR to detect any warnings that have occurred.
A common use of @@ERROR in SQL Server 2000 and earlier is to indicate the success or failure of a stored procedure. An integer variable is initialized to 0. After each Transact-SQL statement completes, @@ERROR is tested for being 0, and if it is not 0, it is stored in the variable. The procedure then returns the variable on the RETURN statement. If none of the Transact-SQL statements in the procedure had an error, the variable remains at 0. If one or more statements generated an error, the variable holds the last error number. The following example shows a simple stored procedure with this logic.
using @@ERROR as the primary means of detecting errors leads to a very different style of error-handling code than that which is used with TRY…CATCH constructs.
- @@ERROR must be either tested or saved after every Transact-SQL statement because a developer cannot predict in advance which statement might generate an error. This doubles the number of Transact-SQL statements that must be coded to implement a given piece of logic.
- TRY…CATCH constructs are much simpler. A block of Transact-SQL statements is bounded by BEGIN TRY and END TRY statements, and then one CATCH block is written to handle errors that might be generated by that block of statements.
Outside of a CATCH block, @@ERROR is the only part of a Database Engine error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings (object names, for example) are returned only to the application in which they can be processed using the API error-handling mechanisms. If the error invokes a CATCH block, the system functions ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_NUMBER, ERROR_SEVERITY, and ERROR_STATE can be used.
Using RAISERROR
RAISERROR is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine.
RAISERROR can return either:
- A user-defined error message that has been created using the sp_addmessage system stored procedure. These are messages with a message number greater than 50000 that can be viewed in the sys.messages catalog view.
- A message string specified in the RAISERROR statement.
RAISERROR can also:
- Assign a specific error number, severity, and state.
- Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
- Substitute argument values into the message text, much like the C language printf_s function.
Both RAISERROR and PRINT can be used to return informational or warning messages to an application. The message text returned by RAISERROR can be built using string substitution functionality similar to the printf_s function of the C standard library, whereas PRINT can only return a character string or character expression. A RAISERROR severity of 11 to 19 executed in the TRY block of a TRY…CATCH construct causes control to transfer to the associated CATCH block. Specify a severity of 10 or lower to return messages using RAISERROR without invoking a CATCH block. PRINT does not transfer control to a CATCH block.
When RAISERROR is used with the msg_id of a user-defined message in sys.messages, msg_id is returned as the SQL Server error number, or native error code. When RAISERROR is used with a msg_str instead of a msg_id, the SQL Server error number and native error number returned is 50000.
When you use RAISERROR to return a user-defined error message, use a different state number in each RAISERROR that references that error. This can help in diagnosing the errors when they are raised..
Use of RAISERROR
Help in troubleshooting Transact-SQL code.
· Check the values of data.
· Return messages that contain variable text.
· Cause execution to jump from a TRY block to the associated CATCH block.
· Return error information from the CATCH block to the calling batch or application
Example: Use of RAISERROR
Begin Try
execute sp_emprecord
End Try
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
-- =============================================
-- Author: <Author,,Anil Singh>
-- Create date: <Create Date, ,01/12/2011>
-- Description: <Description, ,Testing to function>
-- =============================================
CREATE FUNCTION sumofTwonum
(
-- Add the parameters for the function here
@Param1 int,
@Param2 int
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int
-- Add the T-SQL statements to compute the return value here
SET @Result=@Param1*@Param2;
-- Return the result of the function
RETURN @Result
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Anil Singh>
-- Create date: <Create Date,,01/12/2011>
-- Description: <Description,,Call a function in procedure>
-- =============================================
CREATE PROCEDURE sp_callFunction
(
-- Add the parameters for the stored procedure here
@Param11 int,
@Param22 int
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @spResult as int --varchar(100);
BEGIN TRANSACTION
-- Call the Function.
SET @spResult=dbo.sumofTwonum(@Param11,@Param22);
COMMIT TRANSACTION
print 'Call function in Procedure'+@spResult;
END
GO
-- EXAMPLE: WHILE Loop
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
-- Example of WHILE Loop with BREAK keyword
DECLARE @COUNT INT
SET @COUNT = 1
WHILE (@COUNT <=10)
BEGIN
PRINT @COUNT
SET @COUNT = @COUNT + 1
IF @COUNT = 5
BREAK;
END
-- Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag <=5)
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
CONTINUE;
IF @Flag = 4 -- This will never executed
BREAK;
END
--
DECLARE @intFlag1 INT
SET @intFlag1 = 1
PRINT @intFlag1
WHILE (@intFlag1 <=5) BEGIN
SET @intFlag1 = @intFlag1 + 1
IF @intFlag1 = 4 -- This will never executed
CONTINUE;
ELSE
PRINT @intFlag1
END
-------------------
declare @i int
declare @j int
print @j;
set @i=1
set @J=2
while (@i<=5)
begin
print @i;
set @j=@i*@j
print @j;
set @i=@i+1
PRINT @i;
end
-------------------
declare @I1 int
declare @J2 int
print 'THIS IS INT NUMBER'+ CAST(@I1 AS VARCHAR)
set @I1=1
set @J2=2
while (@I1<=5)
begin
print @I1;
set @J2=@I1*@J2
print @J2;
set @I1=@I1+1
PRINT @I1;
end
------------------------
No comments:
Post a Comment