Re: Testing Table Existence - DB name as variable ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 06/27/04 

Date: Sun, 27 Jun 2004 20:11:02 +0530

Oh Rob,

    You are trying to mix dynamic SQL and Static SQL.
    You cannot do that. Your Else condition doesnt have a
    corresponding IF condition. thats why you are getting the error.

    To learn more abt dynamic SQL read

    http://www.sommarskog.se/dynamic_sql.html 

-- 
Roji. P. Thomas
SQL Server Programmer
"rob" <rwc1960@bellsouth.net> wrote in message
news:PKzDc.129$O3.27@bignews2.bellsouth.net...
> I am doing something wrong in the code below...
>
> This section fails within the cursor... I am attempting to check for
> existence of a table in the database names I am looping through...
>
> As is, Error is Incorrect syntax near keyword 'ELSE'
> If I comment out  from BEGIN to the END, Error is Incorrect syntax near
')'
>
> Thanks !
>
> DECLARE @sql as varchar(8000)
> DECLARE @SalespersonDB as varchar(8000)
>
> set @SalespersonDB = 'EJ'
> set @sql = ' if exists (select * from dbo.sysobjects where id =
object_id(['
> + @SalespersonDB + '].[dbo].[tblCustPrClChanlSlsREP]))'
>    print @sql
>    Exec(@sql)
> BEGIN
>  Print 'Do nothing'
> END
> ELSE
> BEGIN
>   Print 'Do something'
> End
>
>

Relevant Pages

  • Please Help with Transaction + Alter Procedure statement
    ... I am struggling with the following SQL. ... Incorrect syntax near the keyword 'PROCEDURE'. ... Must declare the variable '@mySomething'. ...
    (microsoft.public.sqlserver)
  • Re: Testing Table Existence - DB name as variable ?
    ... DECLARE @sql as varchar ... DECLARE @SalespersonDB as varchar ... Exec sp_executesql @sql, N'@cnt nvarcharOUTPUT', @cnt OUTPUT ... Your Else condition doesnt have a ...
    (microsoft.public.sqlserver.programming)
  • Re: Testing Table Existence - DB name as variable ?
    ... DECLARE @sql as nvarchar ... SQL Server MVP ... Your Else condition doesnt have a ...
    (microsoft.public.sqlserver.programming)
  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • SQL SERVER 2000 domain changes for users.
    ... In the SQL server, we have ... how to change users login name in sql 2000 SP4 after domain change... ... DECLARE @charvalue varchar ... -- temporary srvrole access table ...
    (microsoft.public.sqlserver.server)