February 9, 2005 at 5:00 pm
Here's what I want to do. I want to have one validation table as follows:
CREATE TABLE [dbo].[zValidationLookups] (
[EdTableName] [varchar] (75) NOT NULL ,
[FieldToTest] [char] (30) NOT NULL ,
[ValidValue] [varchar] (15) NOT NULL
)
What I want to do is have a stored procedure that I will pass a table name and field name to, the proc will then return rows that do not match values found in [ValidValue]. Effectively what I want is something akin to macro expansion that I think I remember from xBase days(daze?) but I could be brain-dead.
The field [EdTableName] would contain something like the following:
table1, table2, table3
since several fields in several tables might have the same values. Likewise, the FieldToTest would have several field names corresponding to what tables they appear in, though I think the field names are standardized across tables in this DB, I’ll have to confirm this.
In semi-pseudocode terms, I'm wanting to do:
spValidater (table1, field1)
select *
from %EdTableName%
where
‘table1’ in [EdTableName]
AND ‘field1’ in [FieldToTest]
AND value(‘field1’) NOT IN [ValidValue]
If all rows pass validation, the proc would return ‘%EdTableName% is clean.’
I’ve always wanted to do things like this in Transact SQL but I’ve never found a way to pull it off. Any suggestions? It would save me a lot of brute-force coding.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 10, 2005 at 10:06 am
I've received a suggested solution from a local email group using Dynamic SQL:
CREATE PROCEDURE [dbo].[YourSproc]
@table1 varchar(100),
@field1 varchar(100)
AS
SET NOCOUNT ON
Declare @SQL varchar(500)
select *
from %EdTableName%
where
'table1' in [EdTableName]
AND 'field1' in [FieldToTest]
AND value('field1') NOT IN [ValidValue]
Set @SQL = 'Select * From ' + RTrim(@table1) + 'Where ' +
RTrim(@table1) + 'IN [EdTableName] AND ' +
RTrim(@field1) + 'IN [FieldToTest] AND Value(' + RTrim(@field1) + ')
NOT IN [ValidValue]'
Exec (@SQL)
I'll test it, if I get it working I'll post the solution.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 10, 2005 at 3:11 pm
Got it!
I got a push in the right direction from a local mail list and got it working. I added the ability to specify a sort order as a third parameter, if you want a multi-key sort, just put the fields in single quotes seperated by commas.
CREATE PROCEDURE [dbo].[spValidate]
@table1 varchar(100),
@field1 varchar(100),
@sort1 varchar(100) = null
AS
declare @qch char(1)
set @qch = char(39) --a single quote
SET NOCOUNT ON
Declare @SQL varchar(500)
Set @SQL = 'select * from ' + RTrim(@table1)
+ ' where ' + RTrim(@field1) + ' NOT IN'
+ ' (select ValidValue from ValidationLookups'
+ ' where EdTableName like ' + @qch + '%' + RTrim(@table1) + '%' + @qch
+ ' and FieldToTest like ' + @qch + '%' + RTrim(@field1) + '%' + @qch + ')'
if (len(rtrim(@sort1)) > 0) set @sql = @sql + ' order by ' + @sort1
Exec (@SQL)
go
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply