In the process of trying to do an online exam system, to manage each student's exam information, consider creating a temporary data table named for each student.
Creating a table dynamically in a stored procedure is good if you don't use parameters. The method is as follows:
ALTER procedure [dbo].[ZXKS_GETSCORE]
AS
begin transaction
--创建临时表,直接命名
create table temp_tablename
(
id int primary key,
da varchar(300),
fs int
)
declare @count int
select @count=@@error
if(@count=0)
commit transaction
else
rollback transaction
If you want to use an incoming parameter as a data table name, you will experience a problem: if the table name is a number then SQL SERVER 2005 does not recognize it and prompts for an error. You must precede the table name of a number with a character that is not numeric. The stored procedure looks like this:
/*
createtable '123456'
*/
alter procedure createtable
@xuehao varchar(20)
as
declare @tablename varchar(20)
set @tablename='temp'+@xuehao
exec('Create Table '+@tablename+'
( name nvarchar(15),
address nvarchar(50)
)')
and use the Exec method to create a method using the parameter as the data table name, using the method described earlier does not work.