Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Storing dynamic sql Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 11:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:00 PM
Points: 111, Visits: 501
DECLARE MY_CURSOR Cursor
FOR
SELECT [DB_NAME],[SCHEMA_NAME],[TABLE_NAME],[COLUMN_NAME] FROM dbo.Data_Profile_Stats
Open My_Cursor
DECLARE @DB_NAME nvarchar(500),@SCHEMA_NAME nvarchar(500),@TABLE_NAME nvarchar(500),@COLUMN_NAME nvarchar(500), @QUERY NVARCHAR(MAX)
DECLARE @MAX_NUMBER INT, @MIN_NUMBER INT, @NULL_COUNT INT, @BLANK_COUNT INT, @ZERO_COUNT INT,@DISTINCT_COUNT INT
Fetch NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME
While (@@FETCH_STATUS = 0)
BEGIN

SELECT @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME

SET @QUERY='SELECT MAX('+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME+'.'+@COLUMN_NAME+') FROM '+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME
--SET @MAX_NUMBER=exec sp_executesql @QUERY
EXEC SP_EXECUTESQL @QUERY, N'@MAX_NUMBER NVARCHAR(255) OUTPUT', @MAX_NUMBER=@MAX_NUMBER OUTPUT
SELECT @MAX_NUMBER
--SET @QUERY='UPDATE [dbo].[Data_Profile_Stats] SET MAX_NUMBER='+CAST(@MAX_NUMBER AS VARCHAR(10))+' WHERE [DB_NAME]='+''''+@DB_NAME+''''+' AND SCHEMA_NAME='+''''+@SCHEMA_NAME+''''+' AND TABLE_NAME='+''''+@TABLE_NAME+''''+' AND COLUMN_NAME='+''''+@COLUMN_NAME+''''
--exec sp_executesql @QUERY

FETCH NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO


In the above query, I am seeing the result of the EXEC SP_EXECUTESQL but not able to see the value of @MAX_NUMBER in the next line

Can someone tell me what am I doing wrong?

Thanks in advance
Post #1444527
Posted Friday, April 19, 2013 11:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 23,022, Visits: 31,542
try this:


DECLARE MY_CURSOR Cursor
FOR
SELECT [DB_NAME],[SCHEMA_NAME],[TABLE_NAME],[COLUMN_NAME] FROM dbo.Data_Profile_Stats
Open My_Cursor
DECLARE @DB_NAME nvarchar(500),@SCHEMA_NAME nvarchar(500),@TABLE_NAME nvarchar(500),@COLUMN_NAME nvarchar(500), @QUERY NVARCHAR(MAX)
DECLARE @MAX_NUMBER NVARCHAR(255),
@MIN_NUMBER INT,
@NULL_COUNT INT,
@BLANK_COUNT INT,
@ZERO_COUNT INT,
@DISTINCT_COUNT INT

Fetch NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME
While (@@FETCH_STATUS = 0)
BEGIN
SELECT @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME

SET @QUERY='SELECT @MAX_NUMBER MAX('+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME+'.'+@COLUMN_NAME+') FROM '+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME

EXEC SP_EXECUTESQL @QUERY, N'@MAX_NUMBER NVARCHAR(255) OUTPUT', @MAX_NUMBER = @MAX_NUMBER OUTPUT

SELECT @MAX_NUMBER

FETCH NEXT FROM MY_CURSOR INTO @DB_NAME,@SCHEMA_NAME,@TABLE_NAME,@COLUMN_NAME
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO


Not sure what you are trying to accomplish here.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1444540
Posted Sunday, April 21, 2013 5:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
Lynn Pettis (4/19/2013)
try this:


There must be a typo after @MAX_NUMBER:
    SET @QUERY='SELECT @MAX_NUMBER = MAX('+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME+'.'+@COLUMN_NAME+') FROM '+@DB_NAME+'.'+@SCHEMA_NAME+'.'+@TABLE_NAME

And, it's a good habit to prevent a possibility of an SQL injection:

SET @QUERY='SELECT @MAX_NUMBER = MAX('+TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME+'.'+COLUMN_NAME+') FROM '+TABLE_CATALOG+'.'+TABLE_SCHEMA+'.'+TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_CATALOG = @DB_NAME AND TABLE_SCHEMA = @SCHEMA_NAME AND TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME

Post #1444830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse