Passing a value to a Teradata Stored Procedure from SQL Server

[Decker112]

I'm having trouble passing a Date value to a Stored Procedure that is being executed from SQL Server . 

Declare @D1 as date;
Set @D1 = CAST( '2017-10-30' AS DATE)

exec ('call PRD_TestDB.Monthly(''@D1'',''@D1'');') at [TD2]

I get the error message "A character string failed conversion to a numeric value."

When I do

exec ('call PRD_TestDB.Monthly(''2017-10-30'',''2017-10-30'');') at [TD2]     It works fine. 

It's as though the value is not being passed when the SP is being executed when i try to declare and set. It simply see's the @D1 as a character itself and not the value. I've tried different ways of setting this up and there is not allot of information out there.

Thanks,

[dnoeth · Accepted solution]

You're currently trying to exec this string: call PRD_TestDB.Monthly('@D1','@D1');

Of course this fails, because '@D1' is not a valid date.

You can concat the parameters and you don't need a DATE (which the concat casts to a string).

Try 

DECLARE @D1 AS VARCHAR(10);
SET @D1 = '2017-10-30'
EXEC ('call PRD_TestDB.Monthly(''' + @D1 + ''',''' + @D1 + ''');') AT [TD2] 

You should also be able to pass parameters using ?:

DECLARE @D1 AS DATE;
SET @D1 = Cast( '2017-10-30' AS DATE) 
EXECUTE ('call PRD_TestDB.Monthly(?,?);', @D1, @D1) AT [TD2]

[Decker112]

Awesome,

This is what i was after

DECLARE @D1 AS VARCHAR(10);
SET @D1 = CAST(GETDATE() -1 AS date)

And now it works, Was using the static date as a test.