SQL Server Performance Forum – Threads Archive

passing tablename as parameter to function and to

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

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.

How about performing with SP instead of UDF ? Knowledge is not Power !!!
But When its acted upon then its Power !!!

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

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

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 !!!

See if this helps:http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

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

<blockquote 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"></blockquote id="quote">Be more specific – from what kind of objects are you calling the function? 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=’

‘ />]

As mentioned previously the function will be used in query .
a sample of query is given select dbo.testFun(‘x’,’dataTbl’)

<blockquote id="quote">quote:<hr height="1" noshade id="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’) <hr height="1" noshade id="quote"></blockquote id="quote"> FYI… 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. So its good choice to alter function calls instead having possibility of get data corrupted.[<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Knowledge is not Power !!! But When its acted upon then its Power !!!

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

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

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.

Well it is possible to extend the functionality of udfusing extended stored procedures
Nothing is impossible!!!
Satish

Hi all, Any research on this topic!!!!!
Satish

Satish, you would like to look into: http://msdn2.microsoft.com/es-es/library/ms164734.aspx</a> ( refer to Important Note also <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ) http://msdn2.microsoft.com/es-es/library/ms164627.aspx</a> Do you have exposure to Delphi/C++ ? I have created some of XPs using Delphi just for testing purpose. Knowledge is not Power !!! But When its acted upon then its Power !!!

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

But is it worth the trouble?

yes it is worth for me

]]>