SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pnpsql
Posting Yak Master

India
246 Posts

Posted - 06/11/2013 :  07:15:19  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 06/11/2013 :  07:26:31  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/11/2013 :  07:29:00  Show Profile  Reply with Quote
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
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 06/11/2013 :  07:31:19  Show Profile  Reply with Quote
if user select 4,5,6.... instead of 1,2,3 then i also react as null.

challenge everything
Go to Top of Page

pnpsql
Posting Yak Master

India
246 Posts

Posted - 06/11/2013 :  07:33:28  Show Profile  Reply with Quote
thanks Mr. mik , it works..

challenge everything
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 06/11/2013 :  07:39:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/11/2013 :  07:50:56  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000