CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Hexxxer
Hexxxer

Reputation: 67

Return the result of a SP while inside of a select statement in SQL Server

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.

The select statement would go something like this:

select Product, Cost, [Output from SP where input is Product Code] from table1

My stored procedure is meant to accept a 'Product Code' and then return a bucket type:

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 < '20' SET @bucket = 'SW'
END
GO

I am new to writing stored procedures so I am assuming my syntax for inputs and outputs is correct in the SP.

Upvotes: 0

Views: 207

Answers (1)

ExternalUse
ExternalUse

Reputation: 2075

I shall form my brief comment into a proper answer for you. To provide an immediate solution, you can change your stored procedure to a scalar-valued function instead:

CREATE FUNCTION [dbo].[Product_Bucket_Type] (
    @productcode nvarchar(4)= NULL
) RETURNS nvarchar(30)
AS BEGIN   
    DECLARE @bucket nvarchar(30)
    Select
         @bucket = Case
         WHEN @productcode IN ('20','22', '26','27','28','2A','2B') THEN 'CA'
         ...
    RETURN @bucket
END

Now you can run your query as SELECT Product, Cost, dbo.Product_Bucket_Type(Product) AS whatever FROM ...

Better yet, you follow Beth's and Gordon's advice, and separate your codes out into 1 or 2 tables that you join your query on. Without proper knowledge of what your tables look like presently that is a bit hard to say - as a wild guess I'd put this (pseudo-structure) together:

Table ProductCodes (ID, ProductCode)
1 '20'
2 '2XA'
3 '2XB'
and so on

Table BucketTypes (id, BucketType)
1 'CA'
2 'OU'

... (depending on 1:n, n:1 or n:m connections, which is hard to see, you may want something like this for n:m's

TABLE ProductCodes_Link_BucketType (ProductCodes_ID, BucketTypes_ID)
1 1
2 2
3 2

Not sure what may fit your requirements, so I shall leave it at that. Hope it helps.

Upvotes: 1

Related Questions