Querry with multiple choices - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querry with multiple choices

Author

Topic

henryvuong
Starting Member

8 Posts

Posted - 2008-09-26 : 18:21:46

I have a table with the following data:

ID Item Price
1 World History $15.50
2 Learning Tennis $9.99
3 1000 Recipes $18.00
4 Harry Potter $25.00
5 C# Programing $15.50

I want to write a SQL querry which allows the user to have multiple choices as follow:

Choice one: return all items
Choice two: return item(s) where price = $15.50
Choice three: return item(s) where price > $20.00
Choice four: return item(s) where price < $20.00

I know I can writh multiple querries for the choices, but is it possible to put it all in one querry?

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-27 : 01:31:13

quote:
Originally posted by henryvuong

I have a table with the following data:

ID Item Price
1 World History $15.50
2 Learning Tennis $9.99
3 1000 Recipes $18.00
4 Harry Potter $25.00
5 C# Programing $15.50

I want to write a SQL querry which allows the user to have multiple choices as follow:

Choice one: return all items
Choice two: return item(s) where price = $15.50
Choice three: return item(s) where price > $20.00
Choice four: return item(s) where price < $20.00

I know I can writh multiple querries for the choices, but is it possible to put it all in one querry?

DECLARE @t TABLE ( Id INT, BookName VARCHAR(30), Price MONEY)
INSERT INTO @t
SELECT 1, 'World History', '$15.50' UNION ALL
SELECT 2, 'Learning Tennis', '$9.99' UNION ALL
SELECT 3, '1000 Recipes', '$18.00' UNION ALL
SELECT 4, 'Harry Potter', '$25.00' UNION ALL
SELECT 5, 'C# Programing', '$15.50'

DECLARE @a CHAR(1)
SELECT @a = 'l'

SELECT id, bookname,'$'+CAST(price AS VARCHAR(20)) AS Price FROM @t WHERE @a = 'a'
UNION ALL
SELECT id, bookname,'$'+CAST(price AS VARCHAR(20)) AS Price FROM @t WHERE price = '$15.50' and @a = 'E'
UNION ALL
SELECT id, bookname,'$'+CAST(price AS VARCHAR(20)) AS Price FROM @t WHERE price > '$20.00' and @a = 'G'
UNION ALL
SELECT id, bookname,'$'+CAST(price AS VARCHAR(20)) AS Price FROM @t WHERE price < '$20.00' and @a = 'L'

If u want return
1.All items Then pass 'A' for @a
2.Equals to '$15.50' then pass 'E' for @a
3.Greater than '$20.00' then pass 'G' for @a
4.Less than '$20.00' then Pass 'L' for @a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 01:40:59

quote:
Originally posted by henryvuong

I have a table with the following data:

ID Item Price
1 World History $15.50
2 Learning Tennis $9.99
3 1000 Recipes $18.00
4 Harry Potter $25.00
5 C# Programing $15.50

I want to write a SQL querry which allows the user to have multiple choices as follow:

Choice one: return all items
Choice two: return item(s) where price = $15.50
Choice three: return item(s) where price > $20.00
Choice four: return item(s) where price < $20.00

I know I can writh multiple querries for the choices, but is it possible to put it all in one querry?

the best method for this is to create a stored procedure and then call it with whatever values you want to return the specific data

like

CREATE PROC GetItems
@Price money=NULL,
@Equality char(2)='='
AS
SELECT *
FROM YourTable
WHERE (Price=@Price AND @Equality='=')
OR (Price >@Price AND @Equality='>')
OR (Price <@Price AND @Equality='<')
OR @Price IS NULL
GO

then use it as follows for your scenarios

1.EXEC GetItems
2.EXEC GetItems 15.50,'='
3.EXEC GetItems 20.00,'>'
4.EXEC GetItems 20.00,'<'

khasim76
Starting Member

35 Posts

Posted - 2008-09-29 : 01:44:36

how to create a permanent table for this @t table please let me know

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 01:59:15

quote:
Originally posted by khasim76

how to create a permanent table for this @t table please let me know

use CREATE TABLE statement. look into books online for syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources