"Aaron Bertrand [SQL Server MVP]" wrote:

> > I'm trying not to have to use the EXEC sp_executesql @SQL and wondered if
> > there is a way around my code.
>
> Nope, you cannot evaluate an object name at runtime that way without using
> dynamic SQL.
>
> http://www.sommarskog.se/dynamic_sql.html
>
>
>

> Hello.
>
> A have a Stored Procedure that has one passed in variable.
> I need that variable to append to a table name that needs to be dropped if
> it exists.
>
> My dynamic SQL looks like this:
>   Create Procedure MyProcedure
>     @NetworkID Char(2)    -- Passed in parameter
>
>   IF exists (select * from sysobjects where Name = 'Table1'  + @NetworkID
> and
>    Type = 'U')
>    BEGIN
>     DROP TABLE [Table + @NetworkID]
>    END
>
> The code compiles correctly and the IF statement is executed correctly but I
> get the following error message if the table exists:
>   "Cannot drop the table 'Table1 + NetworkID' because it does not exists in
> the
>    system category."
>
> I'm trying not to have to use the EXEC sp_executesql @SQL and wondered if
> there is a way around my code.
>
> TIA.
>
> Rita

"Stu" wrote:

> Table names can not be variables; you'll have to use dynamic SQL for
> this.
>
>
> RitaG wrote:
> > Hello.
> >
> > A have a Stored Procedure that has one passed in variable.
> > I need that variable to append to a table name that needs to be dropped if
> > it exists.
> >
> > My dynamic SQL looks like this:
> >   Create Procedure MyProcedure
> >     @NetworkID Char(2)    -- Passed in parameter
> >
> >   IF exists (select * from sysobjects where Name = 'Table1'  + @NetworkID
> > and
> >    Type = 'U')
> >    BEGIN
> >     DROP TABLE [Table + @NetworkID]
> >    END
> >
> > The code compiles correctly and the IF statement is executed correctly but I
> > get the following error message if the table exists:
> >   "Cannot drop the table 'Table1 + NetworkID' because it does not exists in
> > the
> >    system category."
> >
> > I'm trying not to have to use the EXEC sp_executesql @SQL and wondered if
> > there is a way around my code.
> >
> > TIA.
> >
> > Rita
>
>