November 22, 2011 at 1:28 pm
I am trying to create a validation tool that will check to see if a field is blank and if it is blank then check to see if the parent field has a value. If the parent value is valid then I need to insert a record into a ValidationTable that reports to the user fields that have to be entered. I am stuck on the Looping part of the INSERT statement. I know that I have to follow my CTE with a SELECT or something but this is where I am stuck. How do I loop through each record with our with out a cursor? Here is what I have:
CREATE PROCEDURE [dbo].[ACC_CallValidationByTableCathID2] (@TableName as nvarchar(200), @PKRecordID as int)
--@TableName is the table that contains the fields I will be looking at
--@PKRecordID is the primary key value of the record from the table I will be looking at
AS
DECLARE @i int
DECLARE @numrows int
DECLARE @FieldName as nvarchar(200)
SET NOCOUNT ON
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM dbo.DBSpecs WHERE CATblName = @TableName)
IF @numrows > 0
WITH FieldRowSet AS (SELECT CAFldName,ROW_NUMBER() OVER (ORDER BY CAFldName) AS 'FldRank'
FROM dbo.DBSpecs WHERE CATblName = @TableName)
-- I now cannot use a while statement here since a SELECT statement is required
-- I would like to loop through each row and call my validation stored stored procedure passing the results from each row
-- I would like to do this from a result set if possible to avoid looping
WHILE (@i <= @numrows)
BEGIN
SET @FieldName = (SELECT CAFldName, FldRank FROM FieldRowSet WHERE @i = FldRank)
EXEC dbo.ACCValidationEngine @PKRecordID ,@TableName,@FieldName
SET @i = @i + 1
END
GO
Owen White
November 22, 2011 at 2:17 pm
I found this on StackOverflow by U07CH:
Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @user-id nVarchar(50)
Insert into @TableUser
Select User_ID
From Users
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1
While @i <= @MaxI
Begin
Select @user-id = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @user-id
Select
@i = @i + 1, @user-id = null
End
Owen White
November 22, 2011 at 3:23 pm
From what you describe I don't think you need any kind of looping at all. You simply want to insert into a table some data when some conditions are met. This really sounds like a single insert statement. I can't even begin to take a stab at what you are looking for however. If you can post some ddl, sample data and some clear definition we can make this happen. Take a look at the first in my signature for best practices on posting all the stuff to get solid tested answers.
_______________________________________________________________
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/
November 22, 2011 at 4:20 pm
Thank you for the response. I have a table called DBSpec which contains a list of fields and their respective table names. I also have the associated parent field and the table for the parent along with the valid value and compare operator. So if a user was to enter data into an application and fill in some demographic data, but left the SSN field blank. I want to report to them in a clean up report that they are missing the SSN and then provide a link so they can navigate to the field and fill in the missing data.
If the missing data element was Insurance-Medicare. Then I look to see if the field is blank and if it is then I look to see if there is parent field that I have to check first. If the parent field is Insurance-Government and the answer is NO then I do not need to report the Insurance-Medicare as the user stated that there is no Government health insurance for the current record.
Now, I am passing the table name and then cycling through all of the fields in that table. I check every field and if it is missing and the parent field constitutes that it truley is missing then, I execute a stored procedure that inserts the Primary Key of the field, The field name, a message stating that it is missing, and some additional data. I am not sure I can do this through a single insert statement due to the fact there is a lot of logic. If you want me to post my code here I will just ask.
Owen White
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply