Avatar of Michael Katz
Michael KatzFlag for United States of America asked on

The result of a qry as a wry

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?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Sharath S

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
Pavel Celba

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.
ASKER
Michael Katz

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Pavel Celba

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-
ASKER
Michael Katz

When i do as you suggest.. I get '

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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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.
ASKER
Michael Katz

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
ASKER
Michael Katz

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

the version is 2014 (SP3-GDR)KB4505218)-12.0.6108.1 X64
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Pavel Celba

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

slightwv (䄆 Netminder)

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 Pletcher


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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Michael Katz

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 Pletcher

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?!
ASKER
Michael Katz

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...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

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

ASKER
Michael Katz

here it is
Table_List.xlsx
Scott Pletcher

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


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

Assuming that one table's collation was a mistake, how hard is it to just change it to match the rest?
Scott Pletcher

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.
ASKER
Michael Katz

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 ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Michael Katz

Thank you Scott, You nailed this.. Very much appreciated
ASKER
Michael Katz

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 Pletcher

I would need to see the exact code you are running to be sure what to change.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

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.

ASKER
Michael Katz

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 Pletcher

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Michael Katz

is it possible to not have the PRINT within the results...maybe just say a '1100000 records'....completed or something simple
Scott Pletcher

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 ...