Re: Pass SQL function as stored procedure param?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 02/22/04 

Date: Sun, 22 Feb 2004 08:13:22 -0600

You have a few options options.

1) Build a dynamic SQL statement in the proc and execute via sp_executesql
or EXECUTE. See http://www.sommarskog.se/dynamic_sql.html for dynamic SQL
considerations.

2) Build the SQL statement in your application rather than use a proc.

3) Return each value and select the one you need in your app code:

    SELECT ItemID,
        SUM(Score) AS TotalScore,
        AVG(Score) AS AverageScore,
        MIN(Score) AS MinimumScore,
        MAX(Score) AS MaximumScore
    FROM tb_table
    WHERE ItemID = @ItemID
    GROUP BY ItemID

4) Create separate procs for each aggregate function (i.e. astp_summary_sum,
astp_summary_avg, etc) 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy Collins" <jd.collins@ntlworld-not.com> wrote in message
news:jt0_b.358$ta.37@newsfe1-win...
> Hi all,
>
> Is it possible to create a stored procedure where I can
> pass "SUM" or "AVG" etc as a parameter?
>
> Something like
>
> CREATE PROCEDURE astp_summary @ItemID int, @Func varchar(3)
> AS
>
> SELECT ItemID, @Func(Score) AS TotalScore
>    FROM tb_table
>    WHERE ItemID = @ItemID
>    GROUP BY ItemID
> GO
>
>
> So
>    EXEC summary 100, 'SUM'
> would return a result set with SUM(Score)
>
> (I have an application which draws graphs based on the data,
> and the user can choose sum, average, max, min etc, and it would
> be nice to just have one SP).
>
> TIA
>
> -- 
> jc
>
> Remove the -not from email

Relevant Pages

  • Re: Modelers / code generators
    ... > dynamic SQL versus stored procs. ... extensive calculations on a lot of data, often this is done better in the ... >> a proc with another one, requires you to remove the old proc, however that ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Identical database w/ identical stored procedures?
    ... scripting the proc out and running it on all your DB's how may db's are ... Running straigh sp's over dynamic sql is alway better ... Defragmentation leads to downtime...Downtime leads ... INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with ...
    (comp.databases.ms-sqlserver)
  • Re: Variable-named database in a proc
    ... dynamic SQL has been the best method. ... Back in they day, when commodity HW boasted 2GB HDDs and PI 133 MHz CPUs, I used a similar method for linked server queries across a set of sub-servers where the names were only known at run time based on the value of a partial logical key referencing a partition table - giving the query reasonable performance for PK entity select & update across a multi-TB database. ... But we need something to replace the old dbname ... your proc is. ...
    (microsoft.public.sqlserver.programming)
  • Re: create/using type dynamically
    ... Now i am looping thru the keys in a proc .Now for a key, ... values of that key want to use dynamic SQL and FORALL to update that ... In engineering the hardest part ... Please state the business case or business problem you are trying ...
    (comp.databases.oracle.misc)