Dynamic Query

Hi friends,
this my query
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from @.xi_errorDb.dbo.sysobjects where id
= object_id([dbo].[@.xi_tableName]) and OBJECTPROPERTY(id, IsUserTable) = 1) '
select @.paramlist = '@.xi_errorDb varchar(200),
@.xi_tableName varchar(200)'
sp_executesql @.sql,@.paramlist,@.i_errorDb,@.i_tableName
I am getting syntax error. Please help me to solve this.
thanks
vanithaShould be something like this:
select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
'.dbo.sysobjects where id
= object_id([dbo].[' + @.xi_tableName + ']) and OBJECTPROPERTY(id,
IsUserTable) = 1)'
sp_executesql @.sql
But suggestable to use the INFORMATION_SCHEMA Views:
select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
'.INFORMATION_SCHEMA.TABLES ' +
'Table_Name like ' + CHAR(39) + @.xi_tableName +
CHAR(39)
sp_executesql @.sql
HTH, Jens Suessmeyer.|||still i am getting the same error
"Jens" wrote:

> Should be something like this:
> select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
> '.dbo.sysobjects where id
> = object_id([dbo].[' + @.xi_tableName + ']) and OBJECTPROPERTY(id,
> IsUserTable) = 1)'
> sp_executesql @.sql
> But suggestable to use the INFORMATION_SCHEMA Views:
>
> select @.SQL = 'if exists (select * from ' + @.xi_errorDb +
> '.INFORMATION_SCHEMA.TABLES ' +
> 'Table_Name like ' + CHAR(39) + @.xi_tableName +
> CHAR(39)
> sp_executesql @.sql
>
> HTH, Jens Suessmeyer.
>|||Could you please post the error ? The best thing would be also to Print
out the produced SQL with Print @.Sql before executing it.
Jens Suessmeyer.|||when the query is printed and executed its executing, but with sp_executesql
its throwing "syntax error near sp_executesql"
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_tableName +
CHAR(39) + ') drop table ' + @.i_errorDb + '.dbo.'+@.i_tableName
print @.sql
sp_executesql @.sql
"Jens" wrote:

> Could you please post the error ? The best thing would be also to Print
> out the produced SQL with Print @.Sql before executing it.
> Jens Suessmeyer.
>|||You have to EXEC the stored procedure if you are having just once batch
use habe to split the batch via GO to execute a procedure without the
word EXEC or you place an execute (whch shoudl be the prefered method)
in front of the procedurename, or you use another syntax with just the
EXEC word (sp_executesql required an nvarchar, so you want to use this
further you have to change the @.sql variable to nvarchar)
declare @.i_errorDb varchar(200)
declare @.i_tableName varchar(200)
Declare @.SQL varchar(4000)
Declare @.ParamList varchar(4000)
select @.i_errorDb = 'test'
select @.i_tableName = 'employee'
select @.SQL = 'if exists (select * from ' + @.i_errorDb +
'.INFORMATION_SCHEMA.TABLES where ' +
'Table_Name like ' + CHAR(39) + @.i_tableName +
CHAR(39) + ') drop table ' + @.i_errorDb + '.dbo.'+@.i_tableName
print @.sql
EXEC(@.SQL)
--OR
--EXEC sp_executesql @.sql
HTH, jens Suessmeyer.