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

Dynamic query Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 4:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:31 AM
Points: 225, Visits: 1,006
I am writing a stored procedure with dynamic query to find the record counts from two tables. The table names will be passed as parameters from SSIS -hence the table names will be dynamic. I need to capture the record count into a variable and insert them into a table as well as also pass them back to SSIS for comparison. I can't really get to work as i am not a dynamic sql expert. Any insights will be appreciated. thanx.

CREATE PROCEDURE [dbo].[spDmValidationCheck](
@DVTableName NVARCHAR(101)
,@DataMartTableName NVARCHAR(101)
,@DVTableRecordCount BIGINT OUTPUT
,@DataMartTableRecordCount BIGINT OUTPUT
)


AS
BEGIN

DECLARE @CurrentDateTime DATETIME=GETDATE()
DECLARE @SQL NVARCHAR(MAX)='SELECT COUNT (*) FROM '
DECLARE @DmSQL NVARCHAR(MAX)=''
DECLARE @DvSQL NVARCHAR(MAX)=''

SET @DmSQL=@SQL+@DataMartTableName
EXEC @DVTableRecordCount = sp_executesql @DmSQL OUTPUT ---??

INSERT INTO Test.dbo.DmValidation(DVTableName, DataMartTableName,DVTableRecordCount,DataMartTableRecordCount ,LogDateTime, RemarksAction)
VALUES
(@DVTableName,@DataMartTableName,@DVTableRecordCount,@DataMartTableRecordCount,@CurrentDateTime,NULL)

SELECT ISNULL(@DataMartTableRecordCount,0)AS DataMartTableRecordCount,ISNULL(@DVTableRecordCount,0) AS DVTableRecordCount

END


Post #1397464
Posted Tuesday, December 18, 2012 2:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
To make what you have work, take a look a this:

CREATE PROCEDURE  [dbo].[spDmValidationCheck](
@DVTableName NVARCHAR(101)
,@DataMartTableName NVARCHAR(101)
,@DVTableRecordCount BIGINT OUTPUT
,@DataMartTableRecordCount BIGINT OUTPUT
)
AS
BEGIN

DECLARE @CurrentDateTime DATETIME=GETDATE()
DECLARE @SQL1 NVARCHAR(MAX)=N'SELECT @DVTableRecordCount = COUNT(*) FROM '
DECLARE @SQL2 NVARCHAR(MAX)=N'SELECT @DataMartTableRecordCount = COUNT(*) FROM '
DECLARE @DmSQL NVARCHAR(MAX)=''
DECLARE @DvSQL NVARCHAR(MAX)=''

SET @DmSQL=@SQL1+@DVTableName
EXEC sp_executesql @DmSQL, N'@DVTableRecordCount BIGINT OUTPUT', @DVTableRecordCount OUTPUT

SET @DmSQL=@SQL2+@DataMartTableName
EXEC sp_executesql @DmSQL, N'@DataMartTableRecordCount BIGINT OUTPUT', @DataMartTableRecordCount OUTPUT

INSERT INTO DmValidation(DVTableName, DataMartTableName, DVTableRecordCount, DataMartTableRecordCount, LogDateTime, RemarksAction)
VALUES (@DVTableName,@DataMartTableName,@DVTableRecordCount,@DataMartTableRecordCount,@CurrentDateTime,NULL)

SELECT ISNULL(@DataMartTableRecordCount,0)AS DataMartTableRecordCount,ISNULL(@DVTableRecordCount,0) AS DVTableRecordCount
END



______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1398055
Posted Tuesday, December 18, 2012 3:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.

CREATE PROCEDURE  [dbo].[spDmValidationCheckTODD](
@DVTableName NVARCHAR(101)
,@DataMartTableName NVARCHAR(101)
,@debug bit = 0)
AS
DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
INSERT INTO DmValidation(DVTableName, DataMartTableName, DVTableRecordCount, DataMartTableRecordCount, LogDateTime, RemarksAction)
SELECT
' + '''' + @DVTableName + '''' + ', ' + '''' + @DataMartTableName + '''' + ',
(SELECT COUNT(*) FROM ' + @DVTableName + '),
(SELECT COUNT(*) FROM ' + @DataMartTableName + '),
GETDATE(), ''''
'

IF @debug = 1 PRINT @SQL
EXEC(@SQL)



______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1398070
Posted Tuesday, December 18, 2012 9:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
toddasd (12/18/2012)
Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.


Famous last words.

You should always be prepared for SQL Injection because 1) You never know when your "closed environment" is going to be opened up, 2) you never know just how clever hackers are at getting into supposed "closed environments", and 3) a closed environment will not protect you from someone on the inside with a score to settle.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1398140
Posted Wednesday, December 19, 2012 7:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
Jeff Moden (12/18/2012)
toddasd (12/18/2012)
Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.


Famous last words.

You should always be prepared for SQL Injection because 1) You never know when your "closed environment" is going to be opened up, 2) you never know just how clever hackers are at getting into supposed "closed environments", and 3) a closed environment will not protect you from someone on the inside with a score to settle.


True on all counts. I regretted posting that a minute after clicking submit. Trouble is my entire system is built like this. I guess it's time to go retrofitting.


______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Post #1398406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse