Professional Web Applications Themes

UDF Q - Microsoft SQL / MS SQL Server

Following is mu udf: CREATE FUNCTION [dbo].[fn_PIMetTotalNESecMixLiquorFlow] ( cPlant char(3), sdtstartdatetime smalldatetime, sdtEndDatetime smalldatetime, Res varchar(20) ) RETURNS tblMixLiq table ([datetime] smalldatetime, value real) AS BEGIN Declare vcSQL varchar(5000) Select vcSQL = case res When 'Hourly' Then ' select at09.datetime,isnull(at09.Value,0) as [TK # 09] from fn_PIGetHourlydataForPlant(' + '''' + Plant+ '''' +',null,'+ ''''+convert(varchar(50),sdt)+''''+',' + ''''+convert(varchar(50),sdt1)+''''+', ' + '''' + 'MetNESecAT09' + '''' + ' ) as AT09 ' When 'Daily' Then ' select at09.datetime,isnull (at09.Value,0) as [TK # 09] from fn_PIGetHourlydataForPlant (' + '''' + Plant+ '''' +',null,'+ ''''+convert(varchar (50),sdt)+''''+',' + ''''+convert(varchar(50),sdt1) +''''+', ' + '''' + 'MetNESecAT09' + '''' ...

  1. #1

    Default UDF Q

    Following is mu udf:

    CREATE FUNCTION [dbo].[fn_PIMetTotalNESecMixLiquorFlow] (
    cPlant char(3), sdtstartdatetime smalldatetime,
    sdtEndDatetime smalldatetime, Res varchar(20) )

    RETURNS tblMixLiq table ([datetime] smalldatetime, value
    real) AS

    BEGIN

    Declare vcSQL varchar(5000)

    Select vcSQL = case res When 'Hourly' Then ' select
    at09.datetime,isnull(at09.Value,0) as [TK # 09] from
    fn_PIGetHourlydataForPlant(' + '''' + Plant+ ''''
    +',null,'+ ''''+convert(varchar(50),sdt)+''''+','
    + ''''+convert(varchar(50),sdt1)+''''+', ' + ''''
    + 'MetNESecAT09' + '''' + ' ) as AT09 '

    When 'Daily' Then ' select at09.datetime,isnull
    (at09.Value,0) as [TK # 09] from fn_PIGetHourlydataForPlant
    (' + '''' + Plant+ '''' +',null,'+ ''''+convert(varchar
    (50),sdt)+''''+',' + ''''+convert(varchar(50),sdt1)
    +''''+', ' + '''' + 'MetNESecAT09' + '''' + ' ) as AT09 '

    End

    Insert tblMixLiq Exec (vcSQL)

    Return

    END

    The error I get is EXECUTE cannot be used as source when
    inserting into table variable.

    I understand the error but I need other ways how could I
    accomplish the above logic.

    I have two different select statements that need to be
    executed depending upon res parameter and insert the
    result into temporary table.

    Thanks for any help.
    Ricky Guest

  2. #2

    Default Re: UDF Q

    I assume what you posted was a mistake and that the function following "When
    'Daily'... " should read fn_PIGetDailydataForPlant - otherwise the res
    param makes no difference.

    Please post the code for the two functions fn_PIGetHourlydataForPlant and
    fn_PIGetDailydataForPlant and include DDL (CREATE TABLE statements) and
    sample data (INSERT statements) if possible. It may be the case that you
    don't need two separate functions at all.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: UDF Q

    David,

    The error you caught is a type. Yes, it should read
    fn_PIGetDailyDataFroPlant. Sorry about that.

    Following is the code for the two functions:

    CREATE FUNCTION [dbo].[fn_PIGetHourlyDataForPlant]
    (cPlant char(3), tintIncNumber tinyint, dtStartDatetime
    datetime, dtEndDatetime datetime, vcTagType varchar
    (50) )
    RETURNS table

    AS


    RETURN Select TDH.Tagkey, TDH.[datetime], TDH.Value,
    TDH.Quality From dbo.TagDataHourly TDH Where TDH.Tagkey =
    ( Select tagkey From fn_PIGetTagCriteriaTagkey(cPlant,
    tintIncNumber, dtStartDatetime, vcTagType) ) and (TDH.
    [Datetime] >= convert(smalldatetime, CONVERT(varchar(8),
    CONVERT(varCHAR(8), dtStartDatetime, 112)) + ' ' + convert
    (varchar(2), datepart(hour, dtStartDatetime)) + ':00:00',
    112) and TDH.[Datetime] <= Coalesce(convert(smalldatetime,
    CONVERT(varchar(8), CONVERT(varCHAR(8), dtEndDatetime,
    112)) + ' ' + convert(varchar(2), datepart(hour,
    dtEndDatetime)) + ':00:00', 112) , convert
    (smalldatetime, CONVERT(varchar(8), CONVERT(varCHAR(8),
    dtStartDatetime, 112)) + ' ' + convert(varchar(2),
    datepart(hour, dtStartDatetime)) + ':00:00', 112) ) )

    ========

    CREATE FUNCTION [dbo].[fn_PIGetDailyDataForPlant] (cPlant
    char(3), tintIncNumber tinyint, dtStartDatetime
    datetime, dtEndDatetime datetime, vcTagType varchar
    (50) )
    RETURNS table

    AS

    RETURN Select TDD.Tagkey, TDD.[datetime], TDD.Value,
    TDD.Quality From dbo.TagDataDaily TDD Where TDD.Tagkey = (
    Select tagkey From fn_PIGetTagCriteriaTagkey(cPlant,
    tintIncNumber, dtStartDatetime, vcTagType) ) and (TDD.
    [Datetime] >= convert(smalldatetime, convert(char(8),
    dtStartDatetime, 112 ) ) and TDD.[Datetime] <= Coalesce
    (convert(smalldatetime, convert(char(8), dtEndDatetime,
    112 )) , convert(smalldatetime, convert(char(8),
    dtStartDatetime, 112 ) ) ) )


    TagDataDaily and TagdataHourly are two separate tables
    containing data at daily and hourly resolutions resp.

    TagDataDaily: Sample Data

    Tagkey, Datetime, Quality, Value
    733, 2003-08-01 00:00:00, 192, 23.45
    733, 2003-08-02 00:00:00, 192, 21.23
    734, 2003-08-01 00:00:00, 198, 678.9

    etc...etc...

    TagDataHouly: Sample Data
    Tagkey, Datetime, Quality, Value
    733, 2003-08-01 00:00:00, 192, 23.34
    733, 2003-08-01 01:00:00, 192, 23.45
    733, 2003-08-01 02:00:00, 192, 21.32

    etc...etc...

    Tagkey from these tables links to Tagtable to get the
    actual tagname such as 'GF021WT' .

    Thanks for help David.

    Ricky






     
    function following "When 
    otherwise the res 
    fn_PIGetHourlydataForPlant and 
    statements) and 
    the case that you 
    Ricky Guest

  4. #4

    Default Re: UDF Q

    Can you do something like this?

    create function ...
    returns table as return

    select ...
    from ...
    where vcSQL = 'Hourly'
    union all
    select ...
    from ...
    where vcSQL = 'Daily'

    If it's possible, it has the advantage of being an inline
    table-valued function, which may optimizer better.

    -- Steve Kass
    -- Drew University
    -- Ref: 33BAC259-D812-4717-A0C3-F8B40B2B8805

    Ricky wrote:
     

    Steve Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139