Hi all,

I have the following script. Why can't i use @tbname when doing drop
or create?

Thanks,
Lorenzo

--

use babel

declare @tbname varchar(500)
set @tbname = 'users'

if exists (select * from information_schema.tables where table_name =
@tbname)
begin
print 'Table ' + @tbname + ' exists, dropping'
drop table users
end

begin
print 'Table ' + @tbname + ' does not exist, creating'
create table users
(
id int primary key not null,
firstname nvarchar(255) not null,
lastname nvarchar(255) not null,
email nvarchar(255) not null,
password nvarchar(255) not null,
salt nvarchar(255) not null,
created_on datetime default getdate() not null,
updated_on timestamp
);
end

That is just the way it is. If this is important to you, you can use dynamic SQL. Build the command
you want to execute in a variable and then execute the contents of the variable:
EXEC(@sql)

You might want to check out the article on dynamic SQL at sommarskog.se.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<lbolognini@gmail.com> wrote in message
news:6e026cb2-035d-46c1-8417-14a599d07ddb@r66g2000hsg.googlegroups.com...
> Hi all,
>
> I have the following script. Why can't i use @tbname when doing drop
> or create?
>
> Thanks,
> Lorenzo
>
> --
>
> use babel
>
> declare @tbname varchar(500)
> set @tbname = 'users'
>
> if exists (select * from information_schema.tables where table_name =
> @tbname)
> begin
> print 'Table ' + @tbname + ' exists, dropping'
> drop table users
> end
>
> begin
> print 'Table ' + @tbname + ' does not exist, creating'
> create table users
> (
> id int primary key not null,
> firstname nvarchar(255) not null,
> lastname nvarchar(255) not null,
> email nvarchar(255) not null,
> password nvarchar(255) not null,
> salt nvarchar(255) not null,
> created_on datetime default getdate() not null,
> updated_on timestamp
> );
> end

Hi
What error do you get? I see that you drop users table and do not use a
variable.



<lbolognini@gmail.com> wrote in message
news:6e026cb2-035d-46c1-8417-14a599d07ddb@r66g2000hsg.googlegroups.com...
> Hi all,
>
> I have the following script. Why can't i use @tbname when doing drop
> or create?
>
> Thanks,
> Lorenzo
>
> --
>
> use babel
>
> declare @tbname varchar(500)
> set @tbname = 'users'
>
> if exists (select * from information_schema.tables where table_name =
> @tbname)
> begin
> print 'Table ' + @tbname + ' exists, dropping'
> drop table users
> end
>
> begin
> print 'Table ' + @tbname + ' does not exist, creating'
> create table users
> (
> id int primary key not null,
> firstname nvarchar(255) not null,
> lastname nvarchar(255) not null,
> email nvarchar(255) not null,
> password nvarchar(255) not null,
> salt nvarchar(255) not null,
> created_on datetime default getdate() not null,
> updated_on timestamp
> );
> end

On Sep 10, 12:20 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.co m> wrote:
> That is just the way it is. If this is important to you, you can use dynamic SQL. Build the command
> you want to execute in a variable and then execute the contents of the variable:
> EXEC(@sql)
>
> You might want to check out the article on dynamic SQL at sommarskog.se.

Great, thanks a lot. I've modified the statement in this way:

declare @drop_cmd varchar(500)
set @drop_cmd = 'drop table ' + @tbname
exec(@drop_cmd)

This has the advantage that i have to change the table name only once
and it would be easier to generate scripts to automate database
creation/destruction/inserts to play around when i'm in testing.

Thanks,
Lorenzo