Passing Parameter(table-name) in user defined 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

SQL Server Development (2000)

Passing Parameter(table-name) in user defined function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-31 : 07:13:27

Rajesh N writes "Is there any possiblity to pass tablename as parameter in SQL Server 2000. If so please send me the solution with an example.

With Regards,
Rajesh N"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-31 : 07:16:10

where you want to pass the parameter? to the stored procedure or to the function..??

if you are thinking to pass the paramter to the function, then its not possible... since you can not use the dynamic sql in the function.

if the procedure then yes you can pass the table name and then using the sp_excutesql or exec() run your dynamic sql..

Chirag

http://chirikworld.blogspot.com/

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-10-31 : 08:36:35

...
(@TableName VARCHAR(25)
@strSQL VARCHAR(1000))
AS

SET @strSQL = 'Select <Columns> From ' + @TableName + ' Where <we criteria>'

EXEC (@strSQL)
...

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-31 : 09:03:19

Passing table name as parameter is not advisible.
www.Sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

satishk
Starting Member

39 Posts

Posted - 2006-11-01 : 02:40:42

It is possible to pass tablename as parameter to 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', 'x','dataTbl')
select * from dbo.testFun('a', 'x','dataTbl2')
Though I know that semi logic function code has some limitation .
I just want to try wheteher the above function can be called in another function
so as to return a single value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 02:45:14

In the other function, write select * from

select * from dbo.testFun('a', 'x','dataTbl') where colx = <some value>

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources