Dooooooooooo (12/12/2013)
when i execute the SP with data parametersUSE [ABC]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[test]
@QTRSTRTDATE = N'select CONVERT(datetime,DATEADD("M", DATEDIFF("M", 0, GETDATE()), 0),120)',
@QTRENDDATE = N'select CONVERT(datetime,DATEADD("M",DATEDIFF("M",-1, GETDATE()),-1),120)'
SELECT'Return Value' = @return_value
GO
i get the below error..
Msg 8114, Level 16, State 5, Procedure usp_MarketingAccountLevel_test, Line 0
Error converting data type nvarchar to datetime.
(1 row(s) affected)
Any help.
You're passing in strings (nvarchar), most likely, inside your procedure you've defined the parameters as datetime. It seems like you're expecting these to be evaluated and then passed, but I haven't had any luck passing an expression as a parameter to a stored procedure.
CREATE PROCEDURE [dbo].[TEST]
@TESTPARAM INT
AS
BEGIN
SELECT @TESTPARAM
END
...
DECLARE @rc int
DECLARE @TESTPARAM int
-- TODO: Set parameter values here.
EXECUTE @rc = [dbo].[TEST]
@TESTPARAM=N'SELECT 1'
GO
Msg 8114, Level 16, State 4, Procedure TEST, Line 0
Error converting data type nvarchar to int.
DECLARE @rc int
DECLARE @TESTPARAM int
-- TODO: Set parameter values here.
EXECUTE @rc = [dbo].[TEST]
@TESTPARAM=(1+1)
GO
Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.