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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy