Using a variable in insert command

Question

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]

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

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

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;

Are you sure its not an out parameter in your stored proc? If that is the case your stored proc syntax is not correct. — Igor
– Igor, Commented Apr 4, 2017 at 9:34

Denis Rubashkin · Accepted Answer · 2017-04-04 10:58:10Z

2

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)

answered Apr 4, 2017 at 10:58

Denis Rubashkin

2,2311 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:25:48Z

1

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

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Apr 4, 2017 at 9:46

Kev

16.5k18 gold badges83 silver badges116 bronze badges

Collectives™ on Stack Overflow

Using a variable in insert command

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related