Sending Column Name as a Parameter to Aggregate function

How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .

my procedure like this:

ALTERPROCEDURE [dbo].[ORNEK10]

@YILVARCHAR(4),

@TEKLIF_TURUVARCHAR(255)

AS

BEGIN

DECLARE @N1FLOAT

DECLARE @N2FLOAT

SET @N1=(SELECT DEGERIFROM PARAMETREWHERE PARAMETRE_ADI='N1')

SET @N2=(SELECT DEGERIFROM PARAMETREWHERE PARAMETRE_ADI='N2')

SETNOCOUNTON;

--I want to avoid using if statements for Sum() function

IF(@TEKLIF_TURU='BASKAN_TEKLIF')

SELECTTOP(100)PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4)AS ACIKLAMA,

SUM(BASKAN_TEKLIF)AS YILI,

((100+@N1)*SUM(BASKAN_TEKLIF))/100AS YIL1,

((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000AS YIL2

FROM GELIRAS GWHERE YIL= @YILGROUPBY KOD1, KOD2, KOD3, KOD4WITH ROLLUP

ORDERBY KOD1, KOD2, KOD3, KOD4

IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')

SELECTTOP(100)PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4)AS ACIKLAMA,

SUM(ENCUMEN_TEKLIF)AS YILI,

((100+@N1)*SUM(ENCUMEN_TEKLIF))/100AS YIL1,

((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000AS YIL2

FROM GELIRAS GWHERE YIL= @YILGROUPBY KOD1, KOD2, KOD3, KOD4WITH ROLLUP

ORDERBY KOD1, KOD2, KOD3, KOD4

IF(@TEKLIF_TURU='MECLIS_TEKLIF')

SELECTTOP(100)PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4)AS ACIKLAMA,

SUM(MECLIS_TEKLIF)AS YILI,

((100+@N1)*SUM(MECLIS_TEKLIF))/100AS YIL1,

((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000AS YIL2

FROM GELIRAS GWHERE YIL= @YILGROUPBY KOD1, KOD2, KOD3, KOD4WITH ROLLUP

ORDERBY KOD1, KOD2, KOD3, KOD4

END

[13034 byte] By [RamazanAcar] at [2007-12-22]
# 1

-- We can use the case statement to decide which sum to return

-- the top 100 percent is pointless unless you are trying to use an order by in a view

-- since this is a proc it is pointless to do top 100 percent

ALTER PROCEDURE [dbo].[ORNEK10]

@YIL VARCHAR(4),

@TEKLIF_TURU VARCHAR(255)

AS

BEGIN

DECLARE @N1 FLOAT

DECLARE @N2 FLOAT

SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')

SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')

SET NOCOUNT ON;

SELECT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,

CASE @TEKLIF_TURU

WHEN 'BASKAN_TEKLIF' THEN SUM(BASKAN_TEKLIF)

WHEN 'ENCUMEN_TEKLIF' THEN SUM(ENCUMEN_TEKLIF)

WHEN 'MECLIS_TEKLIF' THEN SUM(MECLIS_TEKLIF)

END AS YILI,

((100+@N1)*

CASE @TEKLIF_TURU

WHEN 'BASKAN_TEKLIF' THEN SUM(BASKAN_TEKLIF)

WHEN 'ENCUMEN_TEKLIF' THEN SUM(ENCUMEN_TEKLIF)

WHEN 'MECLIS_TEKLIF' THEN SUM(MECLIS_TEKLIF)

END)/100 AS YIL1,

((100+@N1)*(100+@N2)*

CASE @TEKLIF_TURU

WHEN 'BASKAN_TEKLIF' THEN SUM(BASKAN_TEKLIF)

WHEN 'ENCUMEN_TEKLIF' THEN SUM(ENCUMEN_TEKLIF)

WHEN 'MECLIS_TEKLIF' THEN SUM(MECLIS_TEKLIF)

END)/10000 AS YIL2

FROM GELIR AS G

WHERE YIL = @YIL

GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP

ORDER BY KOD1, KOD2, KOD3, KOD4

END

DavidLundell at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 2

Hi david

I want to know way of using parameters as a column name in aggregate function if there is a way.

I dont want to check parameter so i dont want to use conditional clauses(if , case....) in my code.

Thanks for your help.

RamazanAcar at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...
# 3

You can use dynamic sql exec('') or sp_executesql, but this will surely cause your queries to be less efficient, as well as add to your burden since you will need to ensure that the parameters don't contain sql injection attacks

Why don't you want to use case in your code?

DavidLundell at 2007-8-30 > top of Msdn Tech,SQL Server,Transact-SQL...

SQL Server

Site Classified