Stored procedure? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored procedure?

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2013-11-08 : 06:35:18

Hi,

A question... Hope I'm in the right place cause I'm new to this area of SQL.

I have a bunch of tables. More tables will come in this database.

SELECT 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name
FROM mib_historie.information_schema.tables

As a result of that SQL I get all tables:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

I only like to see tablename and max(his_datum_ingang) like above.

To find the max(his_datum_ingang) i need to fire both SQL's for all tables within mib_historie.information_schema.tables

Is there a way to do this automatically? Like in a stored procedure?
I want to create a report with Visual Studio and put that report in the Report Manager. Only need to start to the report and get the answer.

Someone can help me with that?

Thanks already!
Marteijn

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-08 : 08:22:58

quote:
Originally posted by Marteijn

Hi,

A question... Hope I'm in the right place cause I'm new to this area of SQL.

I have a bunch of tables. More tables will come in this database.

SELECT 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name
FROM mib_historie.information_schema.tables

As a result of that SQL I get all tables:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

I only like to see tablename and max(his_datum_ingang) like above.

To find the max(his_datum_ingang) i need to fire both SQL's for all tables within mib_historie.information_schema.tables

Is there a way to do this automatically? Like in a stored procedure?
I want to create a report with Visual Studio and put that report in the Report Manager. Only need to start to the report and get the answer.

Someone can help me with that?

Thanks already!
Marteijn

I didn't quite follow what you are asking - but if you meant that you want to create the select statements and execute them in one shot, you can do it as shown below:

declare @sql nvarchar(max);

SELECT @sql =  querystring from (select 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ';'
FROM mib_historie.information_schema.tables for xml path('')) s(querystring);
exec sp_executesql @sql;

Marteijn
Starting Member

28 Posts

Posted - 2013-11-08 : 08:59:41

Thank you for your answer!

Is it possible to store the results of query into a table?

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER

2013-10-30 15:42:13.953 HIS_GWS_SZVOLGFASE
2013-11-04 15:39:59.137 HIS_GWS_SZWERKER
2013-11-08 06:15:45.037 HIS_PIM_TPER

I want to make a report in Visual Studio for it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 11:08:15

yep use insert..select or select into


select * into YourTable
from
(
select max(his_datum_ingang) AS dateval, 'HIS_GWS_SZVOLGFASE' AS TableName from HIS_GWS_SZVOLGFASE
union all
select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER
union all
select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Marteijn
Starting Member

28 Posts

Posted - 2013-11-08 : 11:20:33

Thanks.. but... there are a lot of tables in MIB_HISTORIE and tables could be
added on daily bases. It needs to be generic.
The query James K provided works perfectly! Those results needs to be imported in
an empty table so I can make a report in SSRS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 12:38:18

then extend that to match this


IF OBJECT_ID('TableCounts') IS NULL
CREATE TABLE TableCounts
(
Cnt int,
TableName varchar(100)
)
DECLARE @SQL VARCHAR(MAX)
SELECT @sql =  'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL ' 
FROM information_schema.tables for xml path('')) s(querystring);
exec sp_executesql @sql;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Marteijn
Starting Member

28 Posts

Posted - 2013-11-13 : 03:27:42

Hi,

I get an error when running the script above:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 04:53:03

DECLARE @SQL NVARCHAR(MAX)

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-13 : 05:08:18

IF OBJECT_ID('TableCounts') IS NULL
CREATE TABLE TableCounts
(
Cnt int,
TableName varchar(100)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @sql = 'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));
exec sp_executesql @sql;

--
Chandu

Marteijn
Starting Member

28 Posts

Posted - 2013-11-13 : 07:10:25

USE MIB_HISTORIE
GO

IF OBJECT_ID('dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE dbo.MAXDAT
GO

CREATE TABLE dbo.MAXDAT (statement nvarchar(max))
GO

declare @sql nvarchar(max);

SELECT @sql = querystring from (select 'select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ';'
FROM mib_historie.information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

insert into dbo.MAXDAT values (@sql)

--SELECT @sql = 'insert TableCounts' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
--FROM information_schema.tables for xml path('')) s(querystring);
--SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

--exec sp_executesql @sql;

The above SQL works. But i can't use the exec after. I get errors on his_datum_ingang.
Table dbo.MAXDAT contains 1 line after exec:

select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE;select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER;select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER; etc. etc. (There are 70 tables).

Whats going wrong to get the values in the table.

Example:
2013-10-30 15:42:13.953 HIS_GWS_SZVOLGFASE
2013-11-04 15:39:59.137 HIS_GWS_SZWERKER
2013-11-08 06:15:45.037 HIS_PIM_TPER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-13 : 07:48:01

do you've that column in all of those tables? otherwise it wont work.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Marteijn
Starting Member

28 Posts

Posted - 2013-11-13 : 08:05:19

Yes, that column is in all tables there! Its a date field and i want
the max date of every table. The max date and the name of the table, thats
what i like seeing in a new table.

Thank you!

Marteijn
Starting Member

28 Posts

Posted - 2013-11-13 : 08:20:33

USE MIB_HISTORIE
GO

IF OBJECT_ID('MIB_METADATA.dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE MIB_METADATA.dbo.MAXDAT
GO

CREATE TABLE MIB_METADATA.dbo.MAXDAT (statement nvarchar(max))
GO

USE MIB_HISTORIE
GO

declare @sql nvarchar(max);

SELECT @sql = 'insert MIB_METADATA.dbo.MAXDAT' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

insert into MIB_METADATA.dbo.MAXDAT values (@sql)

--exec sp_executesql @sql;

I get an error on exec

In the table it looks like this now:

insert MIB_METADATA.dbo.MAXDAT select max(his_datum_ingang), 'HIS_GWS_SZVOLGFASE' from HIS_GWS_SZVOLGFASE UNION ALL select max(his_datum_ingang), 'HIS_GWS_SZWERKER' from HIS_GWS_SZWERKER UNION ALL select max(his_datum_ingang), 'HIS_PIM_TPER' from HIS_PIM_TPER UNION ALL etc. etc.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 04:29:13

Post us back the error message....

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 05:24:02

whats the purpose of this?
insert into MIB_METADATA.dbo.MAXDAT values (@sql)
@sql contains actual query so didnt understand where you're trying to insert that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Marteijn
Starting Member

28 Posts

Posted - 2013-11-15 : 03:30:39

insert into MIB_METADATA.dbo.MAXDAT values (@sql)
that was to see if it really inserted the value into the table

but if i run the below, i get the following error now...

USE MIB_HISTORIE
GO

IF OBJECT_ID('MIB_METADATA.dbo.MAXDAT', 'U') IS NOT NULL
DROP TABLE MIB_METADATA.dbo.MAXDAT
GO

CREATE TABLE MIB_METADATA.dbo.MAXDAT (statement nvarchar(max))
GO

USE MIB_HISTORIE
GO

declare @sql nvarchar(max);

SELECT @sql = 'insert into MIB_METADATA.dbo.MAXDAT' + CHaR(10) + CHAR(13)+ querystring from (select ' select max(his_datum_ingang), '+ ''''+ table_name + ''' from '+ table_name + ' UNION ALL '
FROM information_schema.tables for xml path('')) s(querystring);
SET @sql = LEFT ( @sql, LEN(@sql)-LEN('UNION ALL select'));

exec dbo.sp_executesql @sql;

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-15 : 03:34:14

You've only single column defined in MIB_METADATA.dbo.MAXDAT but you're trying to insert more than 1 values into it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Marteijn
Starting Member

28 Posts

Posted - 2013-11-15 : 04:38:10

Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-15 : 05:10:22

quote:
Originally posted by Marteijn

Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-15 : 05:56:38

quote:
Originally posted by Marteijn

Thank you guys! It's working! I was staring blind on the exec commando that i
forgot the design of the table ;-)
Thank you for your effort! :-)

cool
glad that you got it all sorted!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources