CODEWITHSUNDEEP
sqlsql-servert-sql
Kaja
Kaja

Reputation: 3057

Using a variable in insert command

I would like to use a variable in my INSERT command. This variable includes result value from a storedprocedure:

 declare @File as varbinary(max)
 exec @File=[dbo].[MySp]

enter image description here

but If I use @File in an INSERT command, another value of is written in table

insert into  [dbo].[Plots] values ('test', @File)

enter image description here

My Stored Procedure:

CREATE PROCEDURE [MySp]
AS
BEGIN

EXEC sp_execute_external_script @language = N'R'
    , @script = N'_RCODE_'
    , @input_data_1 = N'_INPUT_QUERY_'
    ,@output_data_1=N'OutputDataset'
--- Edit this line to handle the output data frame.
    WITH RESULT SETS (([plot] VARBINARY(max)));
END;

Upvotes: 1

Views: 2058

Answers (2)

Kev
Kev

Reputation: 16321

Your EXEC call is getting the result code of the SP, which is 0 for success, I suppose, in the absence of an explicit return statement.

See this answer for more details on how to capture actual data from your SP: https://stackoverflow.com/a/3963991/16777

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Your using of Stored Procedure is wrong. There is a recordset on first screenshot, but after execution exec @File=[dbo].[MySp] you don't have the recordset in variable @File. You got

@return_status

in @File

@return_status Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.

The right query can be like this:

declare @File as varbinary(max)
DECLARE @Table TABLE
(
    plot    VARBINARY(MAX)
)

INSERT @Table
exec [dbo].[MySp]

SELECT @File = MAX(plot)
FROM @Table

insert into  [dbo].[Plots] values ('test', @File)

Upvotes: 2

Related Questions