Stored rocedure - check resultset before returning null

• methos-1016078

  Grasshopper

  Points: 17

  More actions

  February 13, 2009 at 7:42 am

  #130973

  Hi all,

  I have written a SP that selects records from a table (yup simple stuff).

  If the select statement returns no values, I want to try 2 other different variables inside of the SP to make sure that is values returned.

  e.g.:

  select users from tbl1 where name like 'james%'

  if no result then try this

  select users from tbl2 where name like 'james%'

  again if no result try this

  select users from tbl3 where name like 'james%'

  ok there are no results so return nothing

  Any suggestions?

  Methos

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  February 13, 2009 at 8:43 am

  #943681

  Two options. First, UNION all three statements together. Then you'll get all the James data.

  Option 2, check for @@ROWCOUNT to see if you have any data with an IF statement.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• gyessql

  Ten Centuries

  Points: 1097

  More actions

  February 13, 2009 at 9:19 am

  #943718

  Here's a way..

  create table names1(id int not null identity(1,1),empname varchar(10))

  create table names2(id int not null identity(1,1),empname varchar(10))

  create table names3(id int not null identity(1,1),empname varchar(10))

  --Insert values...

  select coalesce((select empname from names1 where empname like 'james%'),

  (select empname from names2 where empname like 'james%'),

  (select empname from names3 where empname like 'james%'),

  'James not there!')