Replace Hard Code Table Names with Dynamic SQL, Execute and Return Results Single Row

Question

Replace Hard Code Table Names with Dynamic SQL, Execute and Return Results Single Row

Welsh Corgi

SSC Guru

Points: 116520
More actions
April 16, 2012 at 11:03 am

#267449

Hi Clever T-SQL Experts.
I had the following, it returns the Count(*) in two tables in a single row result set:
------------------------------------------------------------------------------
DECLARE @SQLRowCount int
DECLARE @DB2RowCount int
SET @SQLRowCount = (SELECT COUNT(*) FROM point_cyp.Bureau_Type)
SET @DB2RowCount = (SELECT * FROM OPENQUERY(LSCYPR40DAT,'SELECT COUNT(*) FROM BUR_TYPE'))
SELECT @SQLRowCount AS SQLRowCount, @DB2RowCount AS DB2RowCount
GO
---------------------------------------------------------------------------------
However I want something like the following Code. However it does not return the Counts from the two variables in a single row.
How can this be accomplished?
DECLARE @SchemaName VARCHAR(20)
DECLARE @SQLTableName VARCHAR(60)
DECLARE @SQLQuery VARCHAR(255)
DECLARE @AS400TableName VARCHAR (20)
DECLARE @OpenQuery VARCHAR(255)
DECLARE @OpenQueryPrefix VARCHAR(4000)
DECLARE @OpenQuerySuffix VARCHAR(12)
SET @SchemaName = 'cyp'
SET @SQLTableName = 'Bureau_Type'
SET @SQLQuery = 'SELECT COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName
SET @AS400TableName = 'BUR_TYPE'
SET@OpenQueryPrefix = 'SELECT * FROM OPENQUERY(LSCTLR40DAT, ''SELECT COUNT(*) FROM '
SET@OpenQuerySuffix = ''')'
SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix
--SET @OpenQuery = '(' + @OpenQuery + ')'
SELECT @SQLQuery AS SQLRowCount,@OpenQuery AS DB2RowCount
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 24 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

umm....you have to exec your dynamic sql before you can get results. All you have done is build a string and then select it.

_______________________________________________________________

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/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The problem is that I need to store the results of @i in a variable.

I tried:

SET @SQLRowCount = @i

It is NULL but how do I capture the SQL Count so that I can later capture the DB2 Count and have this statement return the results of both Counts?

SELECT @SQLRowCount AS SQLRowCount, @DB2RowCount AS DB2RowCount

SET @SQLQuery = 'SELECT COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName

DECLARE @i INT

EXEC sp_executesql

@query = @SQLQuery,

@params = N'@i INT OUTPUT',

@i = @i OUTPUT

PRINT @i

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

You need to declare the variable in the dynamic sql, set it to count(*) in your select. The output variable used in the sp_executesql proc will then have that value returned.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I did declare the variable?

DECLARE @SQLQuery NVARCHAR(255)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Dynamic sql will run in its own thread, your variable outside of the dynamic sql. You must declare it INSIDE the dynamic portion.

_______________________________________________________________

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/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I tried the following which is obviously not it and a bunch of other tries"

EXEC sp_executesql

@query = @SQLQuery,

@params = N'@SQLRowCount INT OUTPUT',

@SQLRowCount = @SQLRowCount OUTPUT

Returns:

(No column name)

5

SQLRowCount

NULL

Could you please direct me to an example?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Here is a quick sample you can run:

CREATE TABLE #TestIt(TabID INT);

INSERT INTO #TestIT

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;

DECLARE @MyCnt INT;

DECLARE @SQLCmd NVARCHAR(MAX);

DECLARE @ParmDefinition nvarchar(500);

SET @SQLCmd = N'select @cnt = count(*) from #TestIt;';

SET @ParmDefinition = N'@cnt int OUTPUT'

EXEC sp_executesql @SQLCmd, @ParmDefinition, @cnt = @MyCnt OUTPUT;

SELECT @MyCnt;

DROP TABLE #TestIt;

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

I tried changing it slightly but I get a NULL When SELECTING on @MMyCount:

SET @SQLQuery = 'SELECT COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName

DECLARE @MyCnt INT;

DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@cnt int OUTPUT'

EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;

SELECT @MyCnt AS MyCount;

I'm missing something minor?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Welsh Corgi (4/16/2012)
I tried changing it slightly but I get a NULL When SELECTING on @MMyCount:
SET @SQLQuery = 'SELECT COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName
DECLARE @MyCnt INT;
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@cnt int OUTPUT'
EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;
SELECT @MyCnt AS MyCount;
I'm missing something minor?

SET @SQLQuery = 'SELECT @cnt = COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName -- < here

DECLARE @MyCnt INT;

DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@cnt int OUTPUT'

EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;

SELECT @MyCnt AS MyCount;

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Lynn,

It works, my vision must be failing me because I did not notice the difference?

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Welsh Corgi (4/16/2012)
Lynn,
It works, my vision must be failing me because I did not notice the difference?
Thanks!

Glad to help.

Think of it as a stored procedure that you need to return a value using an output parameter. It is basically the same thing.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

So the first part works, the SQL Server Count.

SET @SQLQuery = 'SELECT @cnt = COUNT(*) FROM ' + @SchemaName +'.'+ @SQLTableName

DECLARE @MyCnt INT;

DECLARE @SQLRowCount int

DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@cnt int OUTPUT'

EXEC sp_executesql @SQLQuery, @ParmDefinition, @cnt = @SQLRowCount OUTPUT;

SELECT @SQLRowCount AS SQLRowCount;

SET @AS400TableName = 'BUR_TYPE'

SET@OpenQueryPrefix = 'SELECT * FROM OPENQUERY(LSCTLR40DAT, ''SELECT COUNT(*) FROM '

SET@OpenQuerySuffix = ''')'

SET @OpenQuery = @OpenQueryPrefix + @AS400TableName + @OpenQuerySuffix

DECLARE @MyCnt INT;

DECLARE @ParmDefinition nvarchar(500);

SET @ParmDefinition = N'@cnt int OUTPUT'

EXEC sp_executesql @OpenQuery, @ParmDefinition, @cnt = @MyCnt OUTPUT;

SELECT @MyCnt AS MyCount;

SQLRowCount

5

00001

8

MyCount

NULL

What am I doing wrong?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Can you add a column alias to the count(*) in the select from the as400 table? If so, then do that, then select that column name in the outer select and assign it to your output variable just like you did for the SQL Server table.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I'm sorry but I do not follow.

I do not have a column Alias in the SQL Select?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/