CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Icaroo
Icaroo

Reputation: 131

Pass variable as parameter on a Stored Procedure

I have a stored procedure with a SELECT statement and two parameters. I want to call this stored procedure from another one that has a variable declared, and use this variable as one of the parameters.

When I try this: 

EXEC [dbo].[Testo] @cd_order = 23, @cd_substep = 33

It returns some rows as result, but when I try this:

set @temp_var1 = ( Select cd_substep FROM ....Where...)

EXEC [dbo].[Testo] @cd_order = 23, @cd_substep = @temp_var1

The result is empty.

The procedure will populate a table variable:

INSERT INTO @Var1Table EXEC [dbo].[Testo] 23, @cd_substep

It's working only when I use a static value. How do I use a variable as a parameter?

Upvotes: 0

Views: 6392

Answers (2)

jthalliens
jthalliens

Reputation: 544

Could be something wrong in your code but it works, I agree with @JonH, I also did something similar based on your explanation for a different perspective

USE tempdb
GO
IF OBJECT_ID(N'tempdb.dbo.#TempData') IS NULL
BEGIN
    CREATE TABLE #TempData
    (
    ID INT IDENTITY
    ,First_Name VARCHAR(100)
    ,Last_Name VARCHAR(100)
    ,RecordDate DATETIME
    )
END
GO
INSERT INTO #TempData (First_Name,Last_Name,RecordDate) VALUES ('Juan','Jose Calambuco',GETDATE())
INSERT INTO #TempData (First_Name,Last_Name,RecordDate) VALUES ('Carlos','Jose Calambuco',GETDATE())
INSERT INTO #TempData (First_Name,Last_Name,RecordDate) VALUES ('Arturo','Jose Calambuco',GETDATE())
INSERT INTO #TempData (First_Name,Last_Name,RecordDate) VALUES ('Fabian','Jose Calambuco',GETDATE())
INSERT INTO #TempData (First_Name,Last_Name,RecordDate) VALUES ('Pedro','Jose Calambuco',GETDATE())
GO
CREATE PROC #TestData
(
@IDTempData INT
)
AS
SELECT * FROM #TempData WHERE ID = @IDTempData
GO
CREATE PROC #TestData2
(
@IDTempData INT 
)
AS
EXEC #TestData @IDTempData
GO

DECLARE @Value INT = (SELECT TOP 1 ID FROM #TempData)
EXEC #TestData @Value

EXEC #TestData2 @Value

EXEC #TestData 3

Upvotes: 0

JonH
JonH

Reputation: 33183

This is not a problem in your code, as I can simply copy what you have and it works:

DECLARE @return_value int
DECLARE @temp_var1 int

SET @temp_var1 = (SELECT 65)

EXEC    @return_value = [dbo].[GetRecordLog]
        @Action = N'All',
        @EntityID = 1,
        @RecordID = @temp_var1

SELECT  'Return Value' = @return_value

Notice my @temp_var1 has a value of 65. I then get rows which match this, I know the code is not exactly the same as yours but the concept remains the same. The issue is not with your code but with the value that @temp_var1 has.

Upvotes: 5

Related Questions