Dynamic sql tablename in function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic sql tablename in function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-16 : 09:32:20

satish writes "How to pass table name as parameter to function and use it in the function for querying it? or to get values from the tablename passed as parameter to the function in sql server 2000"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-16 : 10:01:10

Exactly what are you trying to do with the table?
What you are describing may not be possible, and is probably inadvisable anyway.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 10:10:22

You can not execute dynamic queries within a function.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-16 : 12:26:34

http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

satishk
Starting Member

39 Posts

Posted - 2006-08-17 : 03:12:27

this is what exactly I am trying to do. thescenario is as follows

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 04:43:42

It is not clear what you REALLY want.

Just use

SELECT	Col2
FROM	DataTbl
WHERE	Col1 = 'x'

let us know what you relly want to solve and post it here.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 05:16:30

quote:

How do I run dynamic sql statements in side a UDF?

As peter mentioned ealier in the thread that you can not use Dynamic SQL under UDF.

quote:

Is there any work around to retrieve data that way?

Make use of Stored Procedure istead of function.


Create Proc TestFun (@ColName varchar(10), @TblName varchar(10))
As 
Begin 
Declare @QryString nvarchar(200),@ParmDefinition nvarchar(100),@Output nvarchar(100)
SET @ParmDefinition = N'@level varchar Output'
Set @QryString = N'Select @Level = ' + @ColName + ' From ' + @TblName  + ' Where ' + @ColName + '=''a'''
EXECUTE  sp_executesql @QryString,  @ParmDefinition,
		 @Output output 
Select @Output 

End 

testFun 'col1','dataTbl'

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:23:02

I don't get the original requirements.

N'Select @Level = ' + @ColName + ' From ' + @TblName + ' Where ' + @ColName + '=''a''' ???

SELECT Col1 FROM Table1 WHERE Col1 = 'a' ? That just produces number of 'a'.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:26:02

You can do something like this

Create function testFun(@colname varchar(10),@tblName varchar(10))
returns varchar(10)
as
Begin
declare @x varchar(10)

if @tblname = 'datatbl' and @colname = 'col1'
	select @x= col2 from dataTbl where col1 = 'a'

if @tblname = 'datatbl' and @colname = 'col2'
	select @x= col3 from dataTbl where col2 = 'a'

if @tblname = 'datatbl2' and @colname = 'col1'
	select @x= col2 from dataTbl2 where col1 = 'a'

if @tblname = 'datatbl2' and @colname = 'col2'
	select @x= col3 from dataTbl2 where col2 = 'a'

return @x

end

But you have to beware of duplicate values.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 05:26:34

quote:

I don't get the original requirements.

Well According to me , satish want to create the function, where he can just pass the Colname and TAblename and the record should be retrived based on it, and the codition should be colname = 'a'.

Thats what i understood, its complete a guess work

from my end

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-17 : 05:44:46

Why stop there? In his own attempt, he only want to return one column (col2).

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', 'x','dataTbl')
select * from dbo.testFun('a', 'x','dataTbl2')

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-17 : 05:47:43

But i dont think what he wants is efficient though.

Chirag

satishk
Starting Member

39 Posts

Posted - 2006-08-18 : 05:13:07

Yes chirag is right that the method suggested by peso is not efficient.
however I thank Peso for for trying his level best to help me

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
India

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 05:22:10

Why is the function suggested by me not efficient? Of course it has to be rewritten to comply with satishk's logic (which i still don't get).

The code in the function just does one insert/select and it is still a function as requested in OP.

Peter Larsson
Helsingborg, Sweden

satishk
Starting Member

39 Posts

Posted - 2006-08-18 : 06:43:45

Hi all,
Peso has been trying hard to help me .
I have explained the scenario .
Actually I want something that should be efficient and applicable for accepting any of the tablename as parameter to function.
select dbo.testFun('x','dataTbl')
However I tried to declare a varible in function and then declare that variable to accept tablename passed as parameter to function so that I can use the variable to query in the function

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-19 : 02:23:46

Satish, what you are trying to do is not atall efficient.

First of all, what you want can only be done by using Dynamic SQL, and in the functions they are not allowed. so in the function you can not do it.

Secondly, for round about solution, you can use stored procedure,for getting the records, where you can pass the colname, tablename and if required condition also and retrived the records. but for getting the result set, you will have 2 trip to the servers for retriving the record which again not effiecient, since this can be done in on single query.

Chirag

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-19 : 08:45:58

Satish,

If you could provide some more information on why you want this kind of function will be more helpful ! Exactly what you are trying to gain by it ? There is neither performance gain nor simplicity.

What is the purpose, avoiding repeatable statements or treating this function output as a derived table which can be fitted into more complex query or something else? Why not write those queries more directly ?

Harsh Athalye
India.
"Nothing is Impossible"

satishk
Starting Member

39 Posts

Posted - 2006-08-21 : 06:39:03

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 06:42:29

If you insist having a function, an approach like mine is the way to go.

Peter Larsson
Helsingborg, Sweden

satishk
Starting Member

39 Posts

Posted - 2006-08-21 : 07:12:53

Yes peso that is fine but the only problem in that is that for each and every table name to be passed as parameter to function ,the table name should be included in the if stmt. I mean it should satisy this condition of @tblname .
if @tblname = 'datatbl' and @colname = 'col1'
The problem is that in case you have multiple tables and if ever the tablename to be passed as parameter to function increases then in that case you will have to drop the function and again modify the function to include the new tablename hence this is what is really hurting me.
Hence I am looking for a solution which can be globally acceptable.
I hope that writing a coded for extended cando it

Satish

India

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 07:31:46

Of course there is a problem with the semi-dynamic logic of my function. It needs to be maintained.
But like I said, if you insist having a function, this is the [only] way to go.

Otherwise, if you insist having the fully-dynamic logic, a stored procedure is the way to go.

Or write an extended stored procedure with some .Net language.

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources