We help IT Professionals succeed at work.

The result of a qry as a wry

119 Views
Last Modified: 2020-04-30
I am using sql 2014....I have created a sql statement has the results...
This actually appears in the Results window

Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4

Is it possible to take this and create a stored procedure...that will actually execute this union?
Comment
Watch Question

Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Why not?

Create Procedure proc_name as
Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4
CERTIFIED EXPERT

Commented:
Do you mean this?
CREATE PROCEDURE dbo.YourProcedureName
AS
BEGIN
Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4
END

Open in new window

Calling the SP:
EXEC dbo.YourProcedureName

Of course, to use SELECT *  is not good coding practice.

Author

Commented:
hi guys...No, guess I wasn't exact enough in my request...

I don't want to have to retype the 'Select * from ....'

I want (in a perfect world)..results themselves to be already in the SP...basically all the select statements somehow as some sort of Dynamic SQL thing.. hopefully that makes sense
CERTIFIED EXPERT

Commented:
You don't need to create your own procedure. Store the query into a string variable and call the sp_ExecuteSQL with the variable as a parameter:
DECLARE @sql nvarchar(1000)
SET @sql = 
'Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4'

EXEC sp_ExecuteSQL @sql

Open in new window

Of course, use your own code to populate the @sql string value-

Author

Commented:
When i do as you suggest.. I get '

Subquery returned more than 1 value, This is not permitted when the subquery follows =,!=..
CERTIFIED EXPERT

Commented:
There is no subquery in above unions... Instead of   EXEC sp_ExecuteSQL @sql   place the command  

PRINT @sql

and post the result.

Also please tell what SQL Server version do you use:

SELECT @@version
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If all you are trying to do is keep from retyping common SQL commands, use script files.  Save the commands in a .sql file and execute them when necessary.  Then you have them forever and don't need to worry about procedures.

I'm not a SQL Server person so don't know the tools but it seems like SQLCMD can do this pretty easily and SSMS can if you enable SQLCMD commands.

Author

Commented:
I am not sure I am being clear enough....So I have included the RESULT that I need to incorporate.. It is truly a qry that gets me this Exact information.. I created a qry that really shows this below information.. but it may show more (Table5, Table6).. I need it be dynamic so I don't have re-run the underlying qry and then copy this result and run to get the true result from the below union


Select * from Table1 union all
Select * from Table2 union all
Select * from Table3 union all
Select * from Table4'
Screen-Shot-2020-04-21-at-7.38.38-AM.png

Author

Commented:
same result when I ran with Print @sql...

the version is 2014 (SP3-GDR)KB4505218)-12.0.6108.1 X64
CERTIFIED EXPERT

Commented:
Maybe if you post the query which produced the above result we can help better…
You nay try something like this:
DECLARE @sql nvarchar(max)
SET @sql = N''
SELECT @sql = @sql + ' ' + <<place the expression for Det column here>>
  FROM YourTable
PRINT @sql

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I believe the question is how to dynamically build and execute the union all query from a list of tables or tables in a schema.

That is outside my SQL Server abailites.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
--EXEC dbo.your_proc_name_here
GO
CREATE PROCEDURE dbo.your_proc_name_here
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%',
    @where nvarchar(4000) = NULL,
    @order_by nvarchar(200) = NULL
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SELECT @sql = STUFF(CAST((
    SELECT ' UNION ALL SELECT * FROM [' + name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
    FROM sys.tables
    WHERE name LIKE @table_prefix
    ORDER BY name
    FOR XML PATH(''), TYPE)
    AS nvarchar(max)), 1, 11, '')
IF @order_by <> ''
    SET @sql = @sql + ' ORDER BY ' + @order_by
PRINT @sql
EXEC(@sql)
/*end of proc*/
GO

Author

Commented:
Hi Scott,

I got this error when running the script
Msg 457, Level 16, State 1, Line 3
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in UNION ALL operator.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ouch.  That's a pain.  The easiest way out is to use separate UNION ALL statements, one for each collation.  It's interesting that such similarly named tables would have different collations.  Is that intentional?!

Author

Commented:
not sure if its intentional.. but it's what I inherited   :(

OK.. stupid question time

How can I "use separate UNION ALL statements, one for each collation"...not sure I follow that...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That's not easy to do, and even then there'd be no easy way to combine them.

Create and run this proc so we can see how many columns of each collation we're dealing with.

CREATE PROCEDURE dbo.your_proc_name_here_metadata
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%'
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SELECT @sql = '
    SELECT
        t.name AS table_name,
        c.collation_name AS column_collation_name,
        COUNT(*) AS column_count
    FROM sys.tables t
    INNER JOIN sys.columns c ON c.object_id = t.object_id AND
        c.collation_name IS NOT NULL
    WHERE t.name LIKE ''' + @table_prefix + '''
    GROUP BY
        t.name,
        c.collation_name
    '
PRINT @sql
EXEC(@sql)
/*end of proc*/
GO

Author

Commented:
here it is
Table_List.xlsx
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
For now there's only one table that's not Latin1_General_BIN.  So, for right this minute, you can use the code below (NOTE: this code only expects a single table to not match Latin1_General_BIN; won't work if 2+ tables have that).

You can set @exec_sql = 0 and @print_sql = 1 to show the final UNION ALL sql without actually running it (yet).

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER  PROCEDURE dbo.your_proc_name_here
    @table_prefix nvarchar(100) = 'MrpForecast[0-9][0-9]%',
    @exec_sql bit = 1,
    @print_sql tinyint = 1, /*1=final sql only; >1 all sql*/
    @where nvarchar(4000) = NULL,
    @order_by nvarchar(200) = NULL
AS
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)
SELECT @sql = STUFF(CAST((
    SELECT ' UNION ALL SELECT * FROM [' + name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
    FROM sys.tables t
    WHERE name LIKE @table_prefix AND
        EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
            c.collation_name = 'Latin1_General_BIN')
    ORDER BY name
    FOR XML PATH(''), TYPE)
    AS nvarchar(max)), 1, 11, '')
IF @print_sql > 1
    PRINT 'sql = ' + @sql
SELECT @sql2 = STUFF(CAST((
    SELECT ', [' + c.name + '] ' +
        CASE WHEN c.collation_name IS NULL THEN '' ELSE 'COLLATE Latin1_General_BIN ' END
    FROM sys.tables t
    INNER JOIN sys.columns c ON c.object_id = t.object_id
    WHERE t.name LIKE @table_prefix AND
        EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
            c.collation_name <> 'Latin1_General_BIN')
    ORDER BY t.name, c.column_id
    FOR XML PATH(''), TYPE
    ) AS nvarchar(max)), 1, 2, '')
IF @print_sql > 1
    PRINT '@sql2 = ' + @sql2
SELECT @sql2 = CASE WHEN @sql IS NULL THEN '' ELSE ' UNION ALL ' END +
    'SELECT ' + @sql2 + 'FROM [' + t.name + '] ' +
        CASE WHEN @where = '' OR @where IS NULL THEN ''
             ELSE 'WHERE ' + @where END
FROM sys.tables t
WHERE t.name LIKE @table_prefix AND
    EXISTS(SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND
        c.collation_name <> 'Latin1_General_BIN')
SET @sql = ISNULL(@sql, '') + ' ' + ISNULL(@sql2, '')
IF @order_by <> ''
    SET @sql = @sql + ' ORDER BY ' + @order_by
IF @print_sql > 0
    PRINT '@sql = ' + @sql
IF @exec_sql = 1
    EXEC(@sql)
/*end of proc*/
GO


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Assuming that one table's collation was a mistake, how hard is it to just change it to match the rest?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
That isn't really the problem.  The problem is the code running against that table which expects data to be case insensitive.  If you change collation to BIN, it will be case sensitive.  That could cause massive logic errors in queries.

Author

Commented:
So this is a great answer.. edited slightly to ignore a letter after the last number that is associated with MrpForecast...

Is there anyway to add a

Select * into ConsolidatedMrp ?
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you Scott, You nailed this.. Very much appreciated

Author

Commented:
Hi there, So I have found this to be very useful...

is there anyway I can make this so it only does the SELECT * INTO ConsolidatedMrp piece of the Stored Procedure..

I would rather it not show the 'Results' of all the lines in the print screen  when I run the Exec MRPf?

I tried editing the

@exce_sql bit = 1
@print_sql tinyint = 1, /*1=final sql only;  >1 all sql */

and it doesn't seem to change.. all 1 million plus records show in the results...I would rather it say 'Completed', but still push the 1 million records to the ConsolidatedMrp table.

Thank you
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I would need to see the exact code you are running to be sure what to change.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Run it with @exec_sql = 0 and @print_sql = 1 and send me the print outs of the SQL that you get back from that.

Author

Commented:
Return Value
0

Well does that mean that it ran as I had hoped.. ..?

:)


So fast I didn't realize what the hell happened
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
No, that will just print out the SQL.  You'd probably want to be in SSMS so you can see the results of the PRINT statements in the result window below the query.

Author

Commented:
is it possible to not have the PRINT within the results...maybe just say a '1100000 records'....completed or something simple
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
For testing purposes, you can run it in SSMS without ANY actual data rows returned.

Or just verify yourself that the query that is actually running inserts the rows properly.

Note that if the table already exists, you can't do a
"SELECT * INTO dbo.existing_table_name FROM".  
You would have to change it to:
"INSERT INTO dbo.existing_table_name SELECT * FROM ...
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.