Returning result set via Stored Procedure

• Digs

  SSCertifiable

  Points: 7333

  More actions

  July 6, 2005 at 3:24 pm

  #165249

  Why does this code not return a result set ???

  USE Pubs

  GO

  IF EXISTS (SELECT name

      FROM   sysobjects

      WHERE  name = N'procReturnResultSetTest'

      AND    type = 'P')

      DROP PROCEDURE dbo.procReturnResultSetTest

  GO

  CREATE PROCEDURE dbo.procReturnResultSetTest

  AS

  DECLARE @vchStr VARCHAR(500)

  SET NOCOUNT ON

  /*

  Description: Show a list of names in authors table as a comma string

  Errors: None

  */

  -- Build  String

  SET @vchStr = '%'

  SELECT  @vchStr = CASE

  WHEN LEN(@vchStr) > 0 AND @vchStr <> '%' THEN @vchStr+','+ UPPER(au_lname)

  ELSE UPPER(au_lname)

  END

  FROM dbo.authors

  GROUP BY au_lname

  GO

  ***************

  But when I add these lines before GO it does

  -- Send Result

  SELECT  @vchStr AS CurrentUsers

  I thought the SELECT in first code group would be enough ?

  Also should there be any checking for @@Errors in a SELECT  or SET (of variables) when getting data for a result for a report or data to client via ADO. ??

  Please advise, thanks in advance !

   

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  July 6, 2005 at 3:45 pm

  #571594

  When you do

  select column_name

  it returns the data to you.

   

  When you do

  select @variable = column_name

  it puts the data from the column into the variable, but it doesn't return data to a result set for display.  As you have found, if you want to then see the variable contents you can

  select @variable

  which will return the contents of the variable.

   

  As far as the error checking on 'select', I might check the variables that got set as a result of the select or check @@rowcount to see if any data was returned.

   

  ---

  And then again, I might be wrong ...
  David Webb