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
 parameter logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  08:55:46  Show Profile  Reply with Quote
How to check against the logic with the input of parameter?

If @input = 'hello' exist in B.input, then output should be hello.
If @input = 'hello' not exist in B.input, then output should be noneHello.

can i use coalesce in where condition?

declare @input nvarchar(max)
set @input = 'hello'

select B.input, A.name
from tableA A
inner join tableB B
on A.ID=B.ID
where B.input=@input

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  09:29:54  Show Profile  Reply with Quote
i tried this, but no output appear.

declare @input nvarchar(max)
set @input = 'hello'

select B.input, A.name
,coalesce (B.input, 'noneHello')
from tableA A
inner join tableB B
on A.ID=B.ID
where B.input=@input
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/24/2013 :  12:02:28  Show Profile  Reply with Quote
do you've record with input value hello present

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  12:14:17  Show Profile  Reply with Quote
input = hello which not exist in tableB input, the result should be as below:

set @input='hello'

input name ID
noneHello alice 100
noneHello john 180
noneHello mary 280

Below input = paris exist in tableB input, result should be as below:

set @input='paris'

input name ID
paris alice 100
paris john 180
paris mary 280

where to apply the logic?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/24/2013 :  12:20:09  Show Profile  Reply with Quote
sounds like this is what you want

declare @input nvarchar(max)
set @input = 'hello'

select B.input, A.name
,coalesce (B.input, 'noneHello')
from tableA A
left join tableB B
on A.ID=B.ID
and B.input=@input


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  12:26:00  Show Profile  Reply with Quote
i tried but it return 0 record.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/24/2013 :  12:28:37  Show Profile  Reply with Quote
did you notice the left join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  12:33:11  Show Profile  Reply with Quote
when i try to run this:

declare @input nvarchar(max)
set @input = 'hello'

select B.input, A.name ,A.ID
,coalesce (B.input, 'noneHello')
from tableA A
left join tableB B
on A.ID=B.ID
and B.input=@input

it appear as below:

input name ID
hello alice 100
hello john 180
hello mary 280

but what if something like AAA not exist in B.input:

declare @input nvarchar(max)
set @input = 'AAA'

select B.input, A.name ,A.ID
,coalesce (B.input, 'noneHello')
from tableA A
left join tableB B
on A.ID=B.ID
and B.input=@input

it return nothing.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/24/2013 :  12:50:23  Show Profile  Reply with Quote
AAA doesnt exist in tableA also?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 03/24/2013 :  12:53:05  Show Profile  Reply with Quote
Yup it doesn't exist.

Some record exist in tableA, some are not.

The logic i wanted is to get those exist in tableA, must exist in tableB. If not exist in tableB then it should be noneHello.

Can i do that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/24/2013 :  13:02:59  Show Profile  Reply with Quote
thats exactly what i gave you
It will give data if present from B and in case nothing present it returns noneHello. In all cases, fields from tableA would be returned.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.09 seconds. Powered By: Snitz Forums 2000