April 22, 2013 at 9:10 pm
Hi,
I am following query in any database, the table is exists, but the column my be exists in some database.
But am keep on getting the error 'Invalid column name mark2..Invalid column name mark3..Invalid column name mark4..Invalid column name mark5..Invalid column name mark6)
this is just example.
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark2')
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark3')
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark4')
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark5')
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'student' AND COLUMN_NAME = 'mark6')
BEGIN
select mark2 from student
select mark3 from student
select mark4 from student
select mark5 from student
select mark6 from student
end
If am using IF exists, if the statement succeed it should go to select statement. But it directly goes to select statement and throughing an error.
Please guide me this ASAP.what may be solution. I should not use dynamic query.. this is final time error we are getting.. this should go live today!!
Regards,
antony
April 22, 2013 at 9:21 pm
To get a good answer we will need your actual CREATE TABLE statements (obfuscate the column names if you have to) and your actual IF/THEN script.
My guess is two things - either an incorrectly constructed IF/THEN statement, or the tables are in a different schema.
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark2')
BEGIN
SELECT mark2 FROM dbo.student;
END;
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark3')
BEGIN
SELECT mark3 FROM dbo.student;
END;
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark4')
BEGIN
SELECT mark4 FROM dbo.student;
END;
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark5')
BEGIN
SELECT mark5 FROM dbo.student;
END;
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark6')
BEGIN
SELECT mark6 FROM dbo.student;
END;
April 22, 2013 at 9:33 pm
Its works Thanks a lot.
if only need select statement, am using update statement using mark2 and mark3 fields.
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark2')
begin
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'mark3')
begin
UPDATE student
SET comments = CASE WHEN LEN(mark2)>0 AND mark3 IS NULL THEN COALESCE(comments +'. ','')+ ' Cranial N. I,II,III,IV,VI: ' + COALESCE(mark2 +'.','')
WHEN LEN(mark3)>0 AND mark2 IS NULL THEN COALESCE(comments +'. ','')+ ' Cranial N. I,II,III,IV,VI: ' + COALESCE(mark3 + '.','')
WHEN LEN(mark2)>0 AND LEN(mark3)>0 THEN COALESCE(comments +'. ','')+ ' Cranial N. I,II,III,IV,VI: ' + COALESCE(mark2,'') + COALESCE(+'; '+ mark3 + '.','')
ELSE comments END
WHERE cranial = 'n'
-- cranial is checked
UPDATE student
SET comments = COALESCE(comments +'.','')+ ' Cranial N. I,II,III,IV,VI: normal. '
WHERE cranial = 'Y'
end
end
I am concatenating and moving mark2 and mark3 value to comments field.
But still I am getting invalid column name mark2 .. invalid column name 3
Please give your input..
Regards,
tony
April 22, 2013 at 9:55 pm
As I said before, for an accurate answer to your specific error we will need the actual CREATE TABLE statement for your student table. (Please see http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url])
Without that we can only guess.
Edit: Guess #2 - explicitly specify the schema of the table when updating. i.e. "UPDATE dbo.student", not "UPDATE student"...
April 23, 2013 at 3:24 am
-- details of tables called [student]
-- or columns called [mark2] etc
-- in the 'current' db
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('mark2', 'mark3', 'mark4', 'mark5', 'mark6')
OR TABLE_NAME = 'student'
-- what columns does the [student] table in the current db, default schema, have?
SELECT TOP(1) *
FROM student
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply