Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How do i validate tables in dynamic sql procedure?
This is for homework, the reading & provided are exteremly light. one of the homework problems requires me to pass a column and table varchar to a procedure.
for which i have....
@col varchar(max),
@table varchar(max)
AS
declare @sql_string varchar(max)
if
set @sql_string = 'select ' + @col + ' from ' + @table;
exec (@sql_string);
Now, the next part of the assignment has me validating the @table variable is in fact a table within the database and not some inserted code.(delete, drop, insert, etc.)
I changed the @table to a sysname variable but that does not seem right to me.
3 Answers
- 1 decade agoFavorite Answer
To confirm a name is a table:
declare @pString varchar(128)
select @pString = 'MyTable'
if
(select count(*) as DELTA from sysobjects where name = @pString and xtype = 'U') > 0
print 'yes'
else
print 'no'
GO
- TheMadProfessorLv 71 decade ago
Most DBMS have system tables available to system administrator users that, among other things, can serve as a data dictionary for just such a task. The actual system tablename and the like vary - in DB2, you would check the SYSTABLES and SYSCOLUMNS tables to ensure that the @table and @col values were valid.
- Serge MLv 61 decade ago
Try this
IF exists(select * from information_schema.tables where table_name=@table)
begin
<your code>
end
else
<return error>
