title: Return the result of a SP while inside of a select statement in SQL Server
body: <p>I am wondering how to return the result of a SP inside of a select statement? I have multiple reports that need to use the same code I wrote in the SP where the contents of the SP may change 2-3 times a year.</p>
  
  <p>The select statement would go something like this:</p>
  
  <pre><code>select Product, Cost, [Output from SP where input is Product Code] from table1
  </code></pre>
  
  <p>My stored procedure is meant to accept a 'Product Code' and then return a bucket type:</p>
  
  <pre><code>ALTER PROCEDURE Product_Bucket_Type (@productcode nvarchar(4)= NULL, @bucket nvarchar(30) OUTPUT)
  
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
  Select
      @bucket = Case
      WHEN @productcode IN ('20','22', '26','27','28','2A','2B') THEN 'CA'
      WHEN @productcode IN ('30','32','33','34','35','36','37','38','3A','3B','3C','3R') THEN 'NU' 
      WHEN @productcode IN ('31','43') THEN 'Regulators' 
      WHEN @productcode IN ('40','41','43A','43B','47','48','4A','4B','4F')THEN 'WH'
      WHEN @productcode IN ('50','57','58','5A','5B')THEN 'SN' 
      WHEN @productcode IN ('90','97','98','99','9A','9B','9F') THEN 'QC'
      WHEN @productcode IN ('2J','B01','B02','B03','B04','B05','B06','B07','B08','B09','B10','B11','B12','B13','B14','B15','B16','B17','B23','B24','B25','B28','B29','B30') THEN 'BI' 
      WHEN @productcode IN ('2R','2S','2W','B00','2SA','2SB','2SC','2SD','2SE','2SF','2SG','2SH','2SI','2SJ','2SK','2SL','2SM','2SN','2SO','2SP','2SQ','2SR','2SS','2ST','2SU','2SV','2H','2HA','2T','2TA','2TB','24C','2M') THEN 'TU' 
      WHEN @productcode IN ('21','23','2G','2F','21A') THEN 'GA' 
      WHEN @productcode IN ('2XA','2XB','2XC','2XD','2XE','2XF','2XG','2XH','2XI','2XJ','2XK','2XL','2XM','2XN','2XO','2XP','2XQ','Y0') THEN 'OU' 
      WHEN @productcode IN ('Y7') THEN 'NC'       
      WHEN @productcode IN ('Y8') THEN 'CH' 
      WHEN @productcode IN ('Y9') THEN 'CU' 
      WHEN @productcode IN ('Y1') THEN 'OU'    
      WHEN @productcode IN ('4P') THEN 'PR'
      ELSE 'OTHER'
      END
      if @productcode &lt; '20' SET @bucket = 'SW'
  END
  GO
  </code></pre>
  
  <p>I am new to writing stored procedures so I am assuming my syntax for inputs and outputs is correct in the SP.</p>