SQL Query to make Stored Procedure for Making Hash Table and to concatenate Fields

// SQL Query to make Stored Procedure for Making Hash Table and to concatenate Fields 

Create PROCEDURE [dbo].[SP_Report_Category] 

 (

  @SupID int=null,

  @Campusid int=null,

  @Status varchar(150)

)

as

     

if(@Status='Category')

BEGIN

create table #temptablecategory ( CatId int,cname varchar(max))

declare @cname varchar(max)

declare @tempcname_1 varchar(200)

declare @tempcname_2 varchar(200)

declare @tempcname_3 varchar(200)

declare @tempcname_4 varchar(200)

declare @tempcname_5 varchar(200)

set @cname=''

insert into #temptablecategory(CatId)  -- inserting into the temperory table

select supplierid from Supplierregistration where supplierid=@SupID

set @cname=''

set @tempcname_1=''

set @tempcname_2=''

set @tempcname_3=''

set @tempcname_4=''

set @tempcname_5=''

SELECT     

@tempcname_1= case when manufacturer=1 then 'Manufacturer' else '' end

,

@tempcname_2= case when distributer=1 then ',Distributer' else '' end

,

@tempcname_3= case when retail=1 then ',Retail Supplier' else '' end

,

@tempcname_4= case when Service=1 then ',Service' else '' end

,

@tempcname_5= case when Partner=1 then ',Business Partner' else '' end

FROM       

Supplierregistration

WHERE    

(deleted = 0) AND (campusid = @Campusid)and supplierid=@SupID

set @cname = @tempcname_1+@tempcname_2+@tempcname_3+@tempcname_4+@tempcname_5

update #temptablecategory set cname=@cname where CatId=@SupID

SELECT    

SupplierId, cname as categorynames

FROM        

Supplierregistration

inner join

#temptablecategory on Supplierregistration.supplierid=#temptablecategory.CatId

WHERE    

(deleted = 0) AND (campusid = @Campusid)and supplierid=@SupID

drop table #temptablecategory

END