Add the INSERT INTO @cte which I forgot.

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION dbo.GetLocationDescription

(

@location_id int

)

RETURNS varchar(8000)

AS

BEGIN

&nbsp&nbsp&nbsp&nbspDECLARE @result varchar(8000)

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,@Level int

&nbsp&nbsp&nbsp&nbspSELECT @result = ''

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,@Level = 1

&nbsp&nbsp&nbsp&nbspDECLARE @cte TABLE

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspHLevel int NOT NULL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,Parent int NOT NULL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,[Description] varchar(255) NOT...

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION dbo.GetLocationDescription

(

&nbsp&nbsp&nbsp&nbsp@location_id int

)

RETURNS varchar(8000)

AS

BEGIN

&nbsp&nbsp&nbsp&nbspDECLARE @result varchar(8000)

&nbsp&nbsp&nbsp&nbspSET @result = ''

&nbsp&nbsp WITH cte (HLevel, Parent, [Description])

&nbsp&nbsp&nbsp&nbspAS

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT 1 AS HLevel, T1.Parent, T1.[Description]

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM dbo.Location T1

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspWHERE T1.location_id = @location_id

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspUNION ALL

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT C2.HLevel +...

This should get you started:

-- *** Test Data ***

CREATE TABLE #t

(

&nbsp&nbsp&nbsp&nbspInvNO int NOT NULL

&nbsp&nbsp&nbsp&nbsp,AccNo int NOT NULL

&nbsp&nbsp&nbsp&nbsp,TR_Age int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Pay_Age int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Amt int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Pay_Amt int NOT NULL

&nbsp&nbsp&nbsp&nbsp,Run_Bal int...

It depends on the order of the tables. I prefer to think of the * being on the side where all the rows are returned.

--eg

SELECT *

FROM TableA A

&nbsp&nbsp&nbsp&nbspLEFT JOIN TableB...

The 'N' just means a nchar constant as opposed to a char constant.

You will need to cast to a float first.

SELECT CAST(CAST(N'2.00701E+16' AS float) AS bigint)

Your outer joins are effectively being converted to inner joins by the subsequent inner joins.

The joins should be nested so that the FROM clause looks something like:

FROM

&nbsp&nbsp&nbsp&nbspcopay...

DELETE D

FROM

(

&nbsp&nbsp&nbsp&nbspSELECT

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspROW_NUMBER() OVER (PARTITION BY RefNoColumn, RowNoColumn ORDER BY RefNoColumn) AS RowID

&nbsp&nbsp&nbsp&nbspFROM YourTable

) D

WHERE RowID > 1

I use the following code to do some rough validation when importing email addresses.

SELECT EMail

FROM

(

&nbsp&nbsp&nbsp&nbspSELECT

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspEMail

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('.', REVERSE(EMail)) AS DotPos

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', REVERSE(EMail)) AS AtPos

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,CHARINDEX('@', EMail) AS AtPosStart

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(LEFT(REVERSE(EMail), CHARINDEX('@', REVERSE(EMail)) - 1)) AS DomainName

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,REVERSE(SUBSTRING(REVERSE(EMail),...

There is no need for a cursor, try something like:

-- *** Test Data ***

DECLARE @t TABLE

(

&nbsp&nbsp&nbsp&nbspiTableFieldId int NOT NULL

&nbsp&nbsp&nbsp&nbsp,iMenuId int NULL

&nbsp&nbsp&nbsp&nbsp,vFieldsName varchar(100) NULL

&nbsp&nbsp&nbsp&nbsp,iDataTypeName varchar(50) NULL

&nbsp&nbsp&nbsp&nbsp,iFieldLength int NULL

&nbsp&nbsp&nbsp&nbsp,bIsPrimary bit NULL

&nbsp&nbsp&nbsp&nbsp,bIsIdentity bit...

Rajan John (8/27/2008)

---

For such scenarios, I also do the updates with a join only. I would be interested to see whether this approach has any performance implications, and what are...

You could try a stored proc with two OUTPUT parameters.

I suspect the NOT EXISTS sub-query uses a view which contains at least one JOIN. This is not a good idea as JOINs in sub-queries tend to use NESTED LOOPS...

I think Jack meant something like:

UPDATE T1

SET Col1 = T2.Col1

&nbsp&nbsp&nbsp&nbsp,Col2 = T2.Col2

FROM Tab1 T1

&nbsp&nbsp&nbsp&nbspJOIN Tab2 T2

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspON T1.Col3 = T2.Col3

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspAND T1.Col4 = T2.Col4

&nbsp&nbsp&nbsp&nbspJOIN

&nbsp&nbsp&nbsp&nbsp(

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspSELECT C.Col3, C.Col4, MAX(C.Col1) AS Col1

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspFROM Tab2 C

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspGROUP BY...