Two Proc in Single Proc?

I am newbie in SQL Server 2008.There are three tables which I am using. I have created two Proc for my result.But I want that these two Proc combined in single Proc. Below is my proc -

First Proc -

CREATE PROC [GetPaymentGateway]  
 @CompanyID VARCHAR(3),  
 @CCMSalesChannel VARCHAR(50)  
AS  

declare @mainquery varchar(max)  
set @mainquery='SELECT credit_card_master.card_name, credit_card_master.card_type,'+ @CCMSalesChannel +' FROM credit_card_master 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = credit_card_master.payment_gateway_code  
WHERE [company_id] = '''+@CompanyID+''''  
exec (@mainquery)

Second Proc -

CREATE PROC [GetPaymentGateway2]  
 @CompanyID VARCHAR(3),  
 @NBSalesChannel VARCHAR(50)  
AS  

declare @mainquery varchar(max)  
set @mainquery='SELECT PG_NetBanking_Charges.Online_DC_Charge_Amt, PaymentGateway_master.Payment_Gateway_Name,'+ @NBSalesChannel +' FROM PG_NetBanking_Charges 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = PG_NetBanking_Charges.payment_gateway_code  
WHERE [company_id] = '''+@CompanyID+''''  
exec (@mainquery)

Please suggest me.Is it possibl开发者_高级运维e with hash table or etc.

Thanks in advance.

EDIT - I wanted these two results in single result(like in single table)

EDIT - There are five columns in my table but which one I will select, it will get to know on run time based on param.

---

You should avoid doing that kind of SQL inside stored procs unless its absolutely necessary and you are 1000% sure that you are sanitizing your input properly. The fact that all input params are varchar and these seem to be stored procs used to retrieve payment information, is disturbing, to say the least. You are still vulnerable to SQL Injection attacks by doing that kind of dynamic sql inside procs.

Both procs can be rewritten as follows in one single proc:

CREATE PROC [GetPaymentGateway]  
 @CompanyID VARCHAR(3),  
 @NBSalesChannel VARCHAR(50)  =null,
 @CCMSalesChannel VARCHAR(50)  =null
AS  
BEGIN
SELECT [card_name], [card_type],@CCMSalesChannel as CCMSalesChannel FROM credit_card_master 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = credit_card_master.payment_gateway_code  
WHERE [company_id] = @CompanyID

SELECT PG_NetBanking_Charges.Online_DC_Charge_Amt, PaymentGateway_master.Payment_Gateway_Name, @NBSalesChannel as NBSalesChannel  FROM PG_NetBanking_Charges 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = PG_NetBanking_Charges.payment_gateway_code  
WHERE [company_id] = @CompanyID

END

or perhaps you want this:

CREATE PROC [GetPaymentGateway]  
 @CompanyID VARCHAR(3),  
 @NBSalesChannel VARCHAR(50)  =null,
 @CCMSalesChannel VARCHAR(50)  =null
AS  
BEGIN
if (@CCMSalesChannel is not null)
begin
SELECT [card_name], [card_type],@CCMSalesChannel as CCMSalesChannel FROM credit_card_master 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = credit_card_master.payment_gateway_code  
WHERE [company_id] = @CompanyID
end

else if (@NBSalesChannel is not null)
begin
SELECT PG_NetBanking_Charges.Online_DC_Charge_Amt, PaymentGateway_master.Payment_Gateway_Name, @NBSalesChannel as NBSalesChannel  FROM PG_NetBanking_Charges 
INNER JOIN PaymentGateway_master
ON PaymentGateway_master.payment_gateway_code = PG_NetBanking_Charges.payment_gateway_code  
WHERE [company_id] = @CompanyID
end

END

Update In general, one can select a particular column based on a parameter at "run-time", as follows:

SELECT PG_NetBanking_Charges.Online_DC_Charge_Amt, PaymentGateway_master.Payment_Gateway_Name,  
 case @NBSalesChannel when 'value1' then ColumnA 
 when 'value2' then ColumnB
 when 'anothervalue' then ColumnC
 when 'yet_another_value' then ColumnD else 
 ColumnE end as SalesChannel  
 FROM PG_NetBanking_Charges 
    INNER JOIN PaymentGateway_master
    ON PaymentGateway_master.payment_gateway_code = PG_NetBanking_Charges.payment_gateway_code  
    WHERE [company_id] = @CompanyID

---

Whoever showed you this kind of template for stored procedures, did it wrong. Dynamic query execution can be usefull in few scenarios, but in your case are just overkill, and performance and security hazards. The procedures should be:

CREATE PROC [GetPaymentGateway]  
  @CompanyID VARCHAR(3)
AS  
BEGIN
  SELECT ccm.card_name, ccm.card_type,
    -- all possible values for the @CCMSalesChannel param here 
    -- (unless there are 50 of them, in which case the db design is wrong)
  FROM credit_card_master ccm
    INNER JOIN PaymentGateway_master pm
    ON pm.payment_gateway_code =ccm.payment_gateway_code  
  WHERE company_id = @CompanyID
END

and

CREATE PROC [GetPaymentGateway2]
  @CompanyID VARCHAR(3)
AS
BEGIN
  SELECT bc.Online_DC_Charge_Amt, m.Payment_Gateway_Name,
    -- all possible values for the @NBSalesChannel param here 
  FROM PG_NetBanking_Charges bc
    INNER JOIN PaymentGateway_master m
    ON m.payment_gateway_code = bc.payment_gateway_code
  WHERE company_id = @CompanyID
END

That said, to join them in a single proc, you can pass an additional parameter, that would determine which query to run, and return the results accordingly, like this:

CREATE PROC [GetPaymentGateway]  
  @CompanyID VARCHAR(3),
  @Type int
AS  
  if (@Type = 1) 
  BEGIN
    SELECT ccm.card_name, ccm.card_type,
      -- all possible values for the @CCMSalesChannel param here 
    FROM credit_card_master ccm
      INNER JOIN PaymentGateway_master pm
      ON pm.payment_gateway_code =ccm.payment_gateway_code  
    WHERE company_id = @CompanyID
  END
  ELSE IF (@Type = 2)
  BEGIN
    SELECT bc.Online_DC_Charge_Amt, m.Payment_Gateway_Name,
      -- all possible values for the @NBSalesChannel param here 
    FROM PG_NetBanking_Charges bc
      INNER JOIN PaymentGateway_master m
      ON m.payment_gateway_code = bc.payment_gateway_code
    WHERE company_id = @CompanyID
 END

However, as it looks that the returned records are not quite similar, I would stick with two procedures, for two different scenarios.

---

For the all possible values for the parameter:

1. if there are just a few (enumerable) options, lets call them Column1, Column2 and Column3, you could use the searched case expression like this:

   SELECT 
     case when @NBSalesChannel='Column1' then Column1
          when @NBSalesChannel='Column2' then Column2
          when @NBSalesChannel='Column3' then Column3
          else null
     end as NBSalesChannel
   FROM ...
   

2. You could simply select all possible columns and use application-side code to take what you want - you'll have some overhead here, but the queries will be simpler

   SELECT Column1, Column2, Column3
   FROM ...
   

3. If you have no idea what columns are available, then as a last resort use dynamic sql, but be very aware of issues like SQL Injection (this is a great article that I always use when it seams that I need dynamic SQL)