April 20, 2016 at 7:25 am
First off, kudos to you for going off and working out for yourself how sp_executesql works. Many posters on these forums would have just expected us to provide the finished code for them, so well done!
Now then, I don't think I understand what you're trying to do. Do you need to get a value from the EVENTTYPEID column into the @Label variable? If you do, bear in mind that variables (except table variables) are scalar, so what will you do in the case where the query returns more than one row?
Edit - I posted this before reading your last post. If you execute that string with sp_executesql, it will return a result set, so if that's all you require, there's no need for the temp table. If you need to manipulate the results further, then yes, you may need to stage them in a temp table.
John
April 20, 2016 at 8:04 am
John I'm working and my boss is just waiting for this. This is a simple problem because after that I need to rebuild the .NET code to build all this T-SQL dynamically with the tables and attributes that the user choose on the app.
I neither have .NET and T-SQL relevant experience, I'm an Outsystems developer.
Because this I appreciate your help because showing me the way I will save time in my analyses and I'm learning with you, and this is important. It's always good to have experiences with T-SQL, PL-SQL so this is a good investment for me too.
In my experience normally all the logic is done in Outsystems or .NET, here is different because we are talking about a .NET application that generate SQL to be consumed on other environments.
I will get to you when I need... Thank you
April 20, 2016 at 9:11 am
I'm ready to go to the .NET now...
Thank you John
This peace of my generated SQL
SELECT
OSUSR_CYW_TED_EVENTS.[AMOUNT],
(SELECT MIN(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.[SUBMITVACATIONSID],
OSUSR_CYW_TED_EVENTS.[TIMEEND]
FROM OSUSR_CYW_TED_EVENTS
Is the same as:
DECLARE @SQLStringGetTablename nvarchar(500);
DECLARE @SQLStringGetTablenameOrigin nvarchar(500);
DECLARE @SQLGetResult nvarchar(500);
DECLARE @tableName nvarchar(500);
DECLARE @tableNameOrigin nvarchar(500);
DECLARE @ParmDefinitionTableName nvarchar(500);
DECLARE @ParmDefinitionTableNameOrigin nvarchar(500);
CREATE TABLE #tempTable (AMOUNT nvarchar(500), EVENTTYPEID nvarchar(500), SUBMITVACATIONSID nvarchar(500), TIMEEND nvarchar(500));
--query to catch the phisical name
SET @SQLStringGetTablenameOrigin = N'SELECT @tableNameOriginOUT = 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 @ParmDefinitionTableNameOrigin = N'@tableNameOriginOUT varchar(30) OUTPUT';
EXECUTE sp_executesql @SQLStringGetTablenameOrigin, @ParmDefinitionTableNameOrigin, @tableNameOriginOUT=@tableNameOrigin OUTPUT;
--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;
SET @SQLGetResult = N'INSERT INTO #tempTable
SELECT
' + @tableNameOrigin + '.[AMOUNT],
(SELECT MIN(label)
FROM ' + @tablename + '
WHERE OSAmber.DBO.' + @tablename + '.[Id] = ' + @tableNameOrigin + '.[EVENTTYPEID])
AS [EVENTTYPEID],
' + @tableNameOrigin + '.[SUBMITVACATIONSID],
' + @tableNameOrigin + '.[TIMEEND]
FROM ' + @tableNameOrigin;
EXECUTE sp_executesql @SQLGetResult
select * from #tempTable
April 21, 2016 at 9:48 am
delete post
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply