Ignore Invalid column name error

I'm building a SP. The customers have two options when giving us their data. One is an arbitrary staff_id_number assigned between the patient and the staff member by their old software. The other possibility is using an identifier_npi  number.  So if the file has one or the other on import  and the other column will be missing.

So I built an if statement to select the right query to run to check for dupe records. The problem is the that when I try to create the SP I get Invalid column name 'staff_id_number'. I  I know it is missing. That's why it is in an IF statement. Anyone have a suggesion how to get around the error?

	IF EXISTS(SELECT * FROM sys.columns
	WHERE Name = N'staff_id_number' AND OBJECT_ID = OBJECT_ID(N'if_us_clientMedProf'))
	BEGIN
		SELECT	@ErrorCount = Count(*)
		FROM	(select  facility_number,client_id_number, staff_id_number
				from  if_us_clientMedProf
				group  by facility_number,client_id_number, staff_id_number 
				having  count(*)  >  1) SubQ
	END
	ELSE
	BEGIN
		SELECT	@ErrorCount = Count(*)
		FROM	(select  facility_number,client_id_number, identifier_npi
				from  if_us_clientMedProf
				group  by facility_number,client_id_number, identifier_npi 
				having  count(*)  >  1) SubQ
	END

Open in new window

LVL 38
Jim P.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Dynamic SQL.

Create strings that contain the queries and then execute the desired string.


Kent
0
Jim P.Author Commented:
That worked for the the first set of queries.

The next chunk is a CTE. Can I pull that off with that too?

			WITH CTE
			AS
				(SELECT	*, SeqNum =
							Row_Number() OVER (Partition By facility_number,client_id_number, 
										staff_id_number ORDER BY  ROW_ID DESC)
				from if_us_clientMedProf)
				delete	from if_us_clientmedprof
				where	Row_Id in (SELECT	CTE.Row_Id
							FROM	CTE
							WHERE	SeqNum > 1)

Open in new window


--------------------------------------------------------------------------------------
What it looks like now:
	BEGIN
		SELECT	@SQL =	'SELECT	@ErrorCount = Count(*) ' +
						'FROM	(select  facility_number,client_id_number, identifier_npi ' +
						'from  if_us_clientMedProf ' +
						'group  by facility_number,client_id_number, identifier_npi  ' +
						'having  count(*)  >  1) SubQ ' 
	END
	exec @SQL

Open in new window

0
Scott PletcherSenior DBACommented:
All dynamic SQL is a royal pita.

You might consider always adding the missing/unused column to every table, so that the same SQL always works.  You can use ISNULL() to get the non-null value from the two columns.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim P.Author Commented:
The problem is I won't know which column will be missing until the the SP starts. These SP are going to be dynamically added to the the DB it is going to be run in.

So can I do dynamic SQL with a CTE?
0
Kent OlsenData Warehouse Architect / DBACommented:
My best suggestion here is to try it.  Older DBMS may not handle the CTE.

Just write any valid CTE for the test.
0
Scott PletcherSenior DBACommented:
Ah, so the table doesn't exist yet.

Sure, you can use a CTE in dynamic SQL.
0
Jim P.Author Commented:
I changed the one query to this. It parses out fine. But upon the create I'm getting the error below.

		SELECT	@SQL =	'SELECT	@ErrorCount = isnull(max(SubQ.SeqNum),0) ' +
						'FROM	(select  facility_number,client_id_number, identifier_npi ' +
						'SeqNum = ' +
							'		Row_Number() OVER (Partition By facility_number,client_id_number,  ' +
							'			staff_id_number ORDER BY  ROW_ID DESC)  ' +
						'from  if_us_clientMedProf ) SubQ ' +
						'WHERE SubQ > 1'

Open in new window



Msg 203, Level 16, State 2, Procedure if_US_ClientMedProf_validate_lookup, Line 43
The name 'SELECT      @ErrorCount = isnull(SubQ.SeqNum,0) FROM      (select  facility_number,client_id_number, identifier_npi SeqNum =             Row_Number() OVER (Partition By facility_number,client_id_number,                    staff_id_number ORDER BY  ROW_ID DESC)  from  if_us_clientMedProf ) SubQ WHERE SubQ > 1' is not a valid identifier.

I'm really getting to hate this.
0
Kent OlsenData Warehouse Architect / DBACommented:
SELECT      @SQL =      'SELECT      @ErrorCount = isnull(max(SubQ.SeqNum),0) ' +
            'FROM      (select  facility_number,client_id_number, identifier_npi ' +
            'SeqNum = ' +
            '            Row_Number() OVER (Partition By facility_number,client_id_number,  ' +
            '                  staff_id_number ORDER BY  ROW_ID DESC)  ' +
            'from  if_us_clientMedProf ) SubQ ' +
            'WHERE SubQ > 1'
                                         

Had to reformat to read it.  :)

It looks like line 2 should end with a comma.
0
Scott PletcherSenior DBACommented:
You can't use an undeclared variable in TSQL (the dynamic SQL has no declaration for @ErrorCount, because the caller's declaration for @ErrorCount does not "carry over" into the EXEC()).

You'll have to use "sp_executesql" rather than EXEC() if you want to pass variable value(s) and/or return variable value(s).


>> I'm really getting to hate this. <<

Yes, to quote myself:
"All dynamic SQL is a royal pita."
That's awkwardly worded, but what I meant is that using only dynamic SQL to do things, particularly in a stored proc, is a royal pita.

I'd be so annoyed I might even consider creating two separate procs, one with each column name, then having a "header" proc that simply called which detailed proc was valid.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Author Commented:
Finally got it to work.

Thanks for all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.