case condition - 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

case condition

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-06-11 : 07:15:19

hi team please help me to get desired output.

here is my code where there i need to select records on the basis of some passd value
and when no value is pass it should return all value . i dont want dynamic sql.
i want to do this by conditionig query.

declare @vid numeric(10)
declare @vtbl table
(id numeric(10) ,
name varchar(10))

insert into @vtbl(id, name )
values(101, 'a')

insert into @vtbl(id, name )
values(201, 'b')

insert into @vtbl(id, name )
values(301, 'c')

set @vid = 2

select * from @vtbl
where id = case when @vid = 1 then 101
when @vid = 2 then 201
when @vid = 3 then 301
-- else when no values pass all items should be select
end

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 07:26:31

[code]
SELECT *
from @tbl
WHERE id = (@vid * 100) + 1
OR @vid IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 07:29:00

Not sure about the logic of passing @vid as 1,2,3 (why not pass as 101, 102, 103?) but check if the following is what you're looking for

select * from @vtbl
where id = case when @vid = 1 then 101
when @vid = 2 then 201
when @vid = 3 then 301
ELSE ID
end

Cheers
MIK

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-06-11 : 07:31:19

if user select 4,5,6.... instead of 1,2,3 then i also react as null.

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2013-06-11 : 07:33:28

thanks Mr. mik , it works..

challenge everything

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-11 : 07:39:12

So if a user selects 1,2,3,..., N (N could in a thousands figure too), you would need to explicitly handle all of them in the case statment which is not the good way to do it.. but if the IDs are in specific pattern with respect to the input value, then you can simplify it using the logic proposed by Visakh. But if there is no pattern then it would be better to have the input (@vid) as ID value and use following query

SELECT * FROM @vtbl
WHERE ID=ISNULL(@vid,ID)

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 07:50:56

EVen in your current way you could do this to handle any values passed to @vid


CREATE PROC Getdata
@vid int
AS

IF @vid > 3 THEN SET @vid = NULL

SELECT *
from @tbl
WHERE id = (@vid * 100) + 1
OR @vid IS NULL

now pass value as 1 ,2 3 etc and you'll get data for 101,201,301

pass a value > 3 and you'll get all values by treating the parameter as null


EXEC GetData 1
GO

EXEC GetData 2
GO

EXEC GetData 4
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources