January 27, 2009 at 11:05 pm
Can't we use execute statement in functions?????
If yes then how???????
plz provide example too.......
i have created a function and due to some reason i have to store the sql statement in a variable to execute the statement.....
But sql says invalid use of EXECUTE in function.......
****************--------*********************
My second question is : How can i store a value returned from execute(tsql) in a variable.
Ex:------------- -------------->>>>>>>>
declare @Sal decimal (16,2)
declare @tsql varchar(4000)
set @tsql = "select " + @Sal + " = salary from employee"
exec (@tsql)
-------------------------------
i need the returned salary in the variable @Sal, so that i can further manipulate it.
I dont want to create a physical table or to store the returned value in a temp table from executing the sql variable.
If it is possible in procedure then also fine. But with no use of table/temp table
Thanks
January 27, 2009 at 11:30 pm
smeet.sinha (1/27/2009)
Can't we use execute statement in functions?????
No, you can't.
My second question is : How can i store a value returned from execute(tsql) in a variable.
Look up sp_executesql. It allows passing both input and output parameters. There are some good examples in Books Online.
That said, your example doesn't need dynamic SQL at all. Is the real situation more complex?
declare @Sal decimal (16,2)
select @Sal = salary from employee -- which salary are you getting here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2009 at 2:53 am
Sandy (1/28/2009)
smeet.seena,Please check this.
declare @Sal decimal (16,2), @tsql varchar(4000)
set @tsql = 'select @Sal = salary from employee'
EXEC Sp_Executesql @tql, N'@sal decimal (16,2) OUTPUT',
@Sal=@sal output
select @Sal
Cheers!
Sandy.
This also won’t work in a user defined function. You can not activate a stored procedure from a function.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2009 at 8:22 am
Thanks guys.....
The salary example is not the real situation.
But yes we cant avoid dynamic sql as i'm taking the database instance name from another function.
like :-
select @val = someval from server1.dbname.dbo.tbl_one
to get the value "server1" we are using a function
dbo.getserver('ver1')
thats y we are using dynamic sql, n making it like
set @var = 'select @val = someval from ' + dbo.getserver('ver1') + '.dbname.dbo.tbl_one'
And i was thinking of using that function directly in other procedure to get the retun value. As this particular value i need atleast in 30 queries.
------------
But one thing is sure that we cant use exec or sp_executesql statement in UDF.
Y Microsoft is not working on this issue. I can understand that sp_executesql is a procedure and we cant execute a proc within the UDF. But they can introduce some other way so that user can use dynamic tsql in UDF too, if it is really no other way.
thanks
January 28, 2009 at 8:36 am
You can execute extended stored procedure, but I wouldn't go that path. I would redesign the process.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2009 at 8:49 am
Hey Gail, Sandy
Suppose this is the case......... [See Below the example]
I would like to use this salary value returned from the function where ever it is required , right now more than 30 queries where i need this returned value.......
create function fn_test1 (@empid varchar(200))
returns decimal(18,2)
as
begin
declare @Sal decimal(18,2)
declare @sql nvarchar(500)
set @sql = 'select @Sal = salary from ' + dbo.fn_getserver('ver1') + ' dbmain.dbo.student where name = @empid'
exec sp_executesql @sql, N'@sal decimal(18,2) out, @empid int', @Sal out, @empid
return (@sal)
-------
-------
----/* suppose there are lots of manipulation statement with the @salary variable. */
-------
------
end
But i can't do it in a function........ as Microsoft doesn't allow to use dynamic query in UDF..... Is that so.
Don't u think that would be great if Microsoft would have considered this.
What are the underlying issues with the MS SQL architecture i dont know. But that would be better if we can use Dy sql in UDF....
Any Suggestions / comments ...... Any one??????
January 28, 2009 at 8:52 am
Hi Adi
ur comments ,,,, if any?
Thanks
smeet
January 28, 2009 at 9:08 am
Ya Adi
we can execute extended procs in UDF.
But anyhow i dont think we can make any such XP to execute dynamicn sql.
So mean to say that i have to drop the idea to make a common function to return the value, which we can use anywhere. In the above given senario.
In the given senario if i dont use the function to get the server name then there wont be any problem in my query because then i dont need to use dynamic sql.
The thing is that we have diff servers for Diff Exchange. Thats y we have made a function to get the server name. As exchange server is not ours they can change their instance name any time and just update the table where they update the current server name.
Thanks
Smeet
January 28, 2009 at 10:10 am
One way that I can think about is to create a temporary table, then use a stored procedure that works with the linked servers. It can connect to all the linked server with dynamic SQL and each time inserts the data into the temporary table. After you finish gathering the data in the temporary table, you can activate your function and set it to work with the temporary table instead of the linked servers.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2009 at 1:13 pm
smeet.sinha (1/28/2009)
Don't u think that would be great if Microsoft would have considered this.What are the underlying issues with the MS SQL architecture i dont know. But that would be better if we can use Dy sql in UDF....
It's not that MS didn't think that maybe someone would want to do that. There is an architecture restriction. Functions may not have side-effects of any form. They may not modify data, they may not change schema. That restriction is there so that the query optimiser doesn't have to worry about how many times a function will be run during a query.
The restriction against side effects is easy to check with static code, near-impossible with dynamic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply