Stored Procedure Stored Procedure can have multiple select statement Stored Procedure can have parameters Stored Procedure exists in database Stored procedure return predefined result View View can have only one select statement View does not have any parameter View are virtual and does not exists in database Views can return different result based on where condition.
Joins are used to get data from two or more tables. We can combine the tables data with the common fields between tables. There are four types of Joins in SQL Server Inner Join Outer Join Left outer join (Left Join) Right outer join (Right Join) Full Outer Join (Full Join) Cross Join Self Join Continue Reading…
ERROR_MESSAGE returns the error message cause in SQL Server statement. The Scope of ERROR_MESSAGE is within catch block of try catch. If called outside the catch block it will return NULL. Example Result ErrorMessage Conversion failed when converting the varchar value ‘Hitesh’ to data type int. When called outside the catch block ErrorMessage NULL
Charindex is used to search an expression in another expression and return its starting position if found else will return 0. Syntax : CHARINDEX ( expression_ToFind ,expression_ToSearch) Example : Result : 15
These are related to security in SQL Server. GRANT – It is used to give access permission for any object in SQL Server. All the users with this ROLE_NAME can only select data from TABLE_NAME Table. DENY –It is used to block access permission for any object in SQL Server. All the users with this ROLE_NAME Continue Reading…
It returns the starting position of the first occurrence of a pattern in a specified String. If the pattern is not found then it will return zero. It applies on all valid text and character data types. Syntax : PATINDEX ( ‘%pattern%’ , String_Exp) Example : Result : 3 In this example first occurrence of Continue Reading…
WaitFor Delay : It is used for a specified duration relative to the current time. WaitFor Time : It is used for actual time of day.
Len() function returns number of characters in the input string. Datalength() function returns number of bytes in the input string. Example : Output : 12 and 24
@@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(‘tablename’) are used to get last inserted identity of record. @@IDENTITY : It is used to get last inserted identity of record. It is limited to current Session but it is not limited to current scope. If you have a trigger on a table that causes an identity to be created in another table, Continue Reading…
TRY_CONVERT Function was introduced with SQL Server 2012. This function is similar to previous CONVERT Function but this Function will return null if the value provided is not convertible. Syntax: TRY_CONVERT(datatype,Value) Example: Result: 100 Result: (null)