How to Validate Tables in Dynamic SQL

This is for homework, the reading & provided notes  are exteremly light. one of the homework problems requires me to pass a column and table varchar to a procedure.

for which i have....
create proc proc_test
@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.

Any insight is appreciated.....or a go here read this would be grand.thanks
cflores89Asked:
Who is Participating?
 
ErnariashConnect With a Mentor Commented:
Using the dbo.sysobjects the  type should be 'U' and not 'D', please see code.
You have other methods to list tables within the database like
declare @table nvarchar(250)
set @table='List1'
SELECT * FROM information_schema.Tables
Where Table_Name =@table

SELECT * FROM sys.Tables
Where Name =@table

declare @table nvarchar(max)
set @table='dbo.List1'
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@table) AND type in ('U'))
BEGIN
---Your code
END

Open in new window

0
 
ErnariashCommented:
You could use systems views in sql 2005 and 2008
or SELECT name FROM dbo.sysobjects WHERE xtype = 'U'  see code
 

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@table) AND type = 'D')
BEGIN
....your code 
END
 

Open in new window

0
 
cflores89Author Commented:
Perfect!  I actually use teh schema.Tables suggestion
0
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.

All Courses

From novice to tech pro — start learning today.