SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Ireland
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql server user function problem

    hi can anyone see a problem with this
    Code:
    CREATE FUNCTION dbo.fn_getCathCount(@tableName	varchar(30), @cathId int)
    RETURNS INT
    AS 
    BEGIN
    	DECLARE @rtnVal int
    	SELECT @rtnVal = COUNT(*) FROM @tableName WHERE CathID = @cathId
    	RETURN @rtnVal
    END
    I get an error saying that the @tableName needs to be declared but there dosen't seem to be any problem with the other variable passed in as a parameter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you need to use the EXEC command
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Ireland
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i need to get the result as a variable do you know any ways of getting the exec output into a variable

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    DECLARE sqlstring varchar(937)
    SET sqlstring 
      = 'SELECT @rtnVal = COUNT(*) FROM '
      + @tableName 
      + ' WHERE CathID = @cathId'
    EXEC(sqlstring)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    DECLARE sqlstring varchar(937)
    very funny Rudy.

    compliments of the season

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sir,
    please take a break. Its xmas remember ?

    :-)

  8. #8
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Using exec with a string as argument is not allowed in user-defined functions.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, ya learn something every day, eh

    thanks swampboogie

    i guess i never knew that, because i never had a need to create a function -- the ones SQL Server comes with have always been sufficient for me
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru puco's Avatar
    Join Date
    Feb 2005
    Location
    Slovakia
    Posts
    785
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess you can't do it without, because UDFs a 'pre-compiled' and since SQL doesn't support something like inheritence, how could it know if you table you pass into the function even has a cathId column.

    You can hawever create a UDF that returns number of all rows. But that won't help you I guess.

    But seeing what want to accomplish are you sure you need such a generic function? It looks to me as if you have a problem with your database schema. After all if the items in tables have something as 'catID' common shouldn't they be in same table, or shouldn't you have a separate table for that usage?
    Martin Pernecky


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
  •