| Author |
User-Defined Function
|
|
|
| Hi
I want to create a user-defined function with input of different table name
and output of same table as input.
What should I do?
Example:
"Create Function MyTable (@MyTableName varchar(20))
RETURN Table
AS
BEGIN
SELECT *
FROM @MyTableName
RETURN
END"
Please tell me the right way to do this.
Thanks
| |
| Alejandro Mesa 2005-09-26, 11:23 am |
| Mehdi,
You will need dynamic sql to accomplish this.
create procedure dbo.p1
@tn as sysname
as
set nocount on
declare @sql nvarchar(500)
set @sql = N'select * from ' + quotename(@tn)
exec sp_executesql @sql
return @@error
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Mehdi" wrote:
> Hi
>
> I want to create a user-defined function with input of different table name
> and output of same table as input.
> What should I do?
> Example:
>
> "Create Function MyTable (@MyTableName varchar(20))
> RETURN Table
> AS
> BEGIN
> SELECT *
> FROM @MyTableName
> RETURN
> END"
>
>
> Please tell me the right way to do this.
> Thanks
>
| |
|
| Hi
Thank you for information but i should use user-defined function.
please tell me how do this within user-defined function not in stored
procedure.
Thanks
Mehdi
"Alejandro Mesa" wrote:
[color=darkred]
> Mehdi,
>
> You will need dynamic sql to accomplish this.
>
> create procedure dbo.p1
> @tn as sysname
> as
> set nocount on
>
> declare @sql nvarchar(500)
>
> set @sql = N'select * from ' + quotename(@tn)
>
> exec sp_executesql @sql
>
> return @@error
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
>
> AMB
>
> "Mehdi" wrote:
>
| |
| Tibor Karaszi 2005-09-29, 7:23 am |
| The things you can do in a user defined function are very limited. You cannot call another stored
procedure, use dynamic SQL , modify data (except for table variables defined inside the UDF), etc.
Either write one UDF for each table you expect to use from the UFD and call the proper UDF, or use a
stored procedure instead or post the purpose of these function and perhaps somebody can come up with
some other idea...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/
"Mehdi" <Mehdi@discussions.microsoft.com> wrote in message
news:A2551A20-1D95-4757-88C7- B9FE4BB65938@microso
ft.com...[color=darkred]
> Hi
> Thank you for information but i should use user-defined function.
> please tell me how do this within user-defined function not in stored
> procedure.
>
> Thanks
> Mehdi
>
> "Alejandro Mesa" wrote:
>
|
|
|
|