CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012sql-insert
Advice Me
Advice Me

Reputation: 79

SQL Server INSERT INTO using EXECUTE

I am trying to insert a few values into a table using EXECUTE statement.

I have found few examples, however, those does not answer my question.

Note: sources given below are code from the procedure. All mentioned variables was declared and initialized.

First way I tried:

INSERT INTO [master].[dbo].[table_1] (column1, column2)
EXEC sp_executesql @sql, N'@object id INT, @crlf CHAR(2)', @object_id, @crlf, 1

results in:

Error: Procedure or function has too many arguments specified.

And I completely understand why I am getting this. Because sp_executesql does not allow to have more than 4 arguments, and it cannot recognise which goes where.

Second try (where I only want to get the output using execute statement into the variable, and then put that into the table):

DECLARE @sql_in nvarchar(max)
DECLARE @sql_out nvarchar(max)
SET @sql_in = @sql + ',' 
    + N'@object_id INT, @crlf CHAR(2),' 
    + CONVERT(varchar(100), @object_id) + ',' + @crlf
PRINT @sql_in
EXEC sp_executesql @sql_in, N'@sql_out nvarchar(max) out', @sql_out out

And I get:

Must declare scalar variable "@crlf"

and actually this variable is declared in the top of this procedure, but this one is not that clear for me, why it still complains...

What would be the most sufficient way to get the execution statement and the number(additional value) into the table?

Upvotes: 0

Views: 10467

Answers (2)

himadri
himadri

Reputation: 638

Please try according with following example

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
   FROM AdventureWorks2012.HumanResources.Employee
   WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

OBSERVATION

  1. SQL query needs a parameter @level that is why we have declare it as parameter

  2. again the SQL query returns the output at @max_titleOUT. So we also have declare it as parameter. so the final parameter list is 

    SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

  3. now we have to send the @level value to dynamic query that's why we use @level = @IntVariable that sends the @IntVariable value to @level

  4. Finally by @max_titleOUT = @max_title OUTPUT we take the output of dynamic query that saves at @max_titleOUT, copy the value to @max_title

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

As an only workaround I can suggest using a temporary table. Should be quite simple:

SELECT TOP (0) column1
INTO #TemporaryResults
FROM [master].[dbo].[table_1];

INSERT INTO #TemporaryResults
EXECUTE sp_executesql @sql, N'@object_id INT, @crlf CHAR(2)', @object_id, @crlf;

INSERT INTO [master].[dbo].[table_1] (column1, column2)
SELECT column1, 1
FROM #TemporaryResults;
  1. Create a copy of column1 from [table_1] column1
  2. Insert records, produced by SP into it
  3. Insert records to actual table

Upvotes: 0

Related Questions