where clause depend on variable

[Guest_imported]

hi guys
i have to created one conditional

i have to create where claused depend on variable
please help to do this.

if substring(jcode)='JOB'
set VAR1='JOB_CODE'
else
set VAR1='JOB_SALORDNO'
if exist(select * from jobmast where @var1='000152JOBA')
what wrong in it does not work

m.a.khaleel

 

[tlbroadbent]

MS SQL does not allow variables to used for database, table or column names. You can do the following.

Declare @rc int
Set @rc=0

if substring(jcode)='JOB'
if exist(select * from jobmast
where JOB_CODE='000152JOBA')
Set @rc=1
else
if exist(select * from jobmast
where JOB_SALORDNO='000152JOBA')
Set @rc=1

If @rc=1
Begin
<your code here>
End

You could also create and execute dynamic SQL statements.

declare @sql nvarchar(200), @rc int, @var1 nvarchar(12)
set @rc=0

if substring(jcode)='JOB'
set @var1='JOB_CODE'
else
set @var1='JOB_SALORDNO'

Set @sql=
'if exists (select * from jobmast where ' +
@var1 + '=''000152JOBA'') Set @rc=1'

exec sp_executesql @sql,N'@rc int Output',@rc=@rc output

If @rc=1
Begin
<your code here>
End

---------------------------------

I've seen a number of your posts as Visitor. I recommend that you join Tek-Tips. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

thanks for your help