Using EXEC command as INSERT VALUE in SQL

Question

I want to be able to do something like this: My stored proc will always return a number (tinyint)

INSERT INTO CustomerSelections
           ([draw_date]
           ,[val1]
           ,[val2]
           ,[val3]
           ,[val4]
           ,[val5]
           ,[val6])
     VALUES
           (
           '2013-07-05'
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           ,EXEC GenerateRandomNumbers 1, 49, 1, 0
           )

But i am not aware of how to get the values in there?

UPDATE: I tried something like this:

I put the passed values into a stored proc called:

ALTER PROCEDURE [dbo].[GetUniqueLottoNumber] 

AS
BEGIN

DECLARE @return_value int

EXEC    @return_value = [dbo].[GenerateRandomNumbers]
        @StartNumber = 1,
        @EndNumber = 49,
        @QuantityToOutput = 1,
        @AllowDuplicates = 0

END

Then did this:

CREATE TABLE #tmp (Number TINYINT)
DECLARE @q nvarchar(4000)
DECLARE @return_value int
SET @q = 'EXEC  @return_value = [dbo].[GetUniqueLottoNumber]';

INSERT INTO  #tmp (Number)
EXEC sp_executesql @q

But it doesnt like it when i put @q in the insert values.

GenerateRandomNumbers should be a function and not a stored procedure. Then this question will almost solve itself. Problem is that you have not shown the code for your stored procedure, so we are unable to rewrite your code — t-clausen.dk
– t-clausen.dk, Commented Jul 21, 2013 at 13:58

Luis LL · Accepted Answer · 2013-07-21 12:55:58Z

why do you need to call the procedure 6 times? Why can't call the SP with a parameter of how many responses do you want?

anyway try something like this.

DECLARE @val1 INT
DECLARE @val2 INT
DECLARE @val3 INT 
DECLARE @val4 INT 
DECLARE @val5 INT 
DECLARE @val6 INT 

EXEC @val1 = GenerateRandomNumbers 1, 49, 1, 0
EXEC @val2 = GenerateRandomNumbers 1, 49, 1, 0
EXEC @val3 = GenerateRandomNumbers 1, 49, 1, 0
EXEC @val4 = GenerateRandomNumbers 1, 49, 1, 0
EXEC @val5 = GenerateRandomNumbers 1, 49, 1, 0
EXEC @val6 = GenerateRandomNumbers 1, 49, 1, 0
INSERT INTO CustomerSelections
           ([draw_date]
           ,[val1]
           ,[val2]
           ,[val3]
           ,[val4]
           ,[val5]
           ,[val6])
     VALUES
           (
           '2013-07-05'
           ,@val1
           ,@val2
           ,@val3
           ,@val4
           ,@val5
           ,@val6
           )

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

1

First, you should declare some parameters, and than pass those variables to each exec statement then use that variables in the insert statatement. You can follow below link for details.

Set return value of a stored procedure to a variable

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Jul 21, 2013 at 8:42

uguracar

215 bronze badges

Collectives™ on Stack Overflow

Using EXEC command as INSERT VALUE in SQL

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