The ISO date format is probably best for this; so something like:

DECLARE @DiskStr NVARCHAR(255)

SET @DiskStr = N'C:\CollSoftware2_' + CONVERT(NVARCHAR(8), GETDATE(), 112) + N'.bak'

BACKUP DATABASE [CollSoftware2]

TO  DISK = @DiskStr

WITH  INIT...

You will have to use dynamic SQL.

exec('SELECT xx FROM ' + @Variable)

Declare @Memberid as varchar (50)

Declare @ProcCode as Varchar (15)

set @memberid = '-1'

Set @ProcCode = 'G0202'

Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible

where Membid = COALESCE(NULLIF( @memberid, '-1'), Membid)

and ProcCode =...

Forgot about no Decimal places:

DECLARE @d DECIMAL(18,9)

 ,@S VARCHAR(20)

 ,@R VARCHAR(20)

 ,@Pos SMALLINT

SET @d = 0.0

SET @s-2 = CAST(@D AS VARCHAR(20))

SET @r = REVERSE(SUBSTRING(@S, CHARINDEX('.', @s-2) + 1, 20))

SET @Pos = PATINDEX('%[1-9]%' ,...

DECLARE @d DECIMAL(18,9)

 ,@S VARCHAR(20)

 ,@R VARCHAR(20)

SET @d = 0.8333

SET @s-2 = CAST(@D AS VARCHAR(20))

SET @r = REVERSE(SUBSTRING(@S, CHARINDEX('.', @s-2) + 1, 20))

SELECT LEN(SUBSTRING(@R, PATINDEX('%[1-9]%', @r), 20))

You are comparing two different datatypes with NULLIF( @memberid,-1). As INT has a higher precedence than VARCHAR, the VARCHAR will be implicitly converted to an INT before the comparison is...

>> Can you put SELECT statements inside that COALACSE statement???

You can, but the outer joins should produce the NULLs for you. Try:

SELECT E.EMPID

 ,COALESCE(L.PlantCode, H2.B_COMN_SITE_NO, 'N/A') AS HRLocation

 ,COALESCE(L.PlantCode, P2.B_COMN_SITE_NO, 'N/A') AS...

I am not sure what you are trying to do, but a brief look suggests that you may be able to get away with a select along the lines of:

SELECT...

FYI, the second version should be faster as it scans YourTable once instead of five times.

If possible, normalize the table.

If you have to live with it, something LIKE this should work.

SELECT D.u_id, D.job_id, D.Hours

FROM (

 SELECT u_id, job_id, m_hours as Hours, 1 as HourOrder FROM YourTable

 UNION...

An obvious typo on my part which is now corrected:

SELECT B.BookID AS Publication

 ,B.Title

 ,B.ISBN

 ,B.YearOfPublication

 ,P.PublisherName

 ,B.PlaceOfPublication

 ,B.Verified

 ,B.BookType

 ,P1.Surname + ' ' + P1.Initials

  + ISNULL(', ' + P2.Surname + ' ' + P2.Initials, '')

  +...

Assuming:

1. A book has at least one author

2. dbo.BookAuthor.[Sequence] goes from 1 for first author to 3 for third author

Try something like:

SELECT B.BookID AS Publication

 ,B.Title

 ,B.ISBN

 ,B.YearOfPublication

 ,P.PublisherName

 ,B.PlaceOfPublication

 ,B.Verified

 ,B.BookType

 ,P1.Surname + ' ' + P1.Initials

  +...

It could be a data type precedence problem.

eg If jrlid is a char it will automatically to promoted to a varchar and any indexes on jrlid will not be used....

Looking this up on th web the following function may be more accurate over short distances (read the contents of the link):

CREATE FUNCTION dbo.GreatCircleDistance2

(

 @Latitude1 float = NULL,

 @Longitude1 float = NULL,

 @Latitude2...

Just managed to have a quick look at this.

The problem seems to be in the function where rounding can cause the cosine to be greater than 1 when the co-ordinates...