Friday, March 9, 2012

Function inside a store procedure

Is it possible to create a function inside a store procedure use it and at
the end of the procedure drop the function'
Just curious if somebody has made it !!Marco A. Pi?a wrote:
> Is it possible to create a function inside a store procedure use it
> and at the end of the procedure drop the function'
> Just curious if somebody has made it !!
You could...
Create Proc CreateExecDropFunc
as
Begin
Declare @.SQL nvarchar(4000)
Declare @.FuncName nvarchar(36)
Declare @.TestInt int
Set @.FuncName = CAST(NEWID() as nvarchar(36))
-- Watch for line breaks on next line
Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
Exec sp_executesql @.SQL
Print @.SQL
Set @.TestInt = 1
Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
Print @.SQL
Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
Exec sp_executesql @.SQL
End
Go
Exec CreateExecDropFunc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Excellent, very usefull !!!
"David Gugick" wrote:

> Marco A. Pi?a wrote:
> You could...
> Create Proc CreateExecDropFunc
> as
> Begin
> Declare @.SQL nvarchar(4000)
> Declare @.FuncName nvarchar(36)
> Declare @.TestInt int
> Set @.FuncName = CAST(NEWID() as nvarchar(36))
> -- Watch for line breaks on next line
> Set @.SQL = N'Create Function [dbo].[' + @.FuncName + N'] (@.Param INT)
> Returns INT as Begin Set @.Param = @.Param + 1 Return @.Param End'
> Exec sp_executesql @.SQL
> Print @.SQL
> Set @.TestInt = 1
> Set @.SQL = 'Select [dbo].[' + @.FuncName + N'](@.Param)'
> Print @.SQL
> Exec sp_executesql @.SQL, N'@.Param INT', @.TestInt
> Set @.SQL = N'Drop Function [dbo].[' + @.FuncName + N']'
> Exec sp_executesql @.SQL
> End
> Go
> Exec CreateExecDropFunc
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

No comments:

Post a Comment