Sql Server Interview Questions and Answers Part 5

1.) What is Stored procedure in Sql server ?                                                                         
Stored Procedure is a set of SQL Statement or PL/SQL Programming which perform a specific task.There is no need to compile stored procedure again and again.

2.) How to create stored procedure in sql server ?                                                             
Syntax:-
Create Procedure Procedure_name
as
sql command....

Ex.

create procedure prcselect

     as

select *from student




3.) What are the different ways to execute stored procedure in sql server ?         

There are three different ways to execute stored procedure as given below:-

1.) execute prcselect

     

2.) exec prcselect

      

3.) prcselect

4.) Can we use out parameter in stored procedure statement  ?                                 
yes.

5.) What are the benefits  of stored procedure?                                                                 

1. No need to recompile the code again and again.
2. It is also more helpful for security purpose.
3. It Reduce the Sql injection problem is sql statement.
4. It is faster than function.

6.) What are the benefit between stored procedure and function?                           

1. Stored procedure may or may not return a value that value is only integer type,but function return the values of any data type.
2. We can use out parameter in stored procedure but not with function.
3. Stored procedure is compile once when we created but function is always recompile whenever we call it.
4. Within a Stored procedure we can write such statement which can affect the database or can be time dependent(for example DML statement).But such statement can not return within function.
5. Stored procedure can not be called within the function but a function is called within a Stored procedure.
6. Exception handling can be done in Stored procedure but not in function.
7. Within a Stored procedure we can write such statement which will display data directly to the user.But In function we can not write such SELECT Statement.

7.) What is the default format of date in sql server?                                                        

'MDY' (Month-date-Year)

8.) Can we change 'MDY'  Format in another format in sql server?                            
Yes.

9.) What  are the number of combination of date format in sql server?                  
3! = 3*2*1=6

10.) What is the use of cast function in sql server?                                                          
This Function is used to convert Particular type value to another type value.
Syntax:-

Cast (<column> as <data type>)

select sid as 'students id',sname as 'name',cast(regdate as varchar(30))as 'joining date' from student

11.) What is the use of convert function in sql server?                                                     
This function is also used to convert one type value to another type value.
Syntax:-
Convert (<data type><column><format>)

select sid as 'student_id',sname as 'Name',convert(varchar(30),regdate,104)as 'date' from student

12.) What is difference between Cast and Convert Function in sql server?           

• Cast is ANSI SQL-92 but Convert is a specific to SQL Server.
• Except 'date' data type ,if we convert any type value into any other type ,there is no difference between Cast and Convert function.
• If we want to convert date data type value to varchar' data type the convert function will be used.Because date format is exist in Convert function not Cast function.