Click here to Skip to main content
15,891,184 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,

Im trying to check if a field (isParent) exists and if it doesn't, print a message and stop. If it exists, continue based one one of two choices. However, when I know the field doesnt exist, it throws an invalid field error on the first instance of isParent is Null. Why is it doing this?

Thanks

declare @RUNTYPE varchar(20);
--set @RUNTYPE = 'Incremental Run'
set @RUNTYPE = 'Full Run';

--select COL_LENGTH('eddsdbo.document','isParent')


IF COL_LENGTH('eddsdbo.document','isParent') is NULL
	PRINT 'isParent field is missing. Please create it and retry.'
ELSE 
	BEGIN
		IF @RunType = 'Incremental Run' --AND COL_LENGTH('eddsdbo.document','isParent') is not null
			BEGIN
			--populate yes for parent docs and run first
			update EDDSDBO.DOCUMENT
			set isParent = 1
			where ((BEGATTACH is NULL) or (BEGDOC = BEGATTACH))
			and isParent is NULL
					
			--remaining docs with isParent = NULL should be set to No at this point
			update EDDSDBO.DOCUMENT
			set isParent = 0
			where isParent is NULL

			PRINT 'Incremental run successful!'
			END

		ELSE --IF @RunType = 'Full Run'
			BEGIN
			update EDDSDBO.DOCUMENT set isParent = NULL

			--populate yes for parent docs and run first
			update EDDSDBO.DOCUMENT
			set isParent = 1
			where ((BEGATTACH is NULL) or (BEGDOC = BEGATTACH))
			and isParent is NULL
					
			--remaining docs with isParent = NULL should be set to No at this point
			update EDDSDBO.DOCUMENT
			set isParent = 0
			where isParent is NULL

			PRINT 'Full run successful!'
			END
	END
Posted
Comments
Patrice T 27-Jan-16 20:49pm    
Exact error message and position please.
_Asif_ 28-Jan-16 1:52am    
Your code seems Ok, are you correctly executing it?
[no name] 28-Jan-16 8:48am    
thanks for taking a look. I'm just doing a full run in SQL Server 2008. Is there a different method I should be employing when trying to run it?

Errors on the last line where it says:
C#
--remaining docs with isParent = NULL should be set to No at this point
			update EDDSDBO.DOCUMENT
			set isParent = 0
			where isParent is NULL


Error is:
C#
Msg 207, Level 16, State 1, Line 19
Invalid column name 'isParent'.


Thanks for your help.
 
Share this answer
 
So apparently the solution that ended up working for me - passing parts of the code as dynamic SQL.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900