SQL Server Performance

passing tablename as parameter to function and to

Discussion in 'SQL Server 2005 General Developer Questions' started by satish, Aug 18, 2006.

  1. satish New Member

    Hi all,

    How do I run dynamic sql statements in side a UDF?
    Is there any work around to retrieve data that way?

    Example:
    -- Table
    create table dataTbl
    (col1 varchar(5),col2 varchar(5),col3 varchar(5))

    create table dataTbl2
    (col1 varchar(5),col2 varchar(5),col3 varchar(5))

    --Populate data
    insert into dataTbl values ('x','y','z')
    insert into dataTbl values ('a','1','2')
    insert into dataTbl values ('e','3','4')
    insert into dataTbl values ('h','6','7')

    insert into dataTbl2 values ('x','m','n')
    insert into dataTbl2 values ('a','k','l')
    insert into dataTbl2 values ('e','u','o')
    insert into dataTbl2 values ('h','t','y')


    -- function

    Create function testFun(@colname varchar(10),@tblName varchar(10))
    returns varchar(10)
    as
    Begin
    declare @x varchar(10)
    select @x=col2 from dataTbl where col1='a'
    return @x
    end

    -- calling the function
    select dbo.testFun('x','dataTbl')
    select dbo.testFun('x','dataTbl2')

    How can I achive this objective?


    I know that I can do it by in following manner in the function

    CREATE FUNCTION testFun(@value varchar(10), @colname varchar(10),@tblName varchar(10))
    returns @v table (col1 varchar(10), col2 varchar(10), col3 varchar(10))
    AS

    begin
    if @tblname = 'datatbl' and @colname = 'col1'
    insert @v select * from dataTbl where col1 = @value

    if @tblname = 'datatbl' and @colname = 'col2'
    insert @v select * from dataTbl where col2 = @value

    if @tblname = 'datatbl' and @colname = 'col3'
    insert @v select * from dataTbl where col3 = @value

    if @tblname = 'datatbl2' and @colname = 'col1'
    insert @v select * from dataTbl2 where col1 = @value

    if @tblname = 'datatbl2' and @colname = 'col2'
    insert @v select * from dataTbl2 where col2 = @value

    if @tblname = 'datatbl2' and @colname = 'col3'
    insert @v select * from dataTbl2 where col3 = @value

    return
    end

    --select * from dbo.testFun('a', 'col1','dataTbl')
    ---select * from dbo.testFun('a', 'x','dataTbl2')

    go



    select * from datatbl

    select * from datatbl2
    select col2 from datatbl where col1='a'



    create table dataTbl
    (col1 varchar(5),col2 varchar(5),col3 varchar(5))

    create table dataTbl2
    (col1 varchar(5),col2 varchar(5),col3 varchar(5))

    insert into dataTbl values ('x','y','z')
    insert into dataTbl values ('a','1','2')
    insert into dataTbl values ('e','3','4')
    insert into dataTbl values ('h','6','7')


    insert into dataTbl2 values ('x','m','n')
    insert into dataTbl2 values ('a','k','l')
    insert into dataTbl2 values ('e','u','o')
    insert into dataTbl2 values ('h','t','y')

    However I think this not efficient though.

    I know that I cannot use exec in udf hence this is the problem.
    However if someonecan help me with a script for extended stored procedure to get this done.

    I think exetended stored procedure can do this functionality.


    Satish







  2. Adriaan New Member

    Where are you calling this UDF?

    If you're in a sproc, why not create a temp table, do the "dynamic" INSERT INTO, and work with the temp table.
  3. patel_mayur New Member

    How about performing with SP instead of UDF ?

    Knowledge is not Power !!!
    But When its acted upon then its Power !!!
  4. satish New Member

    Hi all,
    I know that stored procedure would have been the best option.
    But my current scenario is that in that case I will have to redo my design and it will create a mess.

    I know that I cannot use exec in udf hence this is the problem.
    However if someonecan help me with a script for extended stored procedure to get this done.

    I think extended stored procedure can do this functionality.
    I mean code for extended stored procedure so as to allow exec command
    or sp_execute

    Satish
  5. Adriaan New Member

    Not sure that I understand why it MUST be a function. What kind of design?
  6. patel_mayur New Member

    I am sorry but this is not the valid reason for creating extended procedure. Should not go for extreme when the things can be done in simple manner.



    Knowledge is not Power !!!
    But When its acted upon then its Power !!!
  7. FrankKalis Moderator

  8. satish New Member

    The reason why I am finding a solution for this bocz I am using a function to retrieve a particular value .
    Now my requirements have been extended wherein I can just pass the tablename dynamically and value can be retrieved .
    I don't want to go for sps bcoz I will have to make modification everwhere the function was called.
    I am sure that this can be done thru extended stored procedures .
    Also it will be a new learning experience for all


    Satish
  9. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I will have to make modification everwhere the function was called<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Be more specific - from what kind of objects are you calling the function?<br /><br />And anyway, if you change the function to accept a table name, then you already have to go through all the calls to add that name.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  10. satish New Member

    As mentioned previously the function will be used in query .
    a sample of query is given

    select dbo.testFun('x','dataTbl')
  11. patel_mayur New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satish</i><br /><br />As mentioned previously the function will be used in query .<br />a sample of query is given <br /><br />select dbo.testFun('x','dataTbl')<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />FYI...<br /><br />Extended Stored Procedure is an "Inprocess", means it shares the same process/memory space as SQL Server. So it may be possible that your DLL could overwrite memory and cause SQL Server corrupt or even crash. You need through testing of your function before putting it into SQL as extended sp.<br /><br />So its good choice to alter function calls instead having possibility of get data corrupted.[<img src='/community/emoticons/emotion-4.gif' alt=':p' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br /><br /><br />Knowledge is not Power !!!<br />But When its acted upon then its Power !!!
  12. Adriaan New Member

    quote:Originally posted by satish

    As mentioned previously the function will be used in query .
    a sample of query is given

    select dbo.testFun('x','dataTbl')
    Why use a function? Looks like you only need a dynamic select statement, which you can do without a function:

    EXEC ('SELECT ' + @column + ' FROM ' + @datatable)

    If you're using a UDF because you have to repeat this for a number of rows, then why not look at JOINing both lookup tables, and use a CASE in your select list to get the correct value from the correct table, something like this:

    SELECT b.key, CASE WHEN ... THEN l1.colx ELSE l2.coly END
    FROM base_table b
    LEFT JOIN lookup_1 l1 ON b.col1 = l1.col1
    LEFT JOIN lookup_2 l2 ON b.col2 = l2.col1
  13. satish New Member

    I know that function has limited use in case of dynamic queries . I even know that sp would have been a better option in my case .
    However as mentioned earlier in example I have a function wherein my objective is to just pass a
    tablename and retrive values and it should work for each an every tablename supplied as parameter to function



    Satish
    india
  14. Adriaan New Member

    Well, UDFs just won't execute dynamic SQL. It's as simple as that.

    If you've been asked to extend the functionality of an existing UDF to accept dynamic SQL, then notify them that they're asking for the impossible, and that a different approach is required.
  15. satish New Member

    Well it is possible to extend the functionality of udfusing extended stored procedures


    Nothing is impossible!!!


    Satish
  16. satish New Member

    Hi all,

    Any research on this topic!!!!!


    Satish
  17. patel_mayur New Member

    Satish,<br /><br />you would like to look into:<br /><br /<a target="_blank" href=http://msdn2.microsoft.com/es-es/library/ms164734.aspx>http://msdn2.microsoft.com/es-es/library/ms164734.aspx</a> ( refer to Important Note also <img src='/community/emoticons/emotion-1.gif' alt=':)' /> )<br /<a target="_blank" href=http://msdn2.microsoft.com/es-es/library/ms164627.aspx>http://msdn2.microsoft.com/es-es/library/ms164627.aspx</a><br /><br />Do you have exposure to Delphi/C++ ?<br />I have created some of XPs using Delphi just for testing purpose.<br /><br /><br /><br /><br /><br /><br />Knowledge is not Power !!!<br />But When its acted upon then its Power !!!
  18. satish New Member

    Yes ,I have worked on c++.
    can u write xps for the question that has been posted.

    Satish
  19. Adriaan New Member

    But is it worth the trouble?
  20. satish New Member

    yes it is worth for me

Share This Page