CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-function
Rhushikesh
Rhushikesh

Reputation: 3700

How to call the stored procedure from select statment

I have one product table and one variant table as fallow

Product Table:  

    productId product_Name Product_Price Product_Brand 
    ==================================================
    1         Product      200           Brand

Variant Table:

    variantID ProductId Variant_Name
    ================================
    1         1          Black
    2         1          Blue
    3         2          white

i have to list the product in result with the variant but each product have the 4-5 variant to if i make view then it gives the lot of repeated data due to multiple variant.

i want variant string as 

Black,Blue,white

for that i write the one store procedure as fallow

create PROCEDURE [dbo].[get_Variant_String]
(@product Id int, @Char char)
AS
BEGIN
CREATE TABLE #tmp                         
(  
   varient varchar(100)
)
inset into #tmp
select  Variant_Name form  Variant where productID=@ProductId

DECLARE @VarientString varchar(100)
set @VarientString=''
DECLARE @Temp_Varient varchar(100) 
DECLARE Cur_Variant CURSOR for select varient  from #tmp
OPEN Cur_Variant 
WHILE 1 =1
BEGIN
  FETCH NEXT FROM Cur_Variant
  INTO  @Temp_Varient
  IF @@FETCH_STATUS <> 0
  BREAK
    IF @VarientString=''
    BEGIN
      SET @VarientString=@Temp_Varient
    END
    ELSE 
    BEGIN
      SET @VarientString=@VarientString + @Char +@Temp_Varient
    END
  END
CLOSE Cur_Variant
DEALLOCATE Cur_Variant
SELECT @VarientString
END

which gives me output as required

now i have a store procedure to select the product as to select the products. In which i have write the select query to select the product from product table. In this query i want to call above store procedure in select statement for product variant and reduce the no or iteration I have write the select query as fallow:

select productId,product_Name,Product_Price,Product_Brand,(exec get_Variant_String product.productid,',' ) as variant
from product

but it giving error

please suggest the me the way to get the out put as (for every product)

productId product_Name Product_Price Product_Brand Variant
==================================================================
1         Product      200           Brand        Black,Blue,white

I also tried to write the function but i have select statement so it giving me error.

I have got the one option that

Create temp table with the column in product table and variant column. In that table first insert the details form the product table then create the corsor and get the variant for each product using store procedure and select the product from this temp table but i have hundreds of product so in iterate hundreds of time i have to avoid this.please suggest me how to call the above store procedure in select statement on other best way to do this.

Upvotes: 0

Views: 159

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

It seems so much easier to do this rather than writing a stored procedure:

select p.*,
       stuff((select ',' + variant_name
              from variant v
              where v.productid = p.productid
              for xml path ('')
             ), 1, 1, ''
            ) as variants
from product p;

Upvotes: 1

Related Questions