parameter logic - 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

parameter logic

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-24 : 08:55:46

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

420 Posts

Posted - 2013-03-24 : 09:29:54

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-24 : 12:02:28

do you've record with input value hello present

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-24 : 12:14:17

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-24 : 12:20:09

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/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-24 : 12:26:00

i tried but it return 0 record.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-24 : 12:28:37

did you notice the left join?

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-24 : 12:33:11

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-24 : 12:50:23

AAA doesnt exist in tableA also?

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-24 : 12:53:05

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-24 : 13:02:59

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources