I am trying to create a list of tables whose names will be preceded by the user's name. For this I have created one stored procedure
with the following code.
STORED PROCEDURE
CREATE PROCEDURE [dbo].[CrearDatosUsuario]
@UserName varchar(50)
AS
GO
/**/---- *TABLAS* ----/**/
----| CREATE TABLE UserName.Tareas |----
CREATE TABLE [dbo].[@UserName + Tareas](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Titulo] [varchar](50) NOT NULL,
[Tiempo] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----| CREATE TABLE UserName.Estados |----
CREATE TABLE [dbo].[@UserName + Estados](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Valor] [float] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----| CREATE TABLE UserName.TareasEstados |----
CREATE TABLE [dbo].[@UserName + TareasEstados](
[IDTareas] [int] NOT NULL,
[IDEstados] [int] NOT NULL
) ON [PRIMARY]
GO
----| CREATE TABLE UserName.Descripciones |----
CREATE TABLE [dbo].[@UserName + Descripciones](
[ID] [int] NOT NULL,
[Descripcion] [varchar](500) NOT NULL
) ON [PRIMARY]
GO
The stored procedure
runs without errors but when I check the database the tables don't have the username.
Expected result:
- Boards
- dbo.JohnTasks
- bod.JuanStates
- dbo.JohnTasksStates
- dbo.JuanDescriptions
Obtained result:
- Boards
- dbo.@UserName + Tasks
- dbo.@UserName + States
- dbo.@UserName + TasksStates
- dbo.@UserName + Descriptions
It is not valid to do it the way you do, unfortunately you must fall into a dynamic SQL statement. Suppose you want to create a table
JuanTareas
, what you have to do is build the creation statement in a variable and execute it later, let's see:In
@SQL
we have our create query, which we will execute usingsp_executesql
. Theselect
end is simply to verify that the table is correctly created.