Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: Using function in SELECT statement

    Hi, I am trying to make use of reusing my functions in my db. Here is the function that I want to use inside of a select statement:

    DECLARE @year bigint
    DECLARE @intermont_access bit
    DECLARE @hodges_access bit
    DECLARE @main_access bit
    DECLARE @east_access bit
    DECLARE @west_access bit
    DECLARE @prater_apartments_access bit
    DECLARE @moore_st_house_access bit
    DECLARE @office_access bit
    SELECT @year = 2010
    SELECT @intermont_access = 1
    SELECT @hodges_access = 1
    SELECT @main_access = 1
    SELECT @east_access = 1
    SELECT @west_access = 1
    SELECT @prater_apartments_access = 1
    SELECT @moore_st_house_access = 1
    SELECT @office_access = 1

    SELECT * FROM [dbo].[hd_weekly_report_select_reports_by_building_acess](@year, @intermont_access, @hodges_access, @main_access, @east_access, @west_access, @prater_apartments_access, @moore_st_house_access, @office_access) AS Reports;

    I receive the error, Invalid object name 'dbo.hd_weekly_report_select_reports_by_building_a cess'.

    Just found out I misspelled access, that is how the function is defined. I plan on using a left join with another table, however i want this to work first!

    I can run the function by itself using this format:
    dbo.hd_weekly_report_select_reports_by_building_ac ess '2010','1','1','1','1','1','1','1','1';

    Any help is GREATLY appreciated!!

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    if i understand the problem correctly, the JOIN will be as follows

    Select Reports.*,
    <TableName>.*
    From [dbo].[hd_weekly_report_select_reports_by_building_acess]
    (
    @year, @intermont_access, @hodges_access, @main_access,
    @east_access, @west_access, @prater_apartments_access,
    @moore_st_house_access, @office_access
    ) Reports
    Left Outer Join <TableName>
    On reports.<joincolumn> = <TableName>.<joincolumns>
    Cheers....

    baburajv

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does the function return?

    Does it return a table or a string, or a number, etc?
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by baburajv
    if i understand the problem correctly, the JOIN will be as follows

    Select Reports.*,
    <TableName>.*
    From [dbo].[hd_weekly_report_select_reports_by_building_acess]
    (
    @year, @intermont_access, @hodges_access, @main_access,
    @east_access, @west_access, @prater_apartments_access,
    @moore_st_house_access, @office_access
    ) Reports
    Left Outer Join <TableName>
    On reports.<joincolumn> = <TableName>.<joincolumns>
    Thanks for your reply, however I am still getting the error of invalid object name for [dbo].[hd_weekly_report_select_reports_by_building_acess]. Any other ideas?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Could you post your exact function definition please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by pootle flump
    Could you post your exact function definition please?
    Found the solution. Actually use a function instead of a procedure. Sorry for the confusion guys, thanks for the help.

    Out of curiosity, is it possible to use a stored proc in a select statement like a function?

    Thanks again.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by robertmills
    Out of curiosity, is it possible to use a stored proc in a select statement like a function?
    Yes...and no. There is a hack, but you really want to avoid it.

    You can use multi statement table value functions - I would always use those over sprocs. Note that multi statement functions (scalar or table value) are (broadly speaking) much less efficient than inline table value functions, so use those where you can.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by pootle flump
    Yes...and no. There is a hack, but you really want to avoid it.

    You can use multi statement table value functions - I would always use those over sprocs. Note that multi statement functions (scalar or table value) are (broadly speaking) much less efficient than inline table value functions, so use those where you can.
    With the hack is it more efficient to use a stored proc instead of a function? I am returning a table of reports based on whether or not a user has access to a particular building's reports (it's a higher education paperwork application) and joining that with a table of reports they have read to determine what reports the user needs to acknowledge.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Use the function - forget about using the sproc. Only if you are absolutely convinced that you can't convert the sproc to a function would you consider that. And probably not even then.
    I wish I hadn't mentioned the hack

    It sounds like that is a pretty simple query - I'm not even sure you really need a function.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2009
    Posts
    5
    Quote Originally Posted by pootle flump
    Use the function - forget about using the sproc. Only if you are absolutely convinced that you can't convert the sproc to a function would you consider that. And probably not even then.
    I wish I hadn't mentioned the hack

    It sounds like that is a pretty simple query - I'm not even sure you really need a function.
    Lol, what hack? What stored proc?

    It's a function now and always will be!

    Thanks for your help

Posting Permissions

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