An alternative would be to build all of the totals you want in a single query, and then to join all of the queries together on the Region_id field. I...

You should add a link between the main query and all of the correlated subqueries. Probably on the Region_ID field...

SELECT Region_id, Region_Description,

(SELECT Count(d.id) FROM ...

 WHERE...

Guilty as charged , didn't test that. Textdata is of course no character field.

You should use DATALENGTH in stead of LEN. Don't know if you have to...

Hi Yichang,

I've started to work with BizTalk only recently. First of all, I guess you should ask this question in the BizTalk groups (microsoft.public.biztalk).

Anyway, here is my opinion.

I do not...

Are you sure it is that line? I can imagine the solution erroring out when there is no space after the 'EXEC_' stuff.

In that case, CHARINDEX returns zero, so you...

SUBSTRING takes the length as a third parameter. CHARINDEX returns the index of the first occurence.

So you should account for the skipping the first 6 characters.

SUBSTRING(TextData,

...

What do you want to accomplish exactly? Maybe give an example.

Just to select the fields use

SELECT T1.<fields>, T2.<fields>

      FROM T1 INNER...

Have to agree to use Stored Procedures if possible.

In my opinion, Maintainability is not necessarily an advantage of Stored procedures. You might / will be distributing 'Business Logic' over two...

Seems to me like you're checking on different parameters that you pass into your procedure.

One way to speed things up is to write separate procedures for distinct combinations of parameters....

quote:

---

Take the following:

insert * from tablename

Is there a clause which enables me to insert all fields but one,rather than specifiying each...

In fact this is a presentation issue that you should tackle in your application, not at the DB level. If you output a number and you state at OS (Windows)...

The construct is OK. You just have to replace the = NULL by IS NULL.

The difference between the two is that = NULL checks if the value is NULL and...

Just did a test, and in my case, both queries return the same result.

Can you post DDL and data?

First, construct a view to substitute all NULL values in the fact table by a value. I am using '-1', since I guess this will not be present in the...

Better yet, check the Query plan.

I guess when you do a count(*) both results are the same?