April 7, 2008 at 12:09 pm
I have a situation where I need to return the result of dynamic execution of execute () command to a variable.
Here is simplified case, (my actual dynamic script is completely different):
declare
@var1 smalldatetime,
@stmt varchar(max)
set @stmt = 'select getdate()'
execute(@stmt)
I need to assign return value to @var1.
I tried execute @var1 = (@stmt), select @var1 = execute(@stmt), etc, nothing works. Is it possible at all ?
Eventually I will want to create a function based on this script that will return that value.
Thanks
April 7, 2008 at 12:13 pm
sp_executeSQL can have output parameters. Look that one in Books Online. It should do exactly what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 12:30 pm
A user defined function cannot use dynamic SQL.
April 7, 2008 at 12:40 pm
That was my next question. In such a case I will need to find some other solution.
What I want to build is a function that would return object name by passing object_id and database_id, similar to existing object_name() but accross all databases in the server.
April 7, 2008 at 1:11 pm
Mark Shvarts (4/7/2008)
That was my next question. In such a case I will need to find some other solution.What I want to build is a function that would return object name by passing object_id and database_id, similar to existing object_name() but accross all databases in the server.
What's wrong with using Object_name itself? The object ID should return the name, even if it's in another database.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 1:26 pm
No, it returns object name only in the current database context.
April 7, 2008 at 2:07 pm
Mark Shvarts (4/7/2008)
No, it returns object name only in the current database context.
Hmm...don't know what I was looking at earlier...nevermind.
Here's a SP that would do the trick. Publish is to master:
create proc sp_findObj(@dbID int, @objid Int, @objname varchar(5000) OUTPUT)
as
Begin
declare @sql nvarchar(4000)
declare @parms nvarchar(500)
declare @dbname varchar(100)
set @dbname=db_name(@dbid);
declare @return varchar(250)
set @sql='use ['+ @dbname + ']; select @returnout='''
+ @dbname +'..'+ '''+[name] from sys.all_objects where object_id='
+ cast(@objid as varchar(15))
set @parms='@returnout varchar(250) OUTPUT';
exec sp_executeSQL @sql,@parms,@returnout=@return OUTPUT;
set @objname = @return
end
You can then call this way:
declare @obj nvarchar(250)
exec sp_findObj @dbid=12, @objid=52911260, @objname=@obj Output
select @obj
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 3:11 pm
That procedure is great, but I need a function.
Particularly, for my performance testing I am selecting metrics from sys.dm_exec_query_stats by cross applying it to sys.dm_exec_sql_text, which returns database and object ids. It's running accross all databases on the server. Instead of just numbers I would like to get actual object names. I can use cursor to call stored procedure for evey record, but to have a function would be better solution, I think.
April 7, 2008 at 3:45 pm
Mark Shvarts (4/7/2008)
That procedure is great, but I need a function.Particularly, for my performance testing I am selecting metrics from sys.dm_exec_query_stats by cross applying it to sys.dm_exec_sql_text, which returns database and object ids. It's running accross all databases on the server. Instead of just numbers I would like to get actual object names. I can use cursor to call stored procedure for evey record, but to have a function would be better solution, I think.
You can create a CLR function to do it. The performance won't be great, but it would work, especially if you can save the function one round trip by deriving the DB name from sys.databases.
here's the "guts" of the CLR function:
Public Shared Function ObjNameCLR(ByVal DBname As SqlString, ByVal Tblid As SqlInt64) As SqlString
Dim tmp As SqlString
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("select '" + DBname.ToString + "..'+[name] from " + DBname.ToString + ".sys.all_objects where object_id='" + Tblid.ToString + "'", connection)
tmp = CType(command.ExecuteScalar(), String)
connection.Close()
End Using
Return tmp
end function
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 3:55 pm
So you are using Visual Basic to build dynamic function! I've never done it before, but at least it will be a reason to start using CLR.
I also have another idea (I have not tried it either). First is to build a temp table with all db and object ids selected in a cursor looping sys.databases. Then, join sys.dm_exec_sql_text to it.
April 7, 2008 at 4:43 pm
Mark Shvarts (4/7/2008)
So you are using Visual Basic to build dynamic function! I've never done it before, but at least it will be a reason to start using CLR.I also have another idea (I have not tried it either). First is to build a temp table with all db and object ids selected in a cursor looping sys.databases. Then, join sys.dm_exec_sql_text to it.
Considering it's a function, and a CLR function on top of it all, this would be something I would use in small doses. This will create a row-by-row process which won't scale real well.
For something high-volume - I would actually create a temporary table (or a table that gets rebuilt on a regular basis) with this kind of info in it. You could insert the databaseID, objectID and name columns from the various sys.all_objects in each of your databases and use that as your reference.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 8:46 pm
Forgive me for doing just a quick scan of the threads... couldn't you use the Information_Schema views for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2008 at 8:21 am
But information_schema is in each database context, while I need something on a server level. Besides, information_schema does not have a objects view.
April 8, 2008 at 6:05 pm
There's a bunch of "sys.All_" views that are "server wide".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2008 at 7:15 pm
Jeff - which ones? sys.all_objects is context-sensitive, as is all_columns, all_views, syscomments, sysobjects. I haven't tested them all - but I have yet to find one that shows all objects in every DB.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply