April 15, 2016 at 4:16 am
/*Hi this is only a litte piece of the code. We are talking about an .net (C#) app that generate a huge amount of sql code to run in other environments
Because of that i need something as simple as possible, thank you guys*/
--QUERY 1 - works fine:
select [ABSENCETYPEID],
(select TOP 1 LABEL FROM OSUSR_cyw_TED_EVENT_TYPE where id = 2) as label
from OSUSR_cyw_TED_EVENTS
--but because the environment could be diferent I need to catck the fisical name dynamical
--QUERY 2 - works fine:
declare @TablePhisicalName nvarchar(2000)
select @TablePhisicalName = (SELECT 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)
EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = 2')
--HERE I HAVE THE PROBLEM. I need to use the EXEC result as a select attribute. Put the QUERY 2 inside the QUERY one
--I'm doing exeperiences and in my Vision 🙂 it is something like this:
declare @TablePhisicalName nvarchar(2000)
select @TablePhisicalName = (SELECT 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)
select [ABSENCETYPEID],
EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = 2') as test
from OSUSR_cyw_TED_EVENTS
--I was thinking is something like this too to put the EXEC result in a string that I declared before and use it as a select attribute
Can anyone show me the way? I need ideas... Thank you
April 15, 2016 at 4:46 am
Use a temp table, table variable or staging table and insert into it thus:
INSERT INTO temp_table_or_table_variable_or_staging_table
EXEC ('<whatever>')
You can then select from the temp table, table variable or staging table as required.
John
April 15, 2016 at 5:02 am
WOW... good idea, for my example it worked! Thank you John, I will try to apply It to my app.
Kind regards!
declare @TablePhisicalName nvarchar(2000)
CREATE TABLE #temp_table (
test nvarchar(2000))
select @TablePhisicalName = (SELECT 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)
INSERT INTO #temp_table
EXEC('select TOP 1 LABEl from ' + @TablePhisicalName + ' where [id] = 2')
select [ABSENCETYPEID],
(select TOP 1 test from #temp_table)
from OSUSR_cyw_TED_EVENTS
drop table #temp_table
April 15, 2016 at 5:11 am
You're welcome. You do know you shouldn't use TOP without an ORDER BY, don't you? You're risking getting unpredictable results. If, as I suspect, your query only returns one row anyway (without the TOP), then you don't need TOP at all and you may as well leave it out.
John
April 15, 2016 at 6:36 am
Thank you for the observation and feedback.
Regards,
Nuno Oliveira
April 18, 2016 at 8:56 am
Hi again I'm having really difficult to do this, if anyone could help me would be fine
I'm completely lost and without ideas. What I need is simply but I need to apply in a complex query generated by .NET. The Idea that I need here:
In the SQL below we have 2 different tables that I need to get the correct name on the environment that I will execute the SQL
The select is over this table [OSUSR_CYW_TED_EVENTS] but I need to get an attribute going to another table OSUSR_CYW_TED_EVENT_TYPE this attribute as you can see on the SQL below [EVENTTYPEID]
1 -In my first idea I was getting the correct table name and put the result in a variable and try to apply directly in the query below
Something like this:
declare @TablePhisicalName nvarchar(2000)
select @TablePhisicalName = (SELECT physical_table_name FROM ossys_entity en WHERE en.name = 'EVENTS')
INSERT INTO #temp_tableGetLabel EXEC('select TOP 1 LABEl from ' + @TablePhisicalNameDestiny + ' where [id] = 2 ORDER BY 1 ')
The problem here is that I need to get the ID from the principal query to join the second table: where [id] = 2 this value is hard coded and it will be to join like this:
where OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE.[Id] = OSUSR_CYW_TED_EVENTS.[EVENTTYPEID])
2 - I thought work with functions but no luck:
CREATE FUNCTION GetLabel (@id INT)
RETURNS table
AS
BEGIN
INSERT INTO #temp_tableGetLabel EXEC('select TOP 1 LABEl from ' + @TablePhisicalNameDestiny + ' where [id] = ' + @id + ' ORDER BY 1 ')
END
RETURN(select * from #temp_tableGetLabel)
It is no possible to Insert into in a function!
I feel that I'm having difficult in an easiest thing because I will need to get the the phisical name from the principal query, last line of code that involve all query. EXEC command not seems to me suitable to do this
I will need to get the Phisical name for the second table too, something close to the first
Are you seeing a simple way to do this? It is possible? I'm sorry but I don't have T-SQL experience and as you can see no vision, I need someone to show me the way 🙂
SELECT OSUSR_CYW_TED_EVENTS.[ABSENCETYPEID],
OSUSR_CYW_TED_EVENTS.[AMOUNT],
OSUSR_CYW_TED_EVENTS.[AMOUNTEXPENSE],
OSUSR_CYW_TED_EVENTS.[APPROVEDON],
OSUSR_CYW_TED_EVENTS.[APPROVERUSERID],
OSUSR_CYW_TED_EVENTS.[COLABORATORUSERID],
OSUSR_CYW_TED_EVENTS.[CREATEDAT],
OSUSR_CYW_TED_EVENTS.[CREATEDUSERID],
OSUSR_CYW_TED_EVENTS.[DESCRIPTION],
OSUSR_CYW_TED_EVENTS.[ENGAGEMENTID],
OSUSR_CYW_TED_EVENTS.[EVENTDATE],
OSUSR_CYW_TED_EVENTS.[EVENTSTATEID],
'( select ID from OSAmber.DBO.OSUSR_CYW_TED_EVENT_TYPE where LABEL = ??' + '' +
(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]) + '' + '??)'
as [EVENTTYPEID],
OSUSR_CYW_TED_EVENTS.[EXPENSETYPEID],
OSUSR_CYW_TED_EVENTS.[ID],
OSUSR_CYW_TED_EVENTS.[ISACTIVE],
OSUSR_CYW_TED_EVENTS.[ISRESCHEDULE],
OSUSR_CYW_TED_EVENTS.[LASTUPDATEDAT],
OSUSR_CYW_TED_EVENTS.[LASTUPDATEDUSERID],
OSUSR_CYW_TED_EVENTS.[PERIOD],
OSUSR_CYW_TED_EVENTS.[RESPONSIBLEUSERID],
OSUSR_CYW_TED_EVENTS.[SUBMITEXPENSESID],
OSUSR_CYW_TED_EVENTS.[SUBMITEXTRAPREVENTIONID]
,OSUSR_CYW_TED_EVENTS.[SUBMITHOURSID],
OSUSR_CYW_TED_EVENTS.[SUBMITMILAGEID],
OSUSR_CYW_TED_EVENTS.[SUBMITVACATIONSID],
OSUSR_CYW_TED_EVENTS.[TIMEEND],
OSUSR_CYW_TED_EVENTS.[TIMESTART]
FROM [OSAmber].dbo.[OSUSR_CYW_TED_EVENTS]
Thank you a loot for your attention
Kind regards,
Nuno Oliveira
April 19, 2016 at 5:12 am
Hi,
I open a new topic for this:
How to build a dynamical Query with T-SQL
http://www.sqlservercentral.com/Forums/Topic1778997-3077-1.aspx
Thank you,
Regards,
Nuno Oliveira
April 21, 2016 at 9:36 am
First, you can make life easier for yourself by using aliases.
It is already bad enough that all your column names are in ALL CAPS (shouting ?)*** you can at least improve legibility by taking the l-o-o-o-o-n-g table name out of the equation.
SELECT OSUSR_CYW_TED_EVENTS.[ABSENCETYPEID],
OSUSR_CYW_TED_EVENTS.[AMOUNT],
OSUSR_CYW_TED_EVENTS.[AMOUNTEXPENSE],
[snip]
FROM [OSAmber].dbo.[OSUSR_CYW_TED_EVENTS]
Which then becomes
SELECT
e.[ABSENCETYPEID],
e.[AMOUNT],
e.[AMOUNTEXPENSE],
[snip]
FROM [OSAmber].dbo.[OSUSR_CYW_TED_EVENTS] e
Isn't this less attention-straining now ?
This goes double if you are selecting columns from different tables in the same select statement. You only need to look at one letter to know where each column is coming from.
SELECT
e.[ABSENCETYPEID],
e.[AMOUNT],
e.[AMOUNTEXPENSE],
p.[LONGEMPLOYEENAME]
[snip]
FROM [OSAmber].dbo.[OSUSR_CYW_TED_EVENTS] e
INNER JOIN [OSAmber].dbo.[OSUSR_CYW_EMPLOYEE_PERSONAL_INFORMATION_TABLE] p
ON p.[EMPLOYEEPRIMARYKEYCOLUMN] = e.[IDENTIFICATIONOFEMPLOYEEABSENTFROMWORKAGAIN
*** At least you can still consider yourself lucky you do not have to live with big-name company which uses an opaque table / column naming scheme what replaces meaningful names with codes like "AB47Z" instead of "Amount".
And in the future, please use the [font="Courier New"]IF Code[/font] button on top of the edit window and select "[font="Courier New"]Specified SQL Code[/font]" - this will go a long way to cleaning up the presentation of T-SQL code you post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply