Dynamic TSQL for multiple returns

Asked

Viewed 100 times

1

I need to make multiple inserts that will depend on an existing result in a table like this:

--CRIA TABELA #CONTATO
CREATE TABLE #CONTATO(
    NOME VARCHAR(100) NULL,
    TELEFONE VARCHAR(50) NULL
);
--INSERE 2 LINHAS NELA
INSERT INTO #CONTATO VALUES('JOAO','11-1111-1111');
INSERT INTO #CONTATO VALUES('MARIA','22-2222-2222');

Imagine who in the example above I need my result to be something like:

PRINT 'MEU NOME É '@NOME+' E MEU TELEFONE É '+@TELEFONE;

Could someone show me what dynamic SQL I would do, because I’ve seen examples on the internet that use the sp_executeSQL but I couldn’t figure out how to manipulate when there’s this case... the return I’d like to have would be something like

--MEU NOME É JOAO E MEU TELEFONE É 11-1111-1111
--MEU NOME É MARIA E MEU TELEFONE É 22-2222-2222
sql sql-server tsql
  • 1

    It’s not very clear what you want. The idea is to execute what, exactly?

  • I need that, for each row that exists inside the table #contact, I can through a sp_executeSQL() take information from this line, execute a command; then pass to the next and bla bla bla...

2 answers

2


The way to use parameters with sp_executeSQL() is really not very intuitive, it is better to even read the microsoft page itself on the subject: sp_executesql (Transact-SQL)

Here is the solution:

--CRIA TABELA #CONTATO
CREATE TABLE #CONTATO(
    NOME VARCHAR(100) NULL,
    TELEFONE VARCHAR(50) NULL
);
--INSERE 2 LINHAS NELA
INSERT INTO #CONTATO VALUES('JOAO','11-1111-1111');
INSERT INTO #CONTATO VALUES('MARIA','22-2222-2222');

DECLARE @Nome nvarchar(50)
DECLARE @Telef nvarchar(20)
DECLARE @sql nvarchar(max)
DECLARE @paramDefinition nvarchar(max)

SET @sql = 'PRINT (''MEU NOME É '' + @pNome +  '' E MEU TELEFONE É '' + @pTelef);'
SET @paramDefinition = '@pNome nvarchar(10), @pTelef nvarchar(10)'

DECLARE CUR CURSOR FOR
SELECT NOME,TELEFONE FROM #CONTATO
OPEN CUR
FETCH NEXT FROM CUR into @nome,@telef

WHILE @@FETCH_STATUS= 0
BEGIN
    EXEC sp_executesql  @sql, @paramDefinition
                        ,@pNome  = @Nome
                        ,@pTelef = @Telef

    FETCH NEXT FROM CUR INTO @Nome,@Telef
END

CLOSE CUR
DEALLOCATE CUR
DROP TABLE #CONTATO

  • Thanks partner, I didn’t know I could use cursors and how to apply while inside SQL... I will give a researched to improve the solution that I create, live and learn!

1

You could do it this way:

Insert Into Nome_Da_Tabela (campo1, campo2, campo3)
       Select Nome_Da_Tabela2.campo1, Nome_Da_Tabela2.campo2, Nome_Da_Tabela2.campo3
         From Nome_Da_Tabela2

Or:

Select concat('Insert Into Nome_Da_Tabela (campo1, campo2, campo3) values (',
               campo1, campo2, campo3,')')
  From Nome_Da_Tabela2

Note: I believe that the Concat is available from SQL2012

Browser other questions tagged sql sql-server tsql

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