How to add code in this store procedure to void empty data?

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  September 13, 2012 at 7:25 am

  #281297

  I use one store procedure to select data as below code:

  select * from ORDER where city='London'

  In asp.net app, one dataset will be created based on this store procedure.

  How to add code in this store procedure to void creating empty dataset?

  (I knew how to code in app to check dataset count but do not know how to in store procedure)

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 13, 2012 at 7:30 am

  #1537187

  adonetok (9/13/2012)

  ---

  I use one store procedure to select data as below code:

  select * from ORDER where city='London'

  In asp.net app, one dataset will be created based on this store procedure.

  How to add code in this store procedure to void creating empty dataset?

  (I knew how to code in app to check dataset count but do not know how to in store procedure)

  There is no point to do so, as your app should handle cases for empty datasets, but it is possible:

  add this before your select:

  IF NOT EXISTS (select 1 from ORDER where city='London') RETURN;

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  September 13, 2012 at 10:28 am

  #1537332

  I might add to that not to use select * and if at all possible change the name of your table to not use a reserved word.

  select * from [order] order by [order]

  Talk about confusing.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/