How To Select Manager Name From This Stored Procedure?

I am trying to get an employee manager's first and last name IF the employee has a manager ( Some employee's do not ex CEO etc ). Currently it returns the employee name for manager and if there is no ManagerID in contact it wont return any values

Here is my general table structure for the tables I'm trying to access:

Employee

EmpID

EmployeeNumber

StartDate

isManager

Status ( full time / part time )

ContactID

Contact

ContactID

Fname

Lname

ManagerID

Department

DeptID

Name

DeptHistory

DeptHistID

DeptID

PosTitle

StartDate

EndDate

ModifiedDate

And here is the query I have been manipulating:

SELECT

dh.StartDate, dh.PositionTitle, d.Name, e.EmployeeNumber, e.Classification, e.Status, c1.FirstName, c1.LastName, c1.SIN, c1.DateOfBirth, c1.PhoneNumber, c1.EmailAddress, c1.AddressLine1, c1.AddressLine2, c1.PostalCode, c1.City, c1.Province, (c2.FirstName+ ' ' + c2.LastName) AS Manager

FROM Person.Contact c1

JOIN HumanResources.Employee e ON c1.ContactID = e.ContactID

JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID

JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID

JOIN Person.Contact c2 ON c2.ManagerID = e.EmpID

WHERE

e.EmpID = @empID

SQL Server - Getting the opposite of a where clause

I have a data set where I would like to filter out rows that meet a specific condition, however it's proving a lot more trickier than I thought it would do.

Essentially I want to do something along the lines of WHERE (column1 NOT LIKE 'Z%' and column2 != '2') with the view that the query will only return rows in which a specific row doesn't have the value like 'Z%' in column1 and doesn't have the value '2' in column2.

For example:

Full data set

column1 | column2
-----------------
ACAL    | 2
-----------------
ZVBBU3  | 2
-----------------
FSKE2   | X

Data set that I want

column1 | column2
-----------------
ACAL    | 2
-----------------
FSKE2   | X

However, it doesn't do that. So I thought there may be a programming approach like WHERE (!(column1 LIKE 'Z% and column2 = '2')) where ! will return the opposite of the criteria specified, but this throws a syntax error. Is there any way of doing what I want to achieve?

Create SQL function for dynamic input values

I have a query:

SELECT Substring(warehouse_id, 1, Len(warehouse_id) - 1)Warehouse_Id FROM   (SELECT (SELECT Cast(ur.warehouse_id AS VARCHAR) + ',' 
        FROM   wms_user_rights ur 
        WHERE  ur.delete_flag = 'N' 
               AND end_date >= Getdate() 
        FOR xml path('')) Warehouse_Id)mm 

It displays this result:

Warehouse_id
------------
11,12

If I don't use that substring, it give this result:

    Warehouse_id
    ---------------
    11

    12

So, my question is:

How do I create a function that will handle all this process that can be handled in one function?

Something like this would be simple:

SELECT FuncName(ur.warehouse_id) FROM  wms_user_rights ur 

I am using sql server 2005

Currently I tried with SubString but I want a Simple Function so that I can use it multiple times

Creating a stored procedure which returns Warehouse_id

I want to create a Stored procedure that will check 5 things from a table and they are as follows:

1. Admin Flag
2. Warehouse_id
3. User_id
4. Delete_flag
5. End_date

After checking this it will return the exact warehouse_id from the table.

The table name is WMS_User_Rights in which Mkey is unique. Please suggest what to do

Below is my Script for WMS_User_Rights table

CREATE TABLE [dbo].[WMS_User_Rights](
    [Mkey] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [Warehouse_Id] [numeric](10, 0) NOT NULL,
    [Admin] [char](1) NOT NULL,
    [User_Id] [numeric](10, 0) NOT NULL,
    [Start_date] [datetime] NULL,
    [End_date] [datetime] NULL,
    [Tran_type] [varchar](10) NULL,
    [Created_By] [numeric](10, 0) NULL,
    [Created_date] [datetime] NULL,
    [Last_Updated_By] [numeric](10, 0) NULL,
    [Last_Updated_date] [datetime] NULL,
    [Delete_Flag] [char](1) NOT NULL,
    [Attribute1] [varchar](250) NULL,
    [Attribute2] [varchar](250) NULL,
    [Attribute3] [varchar](250) NULL,
    [Attribute4] [varchar](250) NULL,
    [Attribute5] [varchar](250) NULL,
    [Attribute6] [varchar](250) NULL,
    [Attribute7] [varchar](250) NULL,
    [Attribute8] [varchar](250) NULL,
    [Attribute9] [varchar](250) NULL,
    [Attribute10] [varchar](250) NULL,
    [Attribute11] [varchar](250) NULL,
    [Attribute12] [varchar](250) NULL,
    [Attribute13] [varchar](250) NULL,
    [Attribute14] [varchar](250) NULL,
    [Attribute15] [varchar](250) NULL
) ON [PRIMARY]

How to have multiple where clauses in SQL query

In the following query I am trying to get specifically a managers name using the 'not so looked up to' subquery. I am only using this subquery as I do not know how to specify another where clause for the 'ManagerID'

I understand that joins should be used in place of these queries as they operate row by row and not as a set but I don't see another way of doing this. Currently this query will not execute as I get the famous "subquery has returned multiple rows" Any help would be appreciated.

SELECT hrdh.StartDate, hrdh.PositionTitle, HumanResources.Department.Name,
       HumanResources.Employee.EmployeeNumber, HumanResources.Employee.Classification, HumanResources.Employee.Status,
       HumanResources.Employee.ManagerID AS ManagerID,
       Person.Contact.FirstName, Person.Contact.LastName, Person.Contact.SIN,
       Person.Contact.DateOfBirth, Person.Contact.PhoneNumber, Person.Contact.EmailAddress, Person.Contact.AddressLine1,
       Person.Contact.AddressLine2, Person.Contact.PostalCode, Person.Contact.City, Person.Contact.Province,

       (SELECT (Person.Contact.FirstName+ ' ' +Person.Contact.LastName) AS manager
        FROM Person.Contact
        INNER JOIN HumanResources.Employee
        ON Person.Contact.ContactID = HumanResources.Employee.ContactID
        WHERE HumanResources.Employee.ManagerID = ManagerID) AS manager

FROM HumanResources.EmployeeDepartmentHistory hrdh, HumanResources.Employee, Person.Contact, HumanResources.Department

WHERE hrdh.EmpID = @empID

What transaction isolation level should I use

I am working on a custom application that allows users to insert / update / delete / retrieve data from a database.

Stored procedures I use affects a few tables so I use a transaction and I want to be able to lock these tables to prevent unintended things from happening when multiple users are inserting , deleting, updating, or retrieving (CRUD) data.

My question is what would be the best isolation level to use here, I have read the MSDN on isolation levels and tried to make sense of it as well as searching around, If someone who has been here done that could comment quickly that would be greatly appreciated.

T-SQL format date by pattern

Is possible change format date by specific pattern ? I need to made a function which has a two parameters. First is date and second is pattern. I need convert more date variants. Goal this function is change US and European date format.

For example i need convert

EU: dd:MM:yyyy hh:mm:ss to US: MM:dd:yyyy hh:mm:ss

On another page i need change

EU: dd/MM/yyyy to US: MM/dd/yyyy

And i have a several next variant to convert

And i want to made a similar function

Formater(euDate, pattern)
BEGIN
....

RETURN usDate

My production server is unfortunately SQL server 2005 and doesn't support function FORMAT(). And function CONVERT() doesn't support some variant of date, which i need convert. So in my current solution i parse EU date at individualy parts (@day = day(@euDate), @month, @year, ...) and join them in new string . And i compare it with input parameter in pattern and return CASE which is equal like pattern. I want to this function make general and simplier.

Thank you for Your advice.