Current Position:Home > Join Two EXEC statements

Join Two EXEC statements

Update:11-30Source: network consolidation
Advertisement
Hi Team,
I want to join two dynamic sql with a UNION in between
EXEC(@STRSQLNOArchive+' UNION' + @STRSQLArchive) 
But here it is throwing an error .How this can be done
SET @STRSQLNOArchive= 'Select columnname1 from table 1'
SET @STRSQLArchive= 'Select columnname2 from table 2'
Result should be like this
Select columnname1 from table 1
UNION
Select columnname2 from table 2
Thanks,
Divya

The Best Answer

Advertisement
this is the query i m using
Declare @FilterTable table
ColumnName varchar(50),
Value varchar(250),
Type varchar(50)
INSERT INTO  @FilterTable(ColumnName,Value,Type)
SELECT
[Table].[Column].value('(@Field)[1]','varchar(250)')  'ColumnName',
[Table].[Column].value('(@Filter)[1]','varchar(250)')  'Value',
[Table].[Column].value('(@Type) [1]','varchar(250)')  'Type'
 FROM @ParamListXML.nodes('/ROOT/Parameter')  [Table]([Column])
-- Select * from @FilterTable
DECLARE @Where varchar(4000);
declare @sql nvarchar(Max);
SELECT
@Where = CASE WHEN ISNULL(@Where,'') = '' THEN '' ELSE @Where + ' AND ' END +'v.'+ColumnName + ' LIKE ''' + Value + ''''
FROM
@FilterTable
WHERE
ISNULL(Value,'') <> ''
DECLARE @STRSQLNOArchive varchar(max)
DECLARE @STRSQLArchive varchar(max)
SET @STRSQLNOArchive = '
SELECT * from 
(SELECT Row_number()Over(Order By V.ProjectId) Rownum,
Colum1,
Colum2,
Colum3,
Colum4,
Colum5,
Colum6,
Colum7,
Colum8
FROM PLanInfoBackup v    
LEFT JOIN Arch_tableBKUP b2 ON v.RefBudgetId = b2.BudgetId    
LEFT JOIN Lookup_TableBKUP d ON b2.LevelDomainId = d.DomainId    
LEFT JOIN ShowBKUP ak ON v.TVSHowID = ak.TVShowID
LEFT JOIN dbo.Status (nolock) bs ON v.BudgetId = bs.BudgetId  
WHERE '+ @where +'
 AND((        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p WHERE v.CompanyId = p.CompanyId AND p.ShowId IS NULL AND p.UserId = '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p2 WHERE v.ShowId = p2.howId AND p2.SeasonId = 0 AND p2.UserId =  '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p3 WHERE v.ShowId = p3.TVShowId AND v.SeasonDId = p3.SeasonDId AND p3.UserId = '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p4 WHERE v.ProId = p4.ProId AND p4.UserId =  '+ CONVERT(varchar(10),@UserId) +' )        
 ) OR '+CONVERT(varchar(10),@UserId)+' = 0) 
 )SearchTest
  where   Rownum < 501 
order by GLCode,  LevelSort, Revision, Scenario '
  --PRINT @STRSQLNOArchive;
 -- EXEC(@STRSQLNOArchive)
 SET @STRSQLArchive = '
 SELECT * from 
( SELECT Row_number()Over(Order By V.ProjectId) Rownum,
Colum1,
Colum2,
Colum3,
Colum4,
Colum5,
Colum6,
Colum7,
Colum8
FROM PLanInfo v      
LEFT JOIN Arch_table b2 ON v.RefBudgetId = b2.BudgetId      
LEFT JOIN Lookup_Table d ON b2.LevelDomainId = d.DomainId      
LEFT JOIN Show ak ON v.TVSHowID = ak.TVShowID
LEFT JOIN dbo.tableStatus (nolock) bs ON v.BudgetId = bs.BudgetId 
WHERE '+ @where +'
AND((        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p WHERE v.CompanyId = p.CompanyId AND p.ShowId IS NULL AND p.UserId = '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p2 WHERE v.ShowId = p2.howId AND p2.SeasonId = 0 AND p2.UserId =  '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p3 WHERE v.ShowId = p3.TVShowId AND v.SeasonDId = p3.SeasonDId AND p3.UserId = '+ CONVERT(varchar(10),@UserId) +' ) OR        
 EXISTS (SELECT ''X'' FROM v_CurrentUser p4 WHERE v.ProId = p4.ProId AND p4.UserId =  '+ CONVERT(varchar(10),@UserId) +' )        
 ) OR '+CONVERT(varchar(10),@UserId)+' = 0) 
 )SearchTest
  where   Rownum < 501 
order by GLCode,  LevelSort, Revision, Scenario '
--set @sql = @STRSQLNOArchive +char(13)+' UNION '+char(13) + @STRSQLArchive
--print @sql
--Exec sp_executesql @sql
  --SELECT ColumnName,Value
  --From  @FilterTable --where columnname ='WBSProject'
  IF (select '1' from @FilterTable where ColumnName = 'IsArchived' and Value='0') >0
 EXEC(@STRSQLNOArchive)  
 ELSE      
BEGIN  
 --EXEC(@STRSQLArchive)   
 set @sql = @STRSQLNOArchive +char(13)+' UNION '+ char(13) + @STRSQLArchive
 Print @sql
 Exec sp_executesql @sql
-- PRint @STRSQLArchive  
--EXEC(@STRSQLNOArchive+ char(13)+' UNION' +char(13)+ @STRSQLArchive)     
END 
  • Join Two EXEC statements Update:11-30

    Hi Team, I want to join two dynamic sql with a UNION in between EXEC(@STRSQLNOArchive+' UNION' + @STRSQLArchive)  But here it is throwing an error .How this can be done SET @STRSQLNOArchive= 'Select columnname1 from table 1' SET @STRSQLArchive= 'Sele

  • EXEC SQL join in ABAP program Update:10-11

    Hi All, I am using EXEC SQL join in my ABAP report,but it is giving dump.could you please correct my following code? START-OF-SELECTION.   CONCATENATE '%' pa_match '%' INTO tp_match.   EXEC SQL.     OPEN dbcur FOR     SELECT  kunnr,name1, banks      

  • Bad exec plan when joining tables using primary keys together w/ Contains Update:11-30

    Hello all...this is something that confuzzles me.... When joining 2 tables, the exec plan shows that the domain index is first accessed, before checking if there is a record in the other table using a highly selective index. create table users (useri

  • Fast Refresh on Materialized View With Join Update:10-11

    Hi All, i have created following Materialized View Logs and MV on EMP and DEPT CREATE MATERIALIZED VIEW LOG ON DEPT WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON EMP WITH PRIMARY KEY,SEQUENCE INCLUDING NEW VALUES; CR

  • The some words in perfectly formatted messages I email are merging or joining together likethis or evenlikethis when they are recieved. How do I fix this? Update:10-11

    When I send emails that are perfectly formatted they are being somewhat truncated when they are received by whomever I'm sending them to. Words will be joined together throughout the email in combinations of two or three. I know this is happening bec

  • Dynamic SQL Joining between tables and Primary keys being configured within master tables Update:10-11

    Team , Thanks for your help in advance ! I'm looking out to code a dynamic SQL which should refer Master tables for table names and Primary keys and then Join for insertion into target tables . EG: INSERT INTO HUB.dbo.lp_order SELECT * FROM del.dbo.l

  • Exception while executing split join in ALSB Update:10-11

    Hi When i am executing splitjoin flow in ALSB i am getting below exception ####<Nov 29, 2010 5:02:17 AM EST> <Error> <BPEL Kernel> <gifsmrt> <AdminServer> <[ACTIVE] ExecuteThread: '8' for queue: 'weblogic.kernel.Default (s

  • E51 Kern-Exec 0 Erorr Update:11-30

    Hi to everyone! I upgraded my E51 to newest firmware (400.34.011) using nokia Nokia Software Updater and after that run in trouble with Kern-Exec 0 Error. Have absolutely no idea why and when it shows up. Mostly I get it when I just lock keypad. Rese

  • Poor query performance when joining CONTAINS to another table Update:11-30

    We just recently began evaluating Oracle Text for a search solution. We need to be able to search a table that can have over 20+ million rows. Each user may only have visibility to a tiny fraction of those rows. The goal is to have a single Oracle Te

  • How to join tables from different databases (DBLink/DB connection ) Update:11-30

    Hello, i have an issue and i hope you could help me to solve it. My problem is: I want to create native sql select which joins two tables from different DB (both of them are ORACLE, one of them internal, another one - external). I have found several