SQL PUZZLE | Insert Hardcoded values along with INSERT-EXEC in a table – Multiple Methods | Advanced SQL

In this puzzle you have to insert some hardcoded values in to a table.

Here we need to insert below values into tableData
1 + OUTPUT of
DECLARE @p1 AS INT = 10
EXEC sp1 @p1
GO

Here we need to insert below values into tableData
2 + OUTPUT of
DECLARE @p2 AS INT = 5
EXEC sp2 @p2
GO.

For more details please see the sample input and expected output.

Expected Output

Script – DDL and INSERT Sample Data

--

CREATE PROC sp1 
(
	@P1 INT
)
AS
BEGIN
	SELECT @P1 + 10
END
GO

CREATE PROC sp2
(
	@P2 INT
)
AS
BEGIN
	SELECT @P2 * 10
END
GO

CREATE TABLE tableData
(
	PrimaryValue  INT, 
	P1 INT, 
	P2 INT,
)
GO

DECLARE @p1 AS INT = 10
EXEC sp1 @p1
GO

DECLARE @p2 AS INT = 5
EXEC sp2 @p2
GO

--

Well there is not direct solutions for this. We have to make some adjustments. 🙂

SOLUTION – 1 | INSERT – UPDATE

--

DECLARE @p1 AS INT = 10
INSERT INTO tableData(P1)
EXEC sp1 @p1
UPDATE tableData SET PrimaryValue = 1
WHERE P1 IS NOT NULL

DECLARE @p2 AS INT = 5
INSERT INTO tableData(P2)
EXEC sp1 @p2
UPDATE tableData SET PrimaryValue = 2
WHERE P2 IS NOT NULL

SELECT * FROM tableData
GO

--

Output – 1

--

PrimaryValue P1          P2
------------ ----------- -----------
1            20          NULL
2            NULL        15

(2 rows affected)




--

SOLUTION – 2 | INSERT into an another Temp TABLE and use that as a new source

--

CREATE TABLE #test(P INT)
DECLARE @p1 AS INT = 10
INSERT INTO #test(P)
EXEC sp1 @p1
INSERT INTO tableData(PrimaryValue,P1,P2)
SELECT 1,P, NULL
FROM #test
 
DELETE FROM #test
DECLARE @p2 AS INT = 5
INSERT INTO #test(P)
EXEC sp1 @p2
INSERT INTO tableData(PrimaryValue,P1,P2)
SELECT 2,NULL, P
FROM #test
 
SELECT * FROM tableData
GO

--

Output – 2

--

PrimaryValue P1          P2
------------ ----------- -----------
1            20          NULL
2            NULL        15

(2 rows affected)



--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com