Tuesday, December 27, 2011

SQL Server( Basic Questions & Answers)


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'

 Question 5: How to Create Cursor in Sql Server?
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

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
)

                                                         OR
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.
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 =-- 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: