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.
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 = 2select * 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 endchallenge everything |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-11 : 07:26:31
|
[code]SELECT *from @tblWHERE id = (@vid * 100) + 1OR @vid IS NULL[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 301ELSE ID endCheersMIK |
|
|
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 querySELECT * FROM @vtbl WHERE ID=ISNULL(@vid,ID)CheersMIK |
|
|
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 @vidCREATE PROC Getdata@vid intASIF @vid > 3 THEN SET @vid = NULLSELECT *from @tblWHERE id = (@vid * 100) + 1OR @vid IS NULL now pass value as 1 ,2 3 etc and you'll get data for 101,201,301pass a value > 3 and you'll get all values by treating the parameter as nullEXEC GetData 1GOEXEC GetData 2GOEXEC GetData 4GO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|