Must declare the scalar variable when using a time variable in sql server

0
votes

I am trying to execute a statement in order to attach a variable to a table name. I am new to sql and I couldn't find a solution to this issue online. 

Declare @t time
Set @t='12:00:00.1'
Declare @ttable varchar (255)
Set @ttable='Temp'+(Select top 1 ID from mtable)

    Exec('Insert into '+@ttable+'(Timestamp) values ('+'@t'+')')

Whenever I run this query I get the following error:

Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@t".

I am not sure what exactly I am running on. I just opened SQL Server Management Studio (SQL Server 2008 R2) and created a database and tables. Any help is much appreciated.

2
sqlsql-serversql-server-2008-r2exec
Drop the quotes around @t in the exec? – Sloan Thrasher
Edit your question and provide sample data, desired results, and explain what you are trying to do. "attaching a variable to a table name" is not sensible. – Gordon Linoff

2 Answers

1
votes

The correct way to call a SQL statement dynamically is to use sp_executesql and parameters. Assuming your table already exists, you would do:

Declare @t time;
Set @t = '12:00:00.1';

Declare @tablename varchar(255);

Set @tablename = 'Temp'+ (Select top 1 ID from mtable);

declare @sql nvarchar(max) 
set @sql = 'Insert into @tablename(Timestamp) values (@t)';

set @sql = replace(@sql, '@tablename', @tablename);  -- alas, cannot use parameters for table names

exec sp_executesql @sql, N'@t time', @t = @t;

I do think it is a poor database design that is constructing table names like this. Instead, you should have a single table and an additional column for the id.

0
votes

You can try this

Declare @t time
declare @tVar varchar(max)
Set @t='12:00:00.1'
set @tVar=CONVERT(VARCHAR,@t)
Declare @ttable varchar (255)
Set @ttable='Temp'+(Select '1')

    --PRINT('Insert into '+@ttable+'(Timestamp) values ('+CONVERT(VARCHAR,@t)+')')
    EXEC  ('Insert into '+@ttable+'(Timestamp) values ('+@tVar+')')