• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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
0
cflores89
Asked:
cflores89
  • 2
1 Solution
 
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
 
ErnariashCommented:
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
 
cflores89Author Commented:
Perfect!  I actually use teh schema.Tables suggestion
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.

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