April 19, 2016 at 5:10 am
I will simulate the problem that I’m having on this topic:
http://www.sqlservercentral.com/Forums/Topic1778097-8-1.aspx?Update=1
To a simple way to explain
select object_id from sys.tables
where name in ('OSUSR_CYW_TED_EVENTS', 'OSUSR_CYW_TED_EVENT_TYPE')
2 tables | Object ID
- OSUSR_CYW_TED_EVENTS | 791061954
- OSUSR_CYW_TED_EVENT_TYPE | 183059788
Imagine now that I only have the object_ID and I need to do the query below. How to do that?
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT TOP 1 LABEL
FROM OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE
WHERE OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE.[Id] = OSUSR_CYW_TED_EVENTS.[EVENTTYPEID] order by 1)
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
I know exactly the structure of the tables OSUSR_CYW_TED_EVENTS and OSUSR_CYW_TED_EVENT_TYPE but on that environment I don’t have the names (they are different) and I only have the Object ID and I need to catch them from the Database and use them dynamically on the query above
Hope to get ideas from you...
Thank you,
Regards,
Nuno Oliveira
April 19, 2016 at 5:16 am
Nuno
You can either join to sys.objects to get the table name, or you can use the OBJECT_ID function. To do the latter, you must run the query in the context of the database that contains the table.
John
April 19, 2016 at 6:43 am
Given an Object_Id, you can use Object_Name() to get the object's name.
To get the schema related to the Object_Id, use Object_Schema_Name().
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 19, 2016 at 6:44 am
Hi Jonh. Thank you again for trying to help me. You was the one that helped me in the other topic:
http://www.sqlservercentral.com/Forums/Topic1778097-8-1.aspx#bm1778113
I was trying to use variables and functions and EXEC commands to to this
So looking for this example that you said:
“You can either join to sys.objects to get the table name, or you can use the OBJECT_ID function. To do the latter, you must run the query in the context of the database that contains the table.”
As you can see the sys.tables it was only an example to an easiest comprehension because we are talking about an Outsystems configuration table that match the SQL Server table name with the Outsystems table name. So the function OBJECT_ID doesn’t help us.
The focus is here: “join to sys.objects to get the table name”
Ok and how to do this query doing that joint that you are talking about? Having de T1 object ID and T2 object ID
SELECT
T1.[AMOUNT],
(SELECT TOP 1 LABEL
FROM T2
WHERE T2.[Id] = T1.[EVENTTYPEID] order by 1)
AS [EVENTTYPEID]
FROM T1
2 tables | Object ID
T1- OSUSR_CYW_TED_EVENTS | 791061954
T2- OSUSR_CYW_TED_EVENT_TYPE | 183059788
It’s seems so easy that hearts to lose so much time with this
imagine T1 = SELECT name
FROM sys.objects
WHERE OBJECT_ID = 791061954
T2 = SELECT name
FROM sys.objects
WHERE OBJECT_ID = 183059788
April 19, 2016 at 7:37 am
Hi Phill,
Thanks for your feedback.
I think I only can use Object_Name() in a Select statement right?
Here in my example I need to use in FROM and WHERE statements
SELECT OSUSR_CYW_TED_EVENTS.[AMOUNT],
(select TOP 1 LABEL from Object_Name(791061954)
where Object_Name(791061954).[Id] = Object_Name(183059788).[EVENTTYPEID])
as [EVENTTYPEID]
FROM Object_Name(183059788)
Guys do you think it's possible to to what I'm trying to do in T-SQL?
Thanks for your attention!
Kind regards,
Nuno Oliveira
April 19, 2016 at 7:43 am
Nuno
If you don't know at design time which table you're going to be querying, you need to use dynamic SQL, where you build a statement to execute and then execute it using EXEC or sp_executesql.
John
April 19, 2016 at 7:48 am
nuno.fbo (4/19/2016)
Hi Phill,Thanks for your feedback.
I think I only can use Object_Name() in a Select statement right?
Here in my example I need to use in FROM and WHERE statements
SELECT OSUSR_CYW_TED_EVENTS.[AMOUNT],
(select TOP 1 LABEL from Object_Name(791061954)
where Object_Name(791061954).[Id] = Object_Name(183059788).[EVENTTYPEID])
as [EVENTTYPEID]
FROM Object_Name(183059788)
Guys do you think it's possible to to what I'm trying to do in T-SQL?
Thanks for your attention!
Kind regards,
Nuno Oliveira
You need dynamic SQL for this, as stated by John.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 19, 2016 at 8:27 am
Ok John,
The target with this new topic is to be sure that as you said: "you need to use dynamic SQL"
So what I want to do is this dynamically:
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT TOP 1 LABEL
FROM OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE
WHERE OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE.[Id] = OSUSR_CYW_TED_EVENTS.[EVENTTYPEID] order by 1)
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
This works but my [id] = 2 is hard coded I need to find a way to link
declare @TablePhisicalName nvarchar(2000)
CREATE TABLE #temp_tableGetLabel (EXECResultGetLabel nvarchar(2000))
select @TablePhisicalName = (SELECT name FROM sys.objects WHERE OBJECT_ID = 791061954)
INSERT INTO #temp_tableGetLabel EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = 2 ORDER BY 1 ')
First step: This works but my [id] = 2 is hard coded I need to find a way to link my EXEC command with the main SELECT, for now we will work with this table OSUSR_CYW_TED_EVENTS. We'll just focus only at the attribute EVENTTYPEID, on this case the dynamic table will be only T2
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(select EXECResultGetLabel from #temp_tableGetLabel)
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
I need to create something like a function that do this:
EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = ' + @id + ' ORDER BY 1 ')
I don't know how to do that, because to use EXEC I need to INSERT the value to a temp table and in a FUNCTION I'm not able to use INSERT
CREATE FUNCTION GetLabel (@id INT)
RETURNS table
AS
BEGIN
INSERT INTO #temp_tableGetLabel EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = ' + @id + ' ORDER BY 1 ')
END
RETURN(select * from #temp_tableGetLabel)
April 19, 2016 at 8:43 am
Instead of using EXEC, try sp_executesql, with an output parameter.
John
April 19, 2016 at 8:48 am
Yes John, I was looking for this now to understand how it works:
https://msdn.microsoft.com/en-us/library/ms188001.aspx
Thanks
April 19, 2016 at 9:02 am
Incidentally, consider making your code easier to read and maintain by:
(1) Avoiding overuse of subqueries
(2) Using consistent capitalisation
(3) Adding line breaks
(4) Aliasing tables
(5) Presenting it using the code tags on the left of your screen
Here are a few examples:
SELECT
e.AMOUNT
,MAX(t.LABEL) AS EVENTTYPEID -- why do you want to call this column EVENTTYPEID?
FROM OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE t
JOIN OSUSR_CYW_TED_EVENTS e ON t.ID = e.EVENTTYPEID
GROUP BY e.AMOUNT
SELECT @TablePhisicalName = name
FROM sys.objects
WHERE OBJECT_ID = 791061954
SELECT
e.AMOUNT
,t.EXECResultGetLabel AS EVENTTYPEID -- why do you want to call this column EVENTTYPEID?
FROM OSUSR_CYW_TED_EVENTS e
CROSS JOIN #temp_tableGetLabel t
John
April 19, 2016 at 11:47 am
Thanks John for the advise.
As I said you this in only a short peace od SQL code we are talking about SQL code generated by a .net application and executed automatically.
But for your help is good to show you the code more readable I understand.
Because you are professional and fluent on this you are used to follow and see good T-SQL code (best practises). But as a beginner I ask you your comprehension.
In your last post I got that we are able to right in SQL format here which is really good.
I really want to thank you a lot your help and your patience.
I need more help
I'm really stuck. No I understand how is easy to work with parameters, but I'm not seeing how I can catch the physical name and apply in the simple example below:
DECLARE @SQLStringGetTablename nvarchar(500);
DECLARE @tableName nvarchar(500);
DECLARE @ParmDefinitionTableName nvarchar(500);
--get the phisical name and put it in the @tableNameOUT variable
SET @SQLStringGetTablename = N'SELECT @tableNameOUT = physical_table_name FROM ossys_entity en INNER JOIN ossys_espace es on es.id = en.espace_id WHERE en.name = ''EVENTS'' AND es.IS_ACTIVE = 1 AND en.IS_ACTIVE = 1'
SET @ParmDefinitionTableName = N'@tableNameOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLStringGetTablename, @ParmDefinitionTableName, @tableNameOUT=@tableName OUTPUT;
--it works fine
SELECT *
from ossys_entity
WHERE physical_table_name = @tableName
--this is my target and doesn't work
SELECT *
from @tableName
Doesn't work because he is expecting a table but this table is dynamic.
If I declare a table above what I need to insert it I don't know because I don't have the table name yet
Thanks a lot
April 19, 2016 at 12:01 pm
nuno.fbo (4/19/2016)
Thanks John for the advise.As I said you this in only a short peace od SQL code we are talking about SQL code generated by a .net application and executed automatically.
But for your help is good to show you the code more readable I understand.
Because you are professional and fluent on this you are used to follow and see good T-SQL code (best practises). But as a beginner I ask you your comprehension.
In your last post I got that we are able to right in SQL format here which is really good.
I really want to thank you a lot your help and your patience.
I need more help
I'm really stuck. No I understand how is easy to work with parameters, but I'm not seeing how I can catch the physical name and apply in the simple example below:
DECLARE @SQLStringGetTablename nvarchar(500);
DECLARE @tableName nvarchar(500);
DECLARE @ParmDefinitionTableName nvarchar(500);
--get the phisical name and put it in the @tableNameOUT variable
SET @SQLStringGetTablename = N'SELECT @tableNameOUT = physical_table_name FROM ossys_entity en INNER JOIN ossys_espace es on es.id = en.espace_id WHERE en.name = ''EVENTS'' AND es.IS_ACTIVE = 1 AND en.IS_ACTIVE = 1'
SET @ParmDefinitionTableName = N'@tableNameOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLStringGetTablename, @ParmDefinitionTableName, @tableNameOUT=@tableName OUTPUT;
--it works fine
SELECT *
from ossys_entity
WHERE physical_table_name = @tableName
--this is my target and doesn't work
SELECT *
from @tableName
Doesn't work because he is expecting a table but this table is dynamic.
If I declare a table above what I need to insert it I don't know because I don't have the table name yet
Thanks a lot
You are almost there. You cannot use a variable in a query at that location, so if you make that last query into another dynamic SQL as above it will work.
EXECUTE sp_executesql 'SELECT * from' + @tablename
April 20, 2016 at 7:03 am
Amazing...
Step by step I'm getting there... I don't know how I will pay you for your help but If you came to Lisbon I definitely will pay you a lunch
If you cn help me now I need ideas to do this:
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT @label) -- I need to find a way to send @LabelId as OSUSR_CYW_TED_EVENTS.[EVENTTYPEID] to this execution
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
Here we have the total code:
DECLARE @SQLStringGetTablename nvarchar(500);
DECLARE @SQLGetLabel nvarchar(500);
DECLARE @id varchar(30);
DECLARE @tableName nvarchar(500);
DECLARE @label nvarchar(500);
DECLARE @tempTable table (result nvarchar(500));
DECLARE @ParmDefinitionTableName nvarchar(500);
DECLARE @ParmDefinitionGetLabel nvarchar(500);
DECLARE @LabelId int;
--query to catch the phisical name
SET @SQLStringGetTablename = N'SELECT @tableNameOUT = physical_table_name FROM ossys_entity en INNER JOIN ossys_espace es on es.id = en.espace_id WHERE en.name = ''EVENT_TYPE'' AND es.IS_ACTIVE = 1 AND en.IS_ACTIVE = 1'
SET @ParmDefinitionTableName = N'@tableNameOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLStringGetTablename, @ParmDefinitionTableName, @tableNameOUT=@tableName OUTPUT;
--put a test ID just to test the input parameter, works great, setting this
--SET @LabelId = 2;
SET @SQLGetLabel = N'SELECT @labelOUT = MIN(label) from ' + @tablename + ' where [id] = @id'
SET @ParmDefinitionGetLabel = N'@id tinyint, @labelOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLGetLabel, @ParmDefinitionGetLabel, @id=@LabelId, @labelOUT=@label OUTPUT;
--(SELECT @label) -- Test was ok! using SET @LabelId = 2;
--I need ideas to do this
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT @label) -- I need to find a way to send @LabelId as OSUSR_CYW_TED_EVENTS.[EVENTTYPEID] to this execution
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
--Our target is to do this dynamically
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT TOP 1 LABEL
FROM OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE
WHERE OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE.[Id] = OSUSR_CYW_TED_EVENTS.[EVENTTYPEID] order by 1)
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS
April 20, 2016 at 7:16 am
I think I'm getting the logic
This is all about levels of execution, I think I need to do something like this:
SET @SQLGetLabel = N'SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT MIN(label)
FROM ' + @tablename + '
WHERE OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE.[Id] = OSUSR_CYW_TED_EVENTS.[EVENTTYPEID])
AS [EVENTTYPEID]
FROM OSUSR_CYW_TED_EVENTS';
And forward I will get this table OSUSR_CYW_TED_EVENTS like I got the first table, the only think here that It's different is that the Output of my final sp_executesql will be a list of results, not a nvarchar output
In a fast thinking maybe I need to insert that result into a temp table and do a select to get the final result...
Let me try...
Thanks
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply