Conditional Views

I have a view which has to be versatile enough to perform different calculations. For instance, using the view below I may want the stock level for that particular wine or in another instance I would the need the stock level for all wines that share the same type. Note that I would never need a combonation of both in the same view.

Is there anyway to specify <myVar> before or when calling this view?

CREATE VIEW vwWines
AS

SELECT
tblWine.ID,
tblWine.WineTypeID,
StockQty =
CASE
WHEN <myVar> = 1
THEN tblWine.StockQty
ELSE
(
SELECT
SUM(StockQty)
FROM
tblWine AS tblWineTwo
WHERE
tblWine.WineTypeID = tblWineTwo.WineTypeID
)
END
FROM
tblWine
Note: This view has been simplified and been done on the fly so may contain errorsdo this as a stored procedure and pass the variable in from the application.|||Why? Why not have two views and let the front end decide which one to call, based on the need of the operator? Follow the KISS principle.|||Keep It Simple Stupid? It took me a minute to remember that one.

I do not know. Maybe having one peice of code to maintain instead of 2. It does not really matter. Either way is valid.|||Exactly, I had thought of that but there is other C# code that are executed on the results and to duplicate it would cause more work in the long run.

I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view. It automatically produces the stored procedures and connection code. I wouldn't want to do it this way as I'd have to provide other means of connecting to the database which again isn't good for maintainence.

I'm going to experiment a little more but if I can't crack it, I'm going to go with the method of creating two view just to keep the code cleaner.|||I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view.Dear Lord...please tell me you aren't using NHibernate.
Tools such as this are a bad idea. They inevitably lead to ineffecient code, unscalable applications, and insecure databases.|||Lol, I'm using something called MyGeneration dOOdads (http://www.mygenerationsoftware.com/portal/dOOdads/Overview/tabid/63/Default.aspx) and it seems to have worked find over the past two years over various projects. Obviously its not flexible enough to cover problems like these but for what it provides, it's definitely worth the trade off. It's the #1 downloaded .NET tool on Download.com, apparently.|||"An Amazing 48k Architecture that Supports the 1.1 and 2.0 .NET Framework
Transactions, Dynamic Queries, and a Highly Intuitive API"
Dynamic Queries are to be AVOIDED. Tools such as this violate the most basic principles of database application design. They do so in the name of short-term development gains, and at the expense of long-term quality.|||But surely you would require the use of dynamic queries even without the help of an ORM tool, for example, an advanced search form. Or am I thinking of the wrong sort of dynamic queries?|||No, you would not need dynamic queries for an automated search form. And if dynamic sql is required it should be constructed with a stored procedure, not by an interface or middle-tier, which should not even have access to the underlying tables.

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP
--

Conditional UNION!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam."Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
>
Would this work?
Select *
FROM myTable
WHERE myColumn = 'a'
UNION
SELECT *
FROM myTable
WHERE myColumn = 'b'
and exists(
Select *
FROM myTable
WHERE myColumn = 'a')
Regards,
John|||Adam
Is there any reason to use UNION instead of UNION ALL? Do you want to
eliminate duplications?
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I have a query that if it returns data i want to perform a union on it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
>
>|||Yes!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edBylb%23wFHA.3000@.TK2MSFTNGP12.phx.gbl...
> Adam
> Is there any reason to use UNION instead of UNION ALL? Do you want to
> eliminate duplications?
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:OKNz$V9wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||Try:
SELECT DISTINCT *
FROM MyTable
WHERE mycolumn IN ('A','B')
AND EXISTS
(SELECT *
FROM MyTable
WHERE mycolumn = 'A') ;
ORDER BY NEWID() fails under UNION or DISTINCT unless you also add NEWID()
to the SELECT list (in which case duplicates would not be eliminated).
Apparently your table doesn't have a key. I suggest you fix that problem
first but I don't see how this query helps you do that.
If the above doesn't help, please post DDL, sample data and required results
as suggested here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible?
A basic example would be great!!
Cheers,
Adam.
Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible?
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
David Portas
SQL Server MVP

Conditional Union!

Hi all,
I have a query that if it returns data i want to perform a union on it.
IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
If (? Above query returns rows)
UNION
SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
Is this kind of thing possible'
A basic example would be great!!
Cheers,
Adam.Adam Knight wrote:
> Hi all,
> I have a query that if it returns data i want to perform a union on
> it.
> IE: Select * FROM myTable WHERE myColumn = 'a' ORDER BY NEWID()
> If (? Above query returns rows)
> UNION
> SELECT * FROM myTable 2 WHERE myColumn = 'b' ORDER BY NEWID
> Is this kind of thing possible'
> A basic example would be great!!
> Cheers,
> Adam.
IF EXISTS (Select * FROM myTable WHERE myColumn = 'a')
Select NEWID(), <explicitly specify columns> FROM myTable WHERE
myColumn = 'a'
UNION ALL -- Use a UNION ALL in most cases
SELECT NEWID(), <explicitly specify columns> FROM myTable 2 WHERE
myColumn = 'b'
ORDER BY 1
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Answered in microsoft.public.sqlserver.programming.
Help others to help you. Please do not multi-post!
--
David Portas
SQL Server MVP
--

Concurrency

Do single commands (or stored procedures) execute concurrently, or they are executed one by one. How do you perform a lock during the execution of a command (or stored procedure).

Single commands are 'implied' transactions, that is the command executes completely, or nothing is done.

For multiple commands, where there is a need to make sure the several statements execute in a 'all or nothing' scenario, use TRANSACTIONS.

Refer to Books Online, Topic: Transactions

|||

Other good topics in the BOL are "Isolation mode" and locking hints.

Jens K. Suessmeyer.

http://www.sqlserver2005.de