user1080139 user1080139 - 1 year ago 64
SQL Question

Display a resultset where each cell is based on different condition using stored procedure

I have a table from which I query data to display resultset using stored procedure. I also have a value to be given in the where condition that keeps changing for each and every cell and that parameter is not available in the table from which I have to query. As of now, I have hardcoded the for that particular parameter. My resultset should consist of 7 rows and 16 columns. Is there any optimistic way instead of hardcoding it ? Any help is appreciated.

Here is the temp table I ve hardcoded:

create table #temp_financials(iden_val int identity(1,1) ,
categ varchar(100),Retail_RDR varchar(100),Avg_Price_RDR varchar(100),
GPVR_RDR varchar(100),
L_RDR varchar(100),
L_Avg_Price varchar(100),L_GPVL varchar(100),
C_RDR varchar(100),C_Avg_Price varchar(100),C_GPVR varchar(100),
C_Rgn_GPVR varchar(100),C_Nat_GPVR varchar(100),
C_Lease_Penet varchar(100),C_Rgn_Penet varchar(100))

insert into #temp_financials(categ,Retail_RDR ,Avg_Price_RDR , GPVR_RDR ,
L_RDR ,L_Avg_Price ,L_GPVL ,
C_RDR ,C_Avg_Price ,C_GPVR ,C_Rgn_GPVR ,C_Nat_GPVR ,
C_Lease_Penet ,C_Rgn_Penet )
values('Ttl Sum',
'B9720','B9996','B9776','B9722','B9997','B9791','B9733','B10003','B9806','B9806 ','B9806','B9885','B9885' )
insert into #temp_financials(categ,Retail_RDR ,Avg_Price_RDR , GPVR_RDR ,
L_RDR ,L_Avg_Price ,L_GPVL ,
C_RDR ,C_Avg_Price ,C_GPVR ,C_Rgn_GPVR ,C_Nat_GPVR ,
C_Lease_Penet ,C_Rgn_Penet )
values ('B9870','B9773','4042A','B9998','B9788',

Here is the table with which I ve to join:


select t.categ,Retail_RDR=w1.CMValue,Avg_Price_RDR=w2.CMValue,
from westernunion w1 join #temp_financials t on
w1.acct_no=t.retail_RDR left outer join
westernunion w2 on w2.acct_no=t.avg_price_rdr
left outer join westernunion w3 on w3.acct_no=t.GPVR_RDR
left outer join westernunion w4 on w4.acct_no=t.L_RDR
where t.iden_val=@loop_var
set @loop_var=@loop_var+1

Answer Source

Use dynamic SQL query:

Declare @vQuery nvarchar(max),
        @vWhereClasue varchar(500)

SET @vQuery = 'Select top 7 col1,col2 ....col16 from ... where ' + @vWhereClasue
exec sp_executesql @vQuery
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download