Michael Valentine Jones (11/30/2011)

---

Sean Lange (11/30/2011)

---

Jeff Moden (11/30/2011)

---

Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference...

Dev (11/30/2011)

---

Lowell (11/30/2011)

---

with Org(Business_Line)

AS

( SELECT 'Blue' UNION ALL SELECT 'Orange' UNION ALL SELECT 'Others' UNION ALL

SELECT NULL UNION ALL SELECT 'Yellow' UNION ALL SELECT 'Green' UNION ALL

SELECT 'Red'...

Jeff Moden (11/30/2011)

---

Actually, there's a great way to track it without a trigger if you can add a column as Kingston suggested. The difference is that you would use...

You should change your datatype in your table to varbinary(max). The image datatype is deprecated. Then you just use varbinary(max) as your parameter.

Lowell, Gus and I all gave you an example of how to do that.

order by case when...

Could you build it with three datasets? As long as you have something that will keep the order consistent that might be easier than trying to do that all in...

michael.french 172 (11/29/2011)

---

I did make a mistake in the order of the FK from Computer to Room. It should read like this:

ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName, FloorName, RoomName)...

I agree that at this point just creating a table is a better approach but I just had to try such a thing with dynamic sql.

create procedure [fLeft_Pad] (@vPadChar char(1),...

cafescott (11/29/2011)

---

Welsh Corgi, thanks for the suggestion. I am reluctant to change to the insert-into method because I think it will take longer overall.

Sean, thanks for the reply. ...

Option 2 is by far a better model than option 1 but it still is not quite right. You still have redundant data.

Building in the base. Everything comes from there....

You are getting varchar(255) because that IS the datatype returned from your function. You could not use the function and do something like this.

select

right(replicate('0', 9) + '1234', 9) as [Zip...

Lowell, I am thinking it might be a report so some string concatenation and your previous example might be sufficient. Just cast each column to a fixed length varchar to...

Is this what your table and data look like?

create table #GAP

(

G varchar(25),

A numeric(9,2) null,

P int null

)

Insert #GAP

select 'HWH 15',17.25, 115

union all select 'SP1 15',NULL, NULL

union all select 'SP2 25',NULL,...

GSquared (11/29/2011)

---

Having "Others" as the column value is a matter of a Case or IsNull/Coalesce statement in the Select clause.

In the Order By, you can put a Case statement like:

Order...

From your original thread...http://www.sqlservercentral.com/Forums/Topic1212595-392-1.aspx

OK,

There's a problem... This function will only replace the very first instance of the pattern you are searching for:

SELECT STUFF(@as_firmSelection,PATINDEX('%,%',@as_firmSelection), CHAR_LENGTH(@as_firmSelection), ''',''')

Run the above and you'll see...