SELECT  u1.EMPLOYID, u1.LASTNAME, u1.FRSTNAME, u2.ADDRESS1, u2.CITY, u2.STATE, u2.ZIPCODE, IsNull(u3.CHEKDATE,'') as CHEKDATE

FROM   UPR00100 u1

inner join UPR00102 u2

on u1.EMPLOYID = u2.EMPLOYID

left outer join UPR30300 u3

on u1.EMPLOYID = u3.EMPLOYID and...

Your query returns AAAA as the only person that took the holiday and is in the list.  CCCC did not take the holiday and XXXX is not in the list.  Given...

You can compare fields of any length but without the size, SQL doesn't know how big the parameter should be. 

Try adding this and see what you get:

CREATE PROCEDURE dbo.GET_USER_DETAIL...

What is the table definition of APP_USER?  How are you calling the procecedure?

The create statement should be something like this:

CREATE PROCEDURE dbo.GET_USER_DETAIL (@lan_id varchar(255)) where 255 is the same size...

Use this:

CASE WHEN fieldname3 IS NULL THEN 0 ELSE 1 END As fieldvalue

Without knowing your system, problems often arise in SQL databases over time due to the increase in data.  5 months ago, your database was new and now it has 5...

create procedure test

as

select * from sysdatabases where dbid = 1

go

exec test

Try something like this:

 Order By

   case when @Field =  'surname' and @SortAscending = 1 then surname else null end asc

  ,case when @Field =  'bookings'  and @SortAscending = 1 then bookings...

MINUS is not valid SQL syntax

What you should do is something like:

SELECT IsNull(b.Bookings, 0) - IsNull(d.Deductions, 0) AS Revenue

LEFT OUTER JOIN (select id, sum(bookings) Bookings...) B ON ...

LEFT OUTER JOIN (select id,...

You would use LEFT OUTER JOIN instead of INNER JOIN for each of the derived tables if it is possible that no results may be returned

This may work for you:

--Build a table of numbers, you may want to make it a real table because it is a good tool to use

declare @Seq Table(ID int)

insert into...

Since the value in the database is "2/22/2006 11:40:40 AM" and you are passing in "2/22/2006" when doing the retrieval they are not the same because if no time is...

I would return the data as one row and leave the presentation of the data to the application being used to display the data.

If, however the data is being presented...

Thinking outside the box, you could also do something like this and not even use a table:

declare @1stPayDue char(3)

declare @NextPayDue char(3)

select @1stPayDue  = 'nov',

        @NextPayDue = 'apr'

declare @1stDate datetime

declare...

I would store the data in a table such as:

Create Table PaymentDate(FirstPayDue char(3), NextPayDue char(3), Number int)

and then use:

Select Number from PaymentDate where FirstPayDue = @1stPayDue and NextPayDue = @NextPayDue