Right, good point.  I meant to do that.  You just do an INNER JOIN to the table of allowed values, with a WHERE clause(s) if needed .

Quite right.  If you wanted to use a trigger, you'd have to put a trigger(s) on the table(s) used within the view, not on the view itself.

Sure.  Based on the originally posted query:

SELECT @currentValue, SomeField
FROM SomeTable
WHERE @currentValue = 'Test1'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AnotherTable
WHERE @currentValue = 'Test2'
UNION ALL
--
SELECT @currentValue, SomeField
FROM AThirdTable
WHERE @currentValue = 'Test3'
UNION...

Yes, I should have said "and without a loop".

For me, I'd use UNION ALL rather than a loop for something like this. SQL will have to parse all the SQL...

How would you pull data from at least 4 different tables efficiently without using a UNION ALL?

I doubt you need a temp table. You can just UNION ALL all the different query results. If a given query returns no rows, that's OK, it will...

Do you want to list inactive data in the report?

If not, you can just join to the other tables including "AccountStatus IN (1)" in the JOIN ON clause, something like...

Personally I'd use CROSS APPLY:

SELECT sl.Name, sl.Country, types.type

FROM Sales_Ledger sl

CROSS APPLY ( VALUES([Type 1]), ([Type 2]), ([Type 3]), ([Type 4]) ) AS types(type)

WHERE types.type > ''

Hopefully SQL's split algorithm isn't that poor for bit columns.

But, if it turns out to be, create an index on:

( MyTable_ID, is_MyTable_Text_NULL ).

Add a bit column to indicate null status of [MyTable_Text], then index on that column.

ALTER TABLE [dbo].[MyTable] ADD is_MyTable_Text_NULL AS CAST(CASE WHEN MyTable_Text IS NULL THEN 1 ELSE 0 END...

You don't want index_columns and indexes in the query as it exists, because it will just generate duplicate rows for no reason.

SELECT TOP (1)

st.name [Table Name],

c.name [Column Name],

t.name [Data Type]

FROM...

You need more clear examples.

For example, if line 1 was the starting line, which of these lines would be included:

0 200 0 0 501 --line 5

200 0 0 0 0...

That's a very interesting idea. It sounds very complex to implement, but quite nice once you get it working.

The trigger is actually rather easy to write and can be...

• This reply was modified 6 years, 9 months ago by ScottPletcher.
• This reply was modified 6 years, 9 months ago by ScottPletcher.

Bit data type is numeric in SQL: naturally 1 means Yes, 0 means No.

So:

CREATE TABLE dbo.table_name ( bit_column bit DEFAULT 1 );

But you also made the blanket statement that:

And...

• This reply was modified 6 years, 9 months ago by ScottPletcher.