SQL Procedures: Can I use a table as a parameter for a procedure?

Asked

Viewed 160 times

4

I’m a beginner in procedures.

I am trying to insert data into a table using a procedure, where I get the table name, the column names and the values of these.

How to receive the types of data I want, such as a table or a column?
I’m thinking right about using a procedure for this?

Here’s an excerpt from the code:

CREATE PROCEDURE `inserirUsuario`(tableName varchar(45),column5 varchar 
(45),dado5 varchar(45),column6 varchar (45),dado6 varchar(45),column1 
varchar (45),dado1 varchar(45),column2 varchar (45),dado2 varchar(45), 
column3 varchar (45),dado3 varchar(45),column4 varchar (45),dado4 
varchar(45) )
BEGIN
DECLARE id int;
insert into endereco(column1, column2, column3, column4) values 
(dado1,dado2,dado3,dado4);

sql stored-procedures

  • Sqlserver, mysql ?

  • The information comes from where? From an application? From SQL itself? If the goal is just to insert data into a table, I don’t see why to use a Stored Procedure.

  • There is a simpler way to use parameter with table value. See https://docs.microsoft.com/pt-br/sql/relational-databases/tables/use-table-valued-parameters-database-engine

2 answers

0

You can even mount a generic trial that can insert data into any table, but this is not something common to do.

CREATE PROCEDURE [InserirUsuario]
( @tableName varchar(45)
, @column1   varchar(45)
, @dado1     varchar(45)
, @column2   varchar(45)
, @dado2     varchar(45)
) AS
BEGIN
  DECLARE @SqlCommand NVARCHAR(MAX)

  SET @SqlCommand = N'INSERT INTO ' + @tableName + '(' + @column1 + ',' + @column2 + 
') VALUES (''' + @dado1 + ''',''' + @dado2 + ''')'

  EXEC SP_EXECUTESQL @SqlCommand
END

In this case you would assemble a string with your command and then execute it, of course you would lack the field format question, but as I mentioned, it’s not common to create a generic precedent like this, mainly because each table can have a different amount of fields.

You could replace creating multiple parameter fields to use only an XML field that could have N column and value fields.

0

I found an article where a script is available that allows the generic creation of a table.

The script would be

CREATE FUNCTION fn_Table_Structure (@InputSQL AS NVARCHAR(4000), @TableName AS NVARCHAR(128) = NULL) 
RETURNS NVARCHAR(4000)
AS
BEGIN

DECLARE @SQL AS NVARCHAR(4000)
DECLARE @name NVARCHAR(128)
DECLARE @is_nullable BIT 
DECLARE @system_type_name NVARCHAR(128)
DECLARE @collation_name NVARCHAR(128)
DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10) -- CRLF

DECLARE CUR_Table CURSOR LOCAL FAST_FORWARD
FOR
    SELECT  name ,
            is_nullable ,
            system_type_name ,
            collation_name
    FROM    sys.dm_exec_describe_first_result_set(@InputSQL, NULL, NULL)
    WHERE   is_hidden = 0
    ORDER BY column_ordinal ASC 

OPEN CUR_Table

FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,
    @collation_name

SET @SQL = 'CREATE TABLE [' + ISNULL(@TableName, 'TableName') + '] ('
    + @NewLine

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL += @NewLine + '[' + @name + ']' + ' ' + @system_type_name
            + CASE WHEN @collation_name IS NOT NULL
                   THEN '  COLLATE ' + @collation_name + ' '
                   ELSE ''
              END + CASE WHEN @is_nullable = 0 THEN ' NOT NULL '
                         ELSE ''
                    END + ',' 
        FETCH NEXT FROM CUR_Table INTO @name, @is_nullable, @system_type_name,
            @collation_name
    END

SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + @NewLine + ')'

CLOSE CUR_Table
DEALLOCATE CUR_Table

RETURN @SQL
end

I believe it runs what you want. Below the link to the article in full.

Article link

Browser other questions tagged sql stored-procedures

You are not signed in. Login or sign up in order to post.