Passing Columns to Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Passing Columns to Function

Author

Topic

zia.ahsan
Starting Member

2 Posts

Posted - 2012-05-28 : 04:37:35

Hi All, I am using SQL Server 2005 and have the following function working properly. Now I need to pass some (but not all, bold and red) columns for select and group by at runtime. How can I do that and what is the best way to achieve it?

ALTER FUNCTION [dbo].[GET_MT_DAILY_HIST] (@STARTDATE DATETIME, @ENDDATE DATETIME)
RETURNS TABLE AS
RETURN
(
SELECT OP_CODE, SHORT_CODE, SID, CP_CODE, KEYWORD, 
CONVERT(CHAR(10), SEND_DATE_TO_OP, 101) AS REC_TERM, YEAR(SEND_DATE_TO_OP) AS REC_YEAR,
SUM(TOTAL) AS TOTAL
FROM CPGW_SUMMARY.DBO.MT_SUMMARY
WHERE SEND_DATE_TO_OP BETWEEN @STARTDATE AND @ENDDATE
GROUP BY OP_CODE, SHORT_CODE, SID, CP_CODE, KEYWORD, 
CONVERT(CHAR(10), SEND_DATE_TO_OP, 101), YEAR(SEND_DATE_TO_OP)
)

Thanks in advance.
Regards,
-Zia

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:59:13

you can do it using function. to determine grouping at runtime you need dynamic sql.
Whats the need of this requirement? Is it for a user interactive report? if yes, you can achieve this dynamic grouping in most of the front end reporting tools

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zia.ahsan
Starting Member

2 Posts

Posted - 2012-05-29 : 02:30:09

Hi Visakh, thank you very much for your suggestion. Yes, this is for user interactive report where users can chose the group by elements in any order. I agree with you as I am using DynamicReports (based on JasperReports) I can group the elements dynamically.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:10:14

quote:
Originally posted by zia.ahsan

Hi Visakh, thank you very much for your suggestion. Yes, this is for user interactive report where users can chose the group by elements in any order. I agree with you as I am using DynamicReports (based on JasperReports) I can group the elements dynamically.

bring data in detail to report and based on user input do required grouping and aggregation inside reporting application

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources