Invalid use of a side-effecting operator 'INSERT EXEC' within a function:

  • Is the following always true, @MyNum and @MyNum_Middle always the same value?

    select @MyNum = '''''22026'''

    select @MyNum_Middle = '''''22026'''''

  • yes, that is not a problem I was experimenting something.. but they will be and should be using the @emp_id that comes in as a param..

    Cheers,
    John Esraelo

  • John Esraelo (5/11/2009)


    yes, that is not a problem I was experimenting something.. but they will be and should be using the @emp_id that comes in as a param..

    Didn't say it was a problem, but looking at your query, it can be rewritten to eliminate a nice piece of RBAR in the select statement by adding a table in the FROM clause.

  • I am on a conference call with the development team and my boss about this issue and see if I have job by the end of the day .. hehehe :w00t:

    This may go to an hour or so...

    Cheers,
    John Esraelo

  • While you are on the phone, can you tell me if this query returns the expected result set?

    select

    *

    from

    openquery(ads_link1111,

    'SELECT

    [BRGEMP].[ID],

    ltrim(rtrim([BRGEMP].[LNAME])) ,

    case length([BRGEMP].[flname])

    when 0 then [BRGEMP].[FNAME]

    else [BRGEMP].[FLNAME]

    end as "FName",

    ltrim(rtrim([BRGEMP].[SSN])) "SSN",

    convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",

    convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",

    convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",

    case BRGEMP.STATUS

    when 1 then ''Applicant''

    when 2 then ''Terminated''

    when 3 then ''Active''

    when 4 then ''Unknown''

    end ,

    BRGAPPL.TERMINATED,

    BRGAPPL.HIRED,

    '' AS JobNum,

    e. DivisionCode,

    CASE p.POSTYPE

    WHEN 1 THEN ''FIELD''

    WHEN 2 THEN ''OFFICE''

    WHEN 3 THEN ''OFFICER''

    WHEN 4 THEN ''SHOP''

    ELSE ''UNKNOWN''

    end "HR_Class"

    FROM

    BRGEMP

    INNER JOIN [elsoffi] e

    on (e. = [BRGEMP].[p_elsoffi])

    INNER JOIN [brgpostp] p

    on (p. = [brgemp].[p_brgpostp])

    LEFT OUTER JOIN BRGAPPL

    ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]

    ')

    ;

    You can try the following if you want to reduce the size of what is returned:

    select

    *

    from

    openquery(ads_link1111,

    'SELECT

    [BRGEMP].[ID],

    ltrim(rtrim([BRGEMP].[LNAME])) ,

    case length([BRGEMP].[flname])

    when 0 then [BRGEMP].[FNAME]

    else [BRGEMP].[FLNAME]

    end as "FName",

    ltrim(rtrim([BRGEMP].[SSN])) "SSN",

    convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",

    convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",

    convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",

    case BRGEMP.STATUS

    when 1 then ''Applicant''

    when 2 then ''Terminated''

    when 3 then ''Active''

    when 4 then ''Unknown''

    end ,

    BRGAPPL.TERMINATED,

    BRGAPPL.HIRED,

    '' AS JobNum,

    e. DivisionCode,

    CASE p.POSTYPE

    WHEN 1 THEN ''FIELD''

    WHEN 2 THEN ''OFFICE''

    WHEN 3 THEN ''OFFICER''

    WHEN 4 THEN ''SHOP''

    ELSE ''UNKNOWN''

    end "HR_Class"

    FROM

    BRGEMP

    INNER JOIN [elsoffi] e

    on (e. = [BRGEMP].[p_elsoffi])

    INNER JOIN [brgpostp] p

    on (p. = [brgemp].[p_brgpostp])

    LEFT OUTER JOIN BRGAPPL

    ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]

    WHERE

    brgemp.id = 22026

    ')

    ;

  • I modified a little and it runs and returns two records...

    select

    *

    from

    openquery(ads_link1111,

    'SELECT

    [BRGEMP].[ID],

    ltrim(rtrim([BRGEMP].[LNAME])) ,

    case length([BRGEMP].[flname])

    when 0 then [BRGEMP].[FNAME]

    else [BRGEMP].[FLNAME]

    end as "FName",

    ltrim(rtrim([BRGEMP].[SSN])) "SSN",

    convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",

    convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",

    convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",

    case BRGEMP.STATUS

    when 1 then ''Applicant''

    when 2 then ''Terminated''

    when 3 then ''Active''

    when 4 then ''Unknown''

    end ,

    BRGAPPL.TERMINATED,

    BRGAPPL.HIRED,

    '''' AS JobNum,

    e. DivisionCode,

    CASE p.POSTYPE

    WHEN 1 THEN ''FIELD''

    WHEN 2 THEN ''OFFICE''

    WHEN 3 THEN ''OFFICER''

    WHEN 4 THEN ''SHOP''

    ELSE ''UNKNOWN''

    end "HR_Class"

    FROM

    BRGEMP

    INNER JOIN [elsoffi] e

    on (e. = [BRGEMP].[p_elsoffi])

    INNER JOIN [brgpostp] p

    on (p. = [brgemp].[p_brgpostp])

    LEFT OUTER JOIN BRGAPPL

    ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]

    WHERE

    brgemp.id = ''22026''

    ')

    Cheers,
    John Esraelo

  • Here is alittle more code, make what ever changes you need to make.

    create view dbo.EmpData

    as

    select

    *

    from

    openquery(ads_link1111,

    'SELECT

    [BRGEMP].[ID],

    ltrim(rtrim([BRGEMP].[LNAME])) ,

    case length([BRGEMP].[flname])

    when 0 then [BRGEMP].[FNAME]

    else [BRGEMP].[FLNAME]

    end as "FName",

    ltrim(rtrim([BRGEMP].[SSN])) "SSN",

    convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",

    convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",

    convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",

    case BRGEMP.STATUS

    when 1 then ''Applicant''

    when 2 then ''Terminated''

    when 3 then ''Active''

    when 4 then ''Unknown''

    end as "Status",

    BRGAPPL.TERMINATED,

    BRGAPPL.HIRED,

    '' AS JobNum,

    e. DivisionCode,

    CASE p.POSTYPE

    WHEN 1 THEN ''FIELD''

    WHEN 2 THEN ''OFFICE''

    WHEN 3 THEN ''OFFICER''

    WHEN 4 THEN ''SHOP''

    ELSE ''UNKNOWN''

    end "HR_Class"

    FROM

    BRGEMP

    INNER JOIN [elsoffi] e

    on (e. = [BRGEMP].[p_elsoffi])

    INNER JOIN [brgpostp] p

    on (p. = [brgemp].[p_brgpostp])

    LEFT OUTER JOIN BRGAPPL

    ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]

    ')

    ;

    GO

    SELECT

    ed.*

    FROM

    dbo.EmpData ed

    WHERE

    ed.ID = '22026'

    GO

    CREATE FUNCTION dbo.EmployeeData(

    @EmpID nvarchar(20)

    )

    RETURNS table

    AS

    RETURN(

    SELECT

    ed.*

    FROM

    dbo.EmpData ed

    WHERE

    ed.ID = @EmpID

    );

    GO

    select * from dbo.EmployeeData(N'22026');

    GO

  • John Esraelo (5/11/2009)


    I modified a little and it runs and returns two records...

    select

    *

    from

    openquery(ads_link1111,

    'SELECT

    [BRGEMP].[ID],

    ltrim(rtrim([BRGEMP].[LNAME])) ,

    case length([BRGEMP].[flname])

    when 0 then [BRGEMP].[FNAME]

    else [BRGEMP].[FLNAME]

    end as "FName",

    ltrim(rtrim([BRGEMP].[SSN])) "SSN",

    convert([BRGAPPL].[HIREDATE], sql_date) "HireDate",

    convert([BRGAPPL].[STARTDATE], sql_date) "StartDate",

    convert([BRGAPPL].[TERMDATE], sql_date) "TermDate",

    case BRGEMP.STATUS

    when 1 then ''Applicant''

    when 2 then ''Terminated''

    when 3 then ''Active''

    when 4 then ''Unknown''

    end ,

    BRGAPPL.TERMINATED,

    BRGAPPL.HIRED,

    '''' AS JobNum,

    e. DivisionCode,

    CASE p.POSTYPE

    WHEN 1 THEN ''FIELD''

    WHEN 2 THEN ''OFFICE''

    WHEN 3 THEN ''OFFICER''

    WHEN 4 THEN ''SHOP''

    ELSE ''UNKNOWN''

    end "HR_Class"

    FROM

    BRGEMP

    INNER JOIN [elsoffi] e

    on (e. = [BRGEMP].[p_elsoffi])

    INNER JOIN [brgpostp] p

    on (p. = [brgemp].[p_brgpostp])

    LEFT OUTER JOIN BRGAPPL

    ON [BRGEMP]. = [BRGAPPL].[P_BRGEMP]

    WHERE

    brgemp.id = ''22026''

    ')

    Were the results correct??

  • would this go inside the view also:

    GO

    SELECT

    ed.*

    FROM

    dbo.EmpData ed

    WHERE

    ed.ID = '22026'

    GO

    Cheers,
    John Esraelo

  • John Esraelo (5/11/2009)


    would this go inside the view also:

    GO

    SELECT

    ed.*

    FROM

    dbo.EmpData ed

    WHERE

    ed.ID = '22026'

    GO

    No. This is testing the view.

  • I see 🙂

    Cheers,
    John Esraelo

  • So, what is happening?? Still on the conference call?

  • There are highly interested in this issue and I wrapped up my stuff and hoping to finish this thing pdq..

    :))

    Cheers,
    John Esraelo

  • As I expected; the test for the view takes 20+ second to run and I have not tested it from running a function yet.. that's next

    Cheers,
    John Esraelo

  • John Esraelo (5/11/2009)


    As I expected; the test for the view takes 20+ second to run and I have not tested it from running a function yet.. that's next

    The function should run about the same.

Viewing 15 posts - 16 through 30 (of 52 total)

You must be logged in to reply to this topic. Login to reply