Storing dynamic sql

Question

Post reply

Storing dynamic sql

rockstar283

SSCrazy

Points: 2704
More actions
April 19, 2013 at 11:09 am

#394634

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Sergiy SSC Guru Points: 110206 More actions · Answer 2

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

_____________
Code for TallyGenerator