[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1072
  • Last Modified:

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

0
Jim P.
Asked:
Jim P.
  • 4
  • 3
  • 3
5 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Jim P.Author Commented:
Finally got it to work.

Thanks for all the help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now