is this possible to use select case

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

is this possible to use select case

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-08 : 22:48:17

hi, this is my procedure.

CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO

if i run this procedure i need the result as following

result result1 result2
found notfound found

is this posible to do using select case ? please help me to get my need please

davidmal
Starting Member

19 Posts

Posted - 2007-02-08 : 23:04:02

Can't really get a feel for what exactly the procedure is doing or the DB schema but the SQL equvalent to select case is:
CASE (field) WHEN (expression) THEN (returnvalue) [ELSE (returnvalue)] END

Here's a simple example:

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

Hope that helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 00:47:41

[code]
CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
declare
@result varchar(10),
@result1 varchar(10),
@result2 varchar(10)

IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select @result = 'found'
else
select @result = 'not found'
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select @result1 = 'found'
else
select @result1 = 'not found'
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select @result2 = 'found'
else
select @result2 = 'not found'

select @result as result, @result1 as result1, @result2 as result2
end
GO
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 01:21:27

[code]CREATE FUNCTION dbo.fnYardRetrieve
(
@Yard VARCHAR(50),
@Vessel VARCHAR(50),
@Customer VARCHAR(50)
)
RETURNS BIT
AS
BEGIN
DECLARE @Found BIT

IF EXISTS (
SELECT cd.Code_Description
FROM CodeMaster AS cd
INNER JOIN CategoryMaster AS ct ON ct.Category_Code = cd.Category_Code
WHERE CASE
WHEN cd.Code_Description = ISNULL(@Yard, cd.Code_Description) AND dt.Category_Description = 'Yard' THEN 1
WHEN cd.Code_Description = ISNULL(@Vessel, cd.Code_Description) AND ct.Category_Description = 'Vessel' THEN 1
WHEN cd.Code_Description = ISNULL(@Customer, cd.Code_Description) AND ct.Category_Description = 'Customer'THEN 1
ELSE 0
END = 1
AND cd.Active = '1'
)
SELECT @Found = 1

RETURN ISNULL(@Found, 0)
END[/code]Call with[code]SELECT CASE
WHEN dbo.fnYardRetrieve(@Yard, @Vessel, @Customer) = 1 THEN 'Found'
ELSE 'Not found'
END[/code]
Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-09 : 01:31:18

hi peso and khtan thank you for answering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources