PiyushNazkani

Parameterized SQL stored procedure for data fetching

Discussion created by PiyushNazkani on Nov 12, 2020

Hi,

I am trying to create a parameterized SQL Stored procedure for fetching the data with start time and the end time as input parameter. We have configured this with the help of the link server.On passing the dates we are getting the below error :

OLE DB provider "PIOLEDBENT" for linked server "......" returned message "The string '@StartTime' was not recognized as a valid DateTime."

I tried to run the same query in the pi sql commander lite with the same parameter as in passed in the SSMS and found it was working fine.

 

Here is my stored procedure:

ALTER PROCEDURE [dbo].[SP_CH_BU1_4RCBU1]
-- Add the parameters for the stored procedure here
@StartTime varchar(32)
,@EndTime varchar(32)
AS
BEGIN
SET NOCOUNT ON;
Declare @Start as datetime;
Declare @end as datetime;
Set @start = convert(datetime,@StartTime,21);
Set @end= convert(datetime,@EndTime,21);
SELECT * FROM OPENQUERY([CEAT_LINK],
'SELECT eh.Path + eh.Name Element, ta.*
FROM [CHSRVOT014].[Asset].[ElementTemplate] et
INNER JOIN [CHSRVOT014].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [CHSRVOT014].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
CROSS APPLY [CHSRVOT014].DataT.[TransposeArchive_CH_BU1_4RC01]
(
eh.ElementID,
Date(N''@StartTime''), /*StartTime*/
Date(N''@EndTime'') /*EndTime*/
) ta
WHERE et.Name = N''CH_BU1_4RC01''
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)')

END

 

Could anyone suggest the possible solution here for the same and why this is happening.

Outcomes