Need help w/ this query

[katbear]

Hi,

Here's my problem. I need to write a query that checks to see if some values do NOT exist in a table (as compared to another table). If they don't exist, I want the query to select the non-existing values in a "array", so that I can email the list.

This is what I have so far, but it's wrong:

SELECT @Industry = Industry
FROM CR_TABLE
WHERE Industry NOT IN
(SELECT Industry FROM V_TABLE)

As you can see, the problem is that you can't put a list into the @Industry variable.

I am wondering the correct way to do this!

Thanks much

 

[monksnake]

Just put the results in a temp table and select all the rows for it, then copy and paste the results into an e-mail.

<.

 

[katbear]

I'm afraid that won't work as I won't be cutting and pasting.

What I need to do is send the output to an email body, that is the @body parameter of sp_dbsendmail (or whatever it's called).

I don't want:
cat
dog
emu

I want:
cat, dog, emu

Someone gave me this code a while back, and I thought it worked, but it doesn't :-(

declare @value1 varchar(8000)

SELECT ISNULL(@value1 + ',' + value1, value1) from test1

It's supposed to concatenate the cols together.

 

[katbear]

Oops, nevermind.

declare @value1 varchar(8000)

SELECT @value1 = ISNULL(@value1 + ',' + value1, value1) from test1

print @value1

That works. I think gmmastros is originator of this idea.

 

[ESquared]

just a tiny note, that a more clear (to me) way to get the same result is:

SELECT @value1 = ISNULL(@value1 + ',', '') + value1

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[katbear]

After staring at this for a long time:

SELECT @value1 = ISNULL(@value1 + ',' + value1, value1) from test1

This version is more clear to me

This ISNULL evaluates "@value1 + ',' + value1" - which on the first go around is NULL because @value1 has no value the first go around. So it assigns @value1 = value1 instead.

Then on the following rows, it starts building up the @value1 with the commas between.

That's how I look at it anyways!

 

[katbear]

Anyhow, it's a cool trick to create an "array" of values from a table on the fly.

And it works for sending output of a table to a single variable, which can then be used in the @body parameter of sp_db_sendmail, which was my original dilemma.

 

[ESquared]

This is the vanilla statement which breaks for one of two reasons. Either @value1 = '' to start with in which case an extra comma is on the front end, or @value1 is null to start with in which case the varaible never gets set to a value besides null.

SELECT @value1 = @value1 + ',' + value1

To me, adding IsNull() around the first part is simpler than repeating value1 again.

SELECT @value1 = [red]IsNull([/red]@value1 + ','[red], '')[/red] + value1

Another consideration about style here is that value1 can sometimes be a very long and complicated calculation. What if it was

value + value2 + CASE WHEN value3 = 'X' then ExcludedValue ELSE IncludedValue END

Then the other method will repeat this expression.

SELECT @value1 = IsNull(
      @value1 + ',' + value + value2 + CASE WHEN value3 = 'X' then ExcludedValue ELSE IncludedValue END,
      value + value2 + CASE WHEN value3 = 'X' then ExcludedValue ELSE IncludedValue END
   )

SELECT @value1 =
      IsNull(@value1 + ',', '')
      + value + value2 + CASE WHEN value3 = 'X' then ExcludedValue ELSE IncludedValue END

So as a matter of practice, repeating the second value ends up with potentially a great amount of confusion, so I make it a point to reduce the number of times that expressions are repeated in my code (to keep me to good habits) even if the expression is simple.

What if you need to change that expression... it would have to be done in two places using the first method. It is EASY to mess that up, I know it, I've done it.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]