have stored proc always return a blank record RSS

• • Reply
  

  MyronCope

  Participant

  1656 Points

  1345 Posts

  have stored proc always return a blank record

  Apr 05, 2012 08:26 PM|LINK

  Using sql server 2005

  I have a stored proc that is a query and when there is no data return I still want the query to return an empty record with NULLs. whats an easy way to do this?

  I've done this before using temp tables but wwondering about an easier way

  thanks

  MC

• • Reply
  

  Rab Nawaz Kh...

  Participant

  955 Points

  209 Posts

  Re: have stored proc always return a blank record

  Apr 05, 2012 08:39 PM|LINK

  you can use like this in your store procedure if its only use to get the data

  IF EXISTS (SELECT * FROM TableName WHERE COLUMN2 = @value1 AND COLUMN2 = @value2)
  BEGIN
  -- your query if the data exists in database
  END
  ELSE
  SELECT NULL AS NoValue

  Hope it will solve your issue

  Please mark as answer if this post answered or solved your problem.
• • Reply
  

  TabAlleman

  All-Star

  15571 Points

  2700 Posts

  Re: have stored proc always return a blank record

  Apr 05, 2012 08:41 PM|LINK

  IF EXISTS(SELECT * FROM MyTable WHERE Condition=true)
    SELECT * FROM MyTable WHERE Condition=true
  ELSE
    SELECT NULL AS Column1, NULL AS Column2, NULL AS Column3, [etc...]

  -Tab Alleman
  • Marked as answer by MyronCope on Apr 06, 2012 01:24 PM
• • Reply
  

  MyronCope

  Participant

  1656 Points

  1345 Posts

  Re: have stored proc always return a blank record

  Apr 06, 2012 01:38 PM|LINK

  TabAlleman

  IF EXISTS(SELECT * FROM MyTable WHERE Condition=true)
    SELECT * FROM MyTable WHERE Condition=true
  ELSE
    SELECT NULL AS Column1, NULL AS Column2, NULL AS Column3, [etc...]

  very nice, I like it.

  this works well assuming the initial exists query is fast, if its slow i guess you would have to weigh using a temp table vs using what you did above