CREATE PROCEDURE FOR GET-GROUP-DETAILS BY ROLE

CREATE PROCEDURE [dbo].[sp_GroupDetails_byUserID]

      @UserId int

AS

DECLARE @RoleID bigint

BEGIN

      SET NOCOUNT ON;

      BEGIN TRY

            SET @RoleID=(SELECT ROLE_ID

FROM ROLE_MEMBER

WHERE [USER_ID]=@UserId)

            IF @RoleID = 1

                  BEGIN

                        -- select user group1 here!!!

                  END              

            IF @RoleID = 2

                  BEGIN

                         -- select user group2 here!!!

                  END

            IF @RoleID = 3

                  BEGIN

                         -- select user group3 here!!!

                  END              

      END TRY

     

      BEGIN CATCH

            declare @Error varchar(8000)

            set @Error= Convert(varchar,ERROR_NUMBER()) + '*****' + Convert(varchar(4000),ERROR_MESSAGE()) + '*****' + isnull(Convert(varchar,ERROR_PROCEDURE()),'sp_GroupDetails_byUserID') + '*****' + Convert(varchar,ERROR_LINE()) + '*****' + Convert(varchar,ERROR_SEVERITY()) + '*****' + Convert(varchar,ERROR_STATE())

            RAISERROR (@Error, -- Message text.                        

                        16, -- Severity.

                        1 -- State.

                       );

      END CATCH

END

NEW keyword in C#

NEW keyword  in C# can be used as an operator, a modifier, or  constraint.

new Operator:  Used to create objects and invoke constructors.

new Modifier:   Used to hide an inherited member from a base class member.

new Constraint:  Used to restrict types that might be used as arguments for a type parameter in a generic declaration.

Find the 2nd,3rd and 4th Highest Salary in SQL Server

-- 2nd Highest Salary

select max(emp_salary) as [2nd Highest Salary]

      from tblSalary

      where emp_salary

      not in (select top 1 emp_salary

                     from tblSalary

                     GROUP BY emp_salary

                     order by emp_salary desc)

                    

-- 3rd Highest Salary

select max(emp_salary) as [3rd Highest Salary]

      from tblSalary

      where emp_salary

      not in (select top 2 emp_salary

                     from tblSalary

                     GROUP BY emp_salary

                     order by emp_salary desc)

                    

-- 4th Highest Salary

select max(emp_salary) as [4th Highest Salary]

      from tblSalary

      where emp_salary

      not in (select top 3 emp_salary

                     from tblSalary

                     GROUP BY emp_salary

                     order by emp_salary desc)

                    

Notes:

1. emp_salary is the name of column from tblSalary table

2. tblSalary is the name of table

3. Similarly we find the Nth Highest Salary from SQL Server above examle.