Passing a value to a Teradata Stored Procedure from SQL Server

Database

Passing a value to a Teradata Stored Procedure from SQL Server

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,

 


Accepted Solutions
Junior Contributor

Re: Passing a value to a Teradata Stored Procedure from SQL Server

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]

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Passing a value to a Teradata Stored Procedure from SQL Server

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]

 

Re: Passing a value to a Teradata Stored

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.