April 7, 2004 at 6:24 pm
Short question.. Is there a way to execute a select statement within a stored proc and not have the column names output?
I am dynamically adding a column to a table (I have created a temp table that contains the list of tables and new columns). Before I add it, I check to see if it already exists, using a select * from syscolumns... and then check the value of @@rowcount.
SET FMTONLY ON will not return the data, however my create temp table does not work.
Thanks!
April 7, 2004 at 10:48 pm
information_schema.columns is one you needed.
if exists(select * from information_schema.columns where Table_name='YourTable' and column_name = 'YourColumn')
print 'add column'
else
print 'exists'
April 8, 2004 at 3:45 pm
I still have the problem. I can do the select statement, but the results are still displaying to the results pane..
Below is part of the code I'm running and the full result set..
Code snippet...
SET @findSQL = 'SELECT table_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @vOwner AND TABLE_NAME = @vTblname AND COLUMN_NAME = @vColname'
SET @parmdef = N'@vOwner varchar(3), @vTblname varchar(64), @vColname varchar(64)'
EXECUTE sp_executesql @findSQL, @parmdef, @vOwner = @owner, @vTblname = @oldtbl, @vColname = @cmscol
IF @@ROWCOUNT = 0
BEGIN
SET @alterSQL = @initalterSQL + @oldtbl + ' ADD ' + @cmscol + @addcoltype
EXECUTE (@alterSQL)
print @@ERROR
END
Full Results of sp...
Table: VETINFO
Adding CID col.
table_name
--------------------------------------
VETINFO
Finish adding CID col.
Adding CID Type cols.
Table does not require type column.
Finish adding CID Type cols.
The stuff in RED is what I'm trying to get rid of.. Is there anyway, other than using SET FMTONLY ON, to suppress the result set of the SELECT?
Thanks, Elizabeth
April 8, 2004 at 7:18 pm
Little change to the code.
SET @findSQL = 'declare @suppress sysname;SELECT @suppress=table_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @vOwner AND TABLE_NAME = @vTblname AND COLUMN_NAME = @vColname'
SET @parmdef = N'@vOwner varchar(3), @vTblname varchar(64), @vColname varchar(64)'
EXECUTE sp_executesql @findSQL, @parmdef, @vOwner = @owner, @vTblname = @oldtbl, @vColname = @cmscol
IF @@ROWCOUNT = 0
print 'Not exists'
ELSE
print 'Exists'
April 12, 2004 at 8:49 am
That worked!!
Thanks for your help!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply