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, Apr 04 '17 at 09:34

score 2 · Accepted Answer · answered Apr 04 '17 at 10:58

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)

score 1 · Answer 2 · edited May 23 '17 at 12:25

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 '17 at 12:25

Community

1
1

answered Apr 04 '17 at 09:46

Kev

15,899
15
79
112

Using a variable in insert command

2 Answers2