TSQL dynamic for multiple returns

Question

TSQL dynamic for multiple returns

Navigation

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

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 above example I need my result to be something like:

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

Someone could show me what the dynamic SQL would be like, because I've already seen examples on the internet that use sp_executeSQL but I could not figure out how to handle it when there is this case ... the feedback 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

asked by anonymous 22.04.2015 / 22:29

2 answers

1

You could do this as follows:

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 concat is available from SQL2012

22.04.2015 / 22:47

Error while placing image in ImageButton Variable does not call function, WebWorker, and Scope functions

score 2 · Accepted Answer

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

Here's 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