July 6, 2011 at 7:16 am
I'll clarify: the gist of what I need is this:
I'm building the sql string from text, variables, and variables and text retrieved in a field in a table.
Is there a way to do this?
Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From '
+ @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode +
'; Select @errorCount'
where @sqlCode is a string retrieved from the db, previously put into a variable, right now it contains the value: WHERE ' + @FieldName + ' IS NULL
the resulting sql statement ends up being:
Declare @errorcount int; Select @errorcount = Count(1) From Dev1.OPD.dbo.TEMP_Hologic428_MainData WHERE ' + @FieldName + ' IS NULL; Select @errorCount
But I want the where clause to actually evaluate the variable @FieldName, not try to execute it that way.
My thought is that if I can force the string to evaluate again somehow, then it would replace that variable with the value it represents. But I can't find a way to do this????
I can change the db value (the where clause in @sqlCode) to be whatever works, I've tried a bunch of different ways!
Any ideas???? I have a meeting in two hours, and I would like to have the issue resolved by then, or know that it can't be done...
your help is GREATLY appreciated!!!
Amy
------------------ original post: ------------------------------
Hi there,
I'm so close, and yet so far... I am looking to report on validation rules -- that is, record how many records break the validation rules set forth for that particular ETL program. But i want it dynamic, so that a proc looks in a table to get all the rules, build the sql statement, and execute it, storing the result (a count of records that break the rule in question) in a variable to use to insert into a table.
I want to make sure it's possible to do this, so I just need to get the dynamic portion of the proc working. If I can get the proc to print out the variable containing the number of records that break the rule in question, then I can take it from there as far as recording the this figure in an error table. Also this is just a simple -- the easiest -- of the rules. If I can get this to work, I'm hoping that I can make the more difficult ones work, and add fields to the implementation table as necessary to accommodate the script code in the Rules table.
If there is already something coded out there that does this, please let me know...
as you will see, I've tried a couple of ways in the proc, but i can't get the variable stored in the sql script in the table to evaluate, instead of just returning the variable name to the sql string...
my tables, data, and procs:
CREATE TABLE [dbo].[T_ETL_ValidationRules](
[RUL_RuleID] [int] IDENTITY(1,1) NOT NULL,
[RUL_Desc] [varchar](100) NOT NULL,
[RUL_SQLCode] [varchar](max) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[T_ETL_RuleImplementations](
[RIM_ImplementationID] [int] IDENTITY(1,1) NOT NULL,
[RIM_ImportID] [int] NULL,
[RIM_PID] [int] NOT NULL,
[RIM_CID] [int] NOT NULL,
[RIM_RUL_RuleID] [int] NOT NULL,
[RIM_FieldName] [varchar](50) NOT NULL,
[RIM_TableName] [varchar](50) NOT NULL,
[RIM_DbName] [varchar](50) NOT NULL,
[RIM_ServerName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Amy_Table](
[CID] [int] NULL,
[PID] [int] NULL,
[FileID] [int] NULL,
[ESuperkey] [nvarchar](1000) NULL,
[Superkey] [nvarchar](1000) NULL,
[DateImported] [datetime] NULL
) ON [PRIMARY]
INSERT INTO [IS_ETLProcess].[dbo].[T_ETL_ValidationRules]
([RUL_Desc]
,[RUL_SQLCode])
VALUES ('Required Field','WHERE '' + @FieldName + '' IS NULL')
INSERT INTO T_ETL_RuleImplementations
([RIM_ImportID]
,[RIM_PID]
,[RIM_CID]
,[RIM_RUL_RuleID]
,[RIM_FieldName]
,[RIM_TableName]
,[RIM_DbName]
,[RIM_ServerName])
VALUES (1,428,80,1,'SuperKey','Amy_Table','yourDBName','yourServerName')
CREATE PROCEDURE [dbo].[USP_RunValidationRule]
@ImplementationID int,
@ImportID int = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PID INT, @CID INT, @fieldName varchar(50), @tablename varchar(50),
@servername varchar(50), @SQLCode varchar(max), @mySQL varchar(max),
@errorcount int, @DBName varchar(50)
Select @PID = ri.RIM_PID,
@CID = ri.RIM_CID,
@fieldName = ri.RIM_FieldName,
@tablename = ri.RIM_tableName,
@DBName = ri.RIM_DbName,
@servername = ri.RIM_servername,
@SQLCode = vr.RUL_sqlcode
From T_ETL_RuleImplementations ri
inner join T_ETL_ValidationRules vr on ri.RIM_RUL_RuleID = vr.RUL_RuleID
Where ri.RIM_ImplementationID = @ImplementationID
/*Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From
(Select RIM_FieldName, RIM_tableName, RIM_servername
From T_ETL_RuleImplementations Where RIM_ImplementationID = ' + Cast(@ImplementationID as varchar(10)) +
') rim, ' + @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode + '
Select @errorCount'*/
Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From '
+ @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode +
'; Select @errorCount'
--exec USP_RunAllValidationRules 428, 82, 0
Select @mySQL
Select @errorCount
exec(@mySQL)
END
CREATE PROCEDURE [dbo].[USP_RunAllValidationRules]
@PID int,
@CID int,
@ImportID int
AS
BEGIN
SET NOCOUNT ON;
--Loop through each implemented rule for this PID and CID, or ImportID
Exec USP_RunValidationRule 1, 1
END
exec USP_RunAllValidationRules 428, 82, 0
July 6, 2011 at 8:16 am
amy,
Not sure about what your really asking, but if your just wanting to print the errors use print instead of select.
July 6, 2011 at 9:02 am
amy.walsh (7/6/2011)
Hi there,I'm so close, and yet so far... I am looking to report on validation rules -- that is, record how many records break the validation rules set forth for that particular ETL program. But i want it dynamic, so that a proc looks in a table to get all the rules, build the sql statement, and execute it, storing the result (a count of records that break the rule in question) in a variable to use to insert into a table.
I want to make sure it's possible to do this, so I just need to get the dynamic portion of the proc working. If I can get the proc to print out the variable containing the number of records that break the rule in question, then I can take it from there as far as recording the this figure in an error table. Also this is just a simple -- the easiest -- of the rules. If I can get this to work, I'm hoping that I can make the more difficult ones work, and add fields to the implementation table as necessary to accommodate the script code in the Rules table.
If there is already something coded out there that does this, please let me know...
as you will see, I've tried a couple of ways in the proc, but i can't get the variable stored in the sql script in the table to evaluate, instead of just returning the variable name to the sql string...
Hi Amy, I think I see where you're going with this. You want to validate your data after it's been loaded into tables...tables that have no constraints on them. This way you can be sure that all data makes it into the database and you do not have to worry about handling data load errors, correct? How much data are we talking about here?
You have to "pay" for validation somewhere and DRI is very good at doing such things. What are you using to load this data? SSIS has very robust mechanisms for redirecting rows that fail to load into a table.
Here is your code that I reformatted with SQL Prompt and surrounded by IFCode tags [code="sql"][/code] in my post so they appear with syntax highlighting:
CREATE TABLE [dbo].[T_ETL_ValidationRules]
(
[RUL_RuleID] [int] IDENTITY(1, 1)
NOT NULL,
[RUL_Desc] [varchar](100) NOT NULL,
[RUL_SQLCode] [varchar](MAX) NOT NULL
)
CREATE TABLE [dbo].[T_ETL_RuleImplementations]
(
[RIM_ImplementationID] [int] IDENTITY(1, 1)
NOT NULL,
[RIM_ImportID] [int] NULL,
[RIM_PID] [int] NOT NULL,
[RIM_CID] [int] NOT NULL,
[RIM_RUL_RuleID] [int] NOT NULL,
[RIM_FieldName] [varchar](50) NOT NULL,
[RIM_TableName] [varchar](50) NOT NULL,
[RIM_DbName] [varchar](50) NOT NULL,
[RIM_ServerName] [varchar](50) NOT NULL
)
CREATE TABLE [dbo].[Amy_Table]
(
[CID] [int] NULL,
[PID] [int] NULL,
[FileID] [int] NULL,
[ESuperkey] [nvarchar](1000) NULL,
[Superkey] [nvarchar](1000) NULL,
[DateImported] [datetime] NULL
)
INSERT INTO [IS_ETLProcess].[dbo].[T_ETL_ValidationRules]
(
[RUL_Desc],
[RUL_SQLCode]
)
VALUES (
'Required Field',
'WHERE '' + @FieldName + '' IS NULL'
)
INSERT INTO T_ETL_RuleImplementations
(
[RIM_ImportID],
[RIM_PID],
[RIM_CID],
[RIM_RUL_RuleID],
[RIM_FieldName],
[RIM_TableName],
[RIM_DbName],
[RIM_ServerName]
)
VALUES (
1,
428,
80,
1,
'SuperKey',
'Amy_Table',
'yourDBName',
'yourServerName'
)
GO
CREATE PROCEDURE [dbo].[USP_RunValidationRule]
@ImplementationID INT,
@ImportID INT = 0
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @PID INT,
@CID INT,
@fieldName VARCHAR(50),
@tablename VARCHAR(50),
@servername VARCHAR(50),
@SQLCode VARCHAR(MAX),
@mySQL VARCHAR(MAX),
@errorcount INT,
@DBName VARCHAR(50)
SELECT @PID = ri.RIM_PID,
@CID = ri.RIM_CID,
@fieldName = ri.RIM_FieldName,
@tablename = ri.RIM_tableName,
@DBName = ri.RIM_DbName,
@servername = ri.RIM_servername,
@SQLCode = vr.RUL_sqlcode
FROM T_ETL_RuleImplementations ri
INNER JOIN T_ETL_ValidationRules vr ON ri.RIM_RUL_RuleID = vr.RUL_RuleID
WHERE ri.RIM_ImplementationID = @ImplementationID
/*Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From
(Select RIM_FieldName, RIM_tableName, RIM_servername
From T_ETL_RuleImplementations Where RIM_ImplementationID = ' + Cast(@ImplementationID as varchar(10)) +
') rim, ' + @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode + '
Select @errorCount'*/
SELECT @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From ' + @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode
+ '; Select @errorCount'
--exec USP_RunAllValidationRules 428, 82, 0
SELECT @mySQL
SELECT @errorCount
EXEC(@mySQL)
END
GO
CREATE PROCEDURE [dbo].[USP_RunAllValidationRules]
@PID INT,
@CID INT,
@ImportID INT
AS
BEGIN
SET NOCOUNT ON ;
--Loop through each implemented rule for this PID and CID, or ImportID
EXEC USP_RunValidationRule
1,
1
END
GO
--exec USP_RunAllValidationRules 428, 82, 0
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 9:04 am
I'll clarify: the gist of what I need is this:
I'm building the sql string from text, variables, and variables and text retrieved in a field in a table.
Is there a way to do this?
Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From '
+ @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode +
'; Select @errorCount'
where @sqlCode is a string retrieved from the db, previously put into a variable, right now it contains the value: WHERE ' + @FieldName + ' IS NULL
the resulting sql statement ends up being:
Declare @errorcount int; Select @errorcount = Count(1) From Dev1.OPD.dbo.TEMP_Hologic428_MainData WHERE ' + @FieldName + ' IS NULL; Select @errorCount
But I want the where clause to actually evaluate the variable @FieldName, not try to execute it that way.
My thought is that if I can force the string to evaluate again somehow, then it would replace that variable with the value it represents. But I can't find a way to do this????
I can change the db value (the where clause in @sqlCode) to be whatever works, I've tried a bunch of different ways!
Any ideas???? I have a meeting in two hours, and I would like to have the issue resolved by then, or know that it can't be done...
your help is GREATLY appreciated!!!
Amy
July 6, 2011 at 9:16 am
Thank you for reformatting -- I didn't know how to do that!
Yes, that is what i need. We are using ssis to load the data, but I don't necessarily want it to fail, I want to report back to the user the issues after it gets into the staging tables, and have them decide whether or not to proceed with the rest of the import procedures, which bring the data into the live tables.
As for the amount of data, to tell you the truth I'm not sure. I'm new here, and the files I've seen are not large -- anywhere from 20K to 200 records. I know there are other ETL programs, though, which take a long time and which I aim to rewrite sometime soon, and I think those must be a lot larger. this process would apply to all programs, which is why i wanted it to be database driven.
I tried to clarify the gist of what i'm looking for, thinking maybe my original explanation was too convoluted.
any ideas on how to build that sql string into an executable statement?
thank you so much for responding!
amy
July 6, 2011 at 9:30 am
Anything you can do in declared T-SQL you can do with Dynamic T-SQL...so is what you're trying to do possible, yes. As for achieving it, it will be a lot of coding and you'll be a string-manipulation ninja by the time it's all done, but it's doable. Hopefully that gives you enough for your meeting.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 10:10 am
AAAAHHHHHH, I figured it out -- the solution turned out to be somewhat simple. I just changed the data in the table from "WHERE ' + @FieldName + ' IS NULL" to "WHERE @FieldName IS NULL", and then did a replace on @mySQL before executing:
Select @mySQL = Replace(@mySQL, '@fieldname', @fieldname)
Now I'm looking for a way to keep the results variable alive after execution of the dynamic sql. only way i can think of to do it is to temporarily store the value in a table. It's a variable scope issue. Once outside the transaction of the dynamic sql, I can't retrieve the value stored in @errorCount.
any other ideas anyone?
Select @mySQL = 'Declare @errorcount int; Select @errorcount = Count(1) From '
+ @serverName + '.' + @DBName + '.dbo.' + @tablename + ' ' + @sqlCode +
'; Select @errorCount'
Select @mySQL = Replace(@mySQL, '@fieldname', @fieldname)
Select @mySQL
Select 'errorcount = ', @errorCount
exec(@mySQL)
July 6, 2011 at 10:14 am
See if something like this works for you:
CREATE TABLE #tmp (errorcount INT) ;
DECLARE @sql NVARCHAR(MAX) ;
SET @sql = 'select 1 as errorcount;'
INSERT INTO #tmp
(
errorcount
)
EXEC (
@sql
) ;
SELECT *
FROM #tmp ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 1:51 pm
hey, that worked, thanks! it's better than the way I had solved the issue, which was by selecting the results of the dynamic sql into a permanent table, and then dropping the table after using it in the proc...
thanks again for your help -- my meeting went well!
Amy
July 6, 2011 at 1:56 pm
Excellent, happy to assist 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply