May 24, 2013 at 10:17 am
I am unable to understand how variables work:
I have created this query that finds all the column names and associated tables that contain "REALS" using a cursor.
I want to find the MIN and MAX reals for the "found" columns thus the reason why I need the table names.
I figured that while the cursor had gotten the column name and table in the respective variables @ColVal and @TableName , I could just create a SELECT statement to return the MIN or MAX value of that column
.
EX: SELECT MAX( + @ColVal + ) FROM + @TableName;
But it is not working.
ANY IDEAS?
Here is the query:
USE [TankInspections]
GO
/****** Object: StoredProcedure [dbo].[MINMAXREAL2] Script Date: 05/22/2013 16:27:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:David Cunningham
-- Create date: 05-22-13
-- Description:MIN MAX REALS
-- Finds all the column names in all the tables that have data type REAL
-- It then loops thru that resulting data (column names and table) and finds the MAX REAL number in that column
-- =============================================
DECLARE @ColVal varchar(50), @TableName varchar(50), @MaxVal varchar(100);
DECLARE data_cursor CURSOR FOR
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS where (DATA_TYPE = 'real');
OPEN data_cursor;
-- Perform the first fetch.
FETCH NEXT FROM data_cursor INTO @ColVal,@TableName;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM data_cursor INTO @ColVal,@TableName;
PRINT 'Column Name: ' + @ColVal + ' Table Name: ' + @TableName;
-- BUILD and INSERT NEW SQL STATEMENT TO GET MAX VALUE
-- EX: SELECT MAX( + @ColVal + ) FROM + @TableName
END
CLOSE data_cursor;
DEALLOCATE data_cursor;
GO
_________
Any solution or tutorials reference would be great - I am "kind of" new to SQL - exposed to it thru VB, .NET etc..
May 24, 2013 at 11:11 am
The simple answer is - You need to use derived SQL to do this:
SELECT MAX( + @ColVal + ) FROM + @TableName
DECLARE @SQL Varchar(1000); -- (Declare this outside your loop)
SET @SQL = 'SELECT MAX(' + @ColVal + ') FROM ' + @TableName;
EXEC (@SQL);
There are other ways of executing derived SQL, but this is the simplest.
Note the derived SQL runs in a separate session so you can't directly save the results into temp tables for instance - you need to do something like this:
IF OBJECT_ID('ATEMP') IS NOT NULL
DROP TABLE ATEMP;
CREATE TABLE ATEMP
(
name varchar(50)
);
DECLARE @SQL Varchar(1000);
SET @SQL = 'SELECT name FROM sys.databases';
EXEC (@SQL);
SET @SQL = 'INSERT INTO ATEMP (name) SELECT MIN(name) FROM sys.databases';
EXEC (@SQL);
SET @SQL = 'INSERT INTO ATEMP (name) SELECT MAX(name) FROM sys.databases';
EXEC (@SQL);
SELECT * FROM ATEMP;
May 24, 2013 at 12:42 pm
You have a few things going on here. First of all you are using a deprecated system catalog.
Laurie is correct that you will need use dynamic sql for this, but you do not need a cursor for this.
Here is a way to do this with no cursor or looping.
declare @SQL nvarchar(max);
with MySql(sqlstring) as
(
select 'UNION ALL select ''' + so.name + ''' as TableName, MAX([' + sc.name + ']) as MaxValue, MIN([' + sc.name + ']) as MinValue from [' + so.name + '] '
from sys.columns sc
join sys.objects so on sc.object_id = so.object_id
join sys.systypes st on sc.user_type_id = st.xusertype
where st.name = 'real'
for XML PATH('')
)
select @SQL = stuff(sqlstring, 1, 10, '') from MySql
exec sp_executesql @SQL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 1:02 pm
SSCrazy Eights - excellent results - all I need is the fields names in the output - as I mentioned I "kind of" new and I can see that so.name = as TableName and sc.name = as MaxValue etc...what is the "SQL name" for a column/field name? I need to know the column names that contained the MIN/MAX values.
What documentation talks about this SC and SO stuff and where did the XML come in? Thank you so much for this insight - I am on thrilled to learn from the great ones!!!
May 24, 2013 at 1:09 pm
davidca65 (5/24/2013)
SSCrazy Eights - excellent results - all I need is the fields names in the output - as I mentioned I "kind of" new and I can see that so.name = as TableName and sc.name = as MaxValue etc...what is the "SQL name" for a column/field name? I need to know the column names that contained the MIN/MAX values.What documentation talks about this SC and SO stuff and where did the XML come in? Thank you so much for this insight - I am on thrilled to learn from the great ones!!!
Well we build our dynamic sql using sc.name right? That should give you a hint that sc.name is the name of the column. 😉 Just add that as another column inside the dynamic sql and you will have the column name too.
What you might want to do is comment out the exec statement and instead select @sql so you can the sql you are running.
You should probably read up on sys.objects, sys.columns in addition to STUFF and FOR XML.
Happy to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply