Create Stored Procedure to create a new table - SQLServer 2014

Question

Create Stored Procedure to create a new table - SQLServer 2014

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I need to create a procedure that creates a new table from the selection of one or several entities. The selection of 'IdEntity' I do from a gridView in the presentation layer in Visual Studio.
What I have is this:

CREATE PROCEDURE [dbo].[sp_LlenarRecursosEntidades] 

@IdEntidad int

AS
BEGIN

declare @_identidad int = -1
if @IdEntidad is null set @IdEntidad = @_identidad

select E.IdEntidad, E.NombreEntidad from Entidad E
where E.IdEntidad = @IdEntidad

if object_id ('Resources') is not null
drop table Resources
begin try
    select distinct E.IdEntidad as ResourceID, E.NombreEntidad as ResourceName 
    into Resources
    from Entidad E
    where E.IdEntidad = @IdEntidad
            order by NombreEntidad 
end try
begin catch
    return -1
end catch
END

The problem is that the table stores only one entity when my code passed the 'IdEntity' of several Entities. Clarifications
In my DB I have the Entity table (EntityID "int", EntityName "varchar")

sql-server-2014 stored-procedure

asked by Pablo Matias 06.07.2017 в 16:29

source

2 answers

2

Use two procedures, the first to create the table:

CREATE PROCEDURE dbo.Crear_entidades

AS
BEGIN 
  SET NOCOUNT ON; 

  DECLARE @SQL NVARCHAR(MAX); 

  SELECT @SQL = 'CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);'; 

  EXEC sp_executesql @sql; 
END
GO

And another to insert data. Which is the same, but instead of the Query to create you have to insert the insert.

Come on, what you have to do is insert the normal SQL querys into the "variable" @SQL, and then execute it.

Take a look at this: link

answered by 06.07.2017 в 16:39

Make cast of complete apps Detect Android 4.0 and 7.0 language

score 0 · Accepted Answer

Well, I could solve it using 3 stored procedures, maybe it is not very optimal but it was useful for the moment.
Since I need the data that the table will show me, it will change every time the method I'm using is called in my presentation layer, I created a stored procedure that removes the Resources table if it already exists:

CREATE procedure [dbo].[sp_EliminarTablaResources] 
as
if OBJECT_ID('Resources') is not null
drop table Resources

Then I created another one so that I could recreate the Resources table:

CREATE PROCEDURE [dbo].[sp_CrearTablaResources] 
-- Add the parameters for the stored procedure here

AS
BEGIN
SET NOCOUNT 
create table Resources(
Id int Primary key Identity(1,1),
ResourceId int not null,
ResourceName varchar(max) not 
END

And finally I created another sp so that it fills the Resources table with the data of the rows that I selected in my gridview of the presentation layer but that does not load the data if it already exists:

CREATE PROCEDURE [dbo].[sp_InsertIntoResources] 
-- Add the parameters for the stored procedure here
@IdEntidad int
AS
declare @NombreEntidad varchar(max)
BEGIN

SET NOCOUNT ON;
if exists(
select ResourceId, ResourceName from Resources
    where ResourceId = @IdEntidad)
    Begin
    --set @NombreEntidad = (select NombreEntidad from Entidad where IdEntidad = @IdEntidad)
        raiserror('Err -998: El registro ya existe.', 1, 10)
        return -998
    end
    Else 
    begin
    set @NombreEntidad = (select NombreEntidad from Entidad where IdEntidad = @IdEntidad)
        insert into Resources (ResourceID, ResourceName)
        values (@IdEntidad, @NombreEntidad)
    end
END