pass date parameter to stored procedure

[Margaret]

How do I pass a date parameter to a stored procedure?

I have in QlikView:

Let @FromDate = '01/01/2016';

Visits: SQL
QV_HospitalVisits '@FromDate';

In SQL Server Management Studio I have

CREATE PROCEDURE [dbo].[QV_HospitalVisits]

@FromDate datetime

AS

Select...

But I get “Error converting data type varchar to datetime”.

I also tried this format for the variable: ‘01/01/2016' and varchar(10) for the data type in the stored procedure. I still got the same error.

What am I doing wrong?

[marcus_sommer · Accepted solution]

Try it with: '$(@FromDate)'

- Marcus

[ToniKautto]

My first question would be what does the valid SQL query look like if you execute it in a SQL client? I do not recall seeing a procedure execution being called this way before, often the EXEC command is used.

QV_HospitalVisits '01/01/2016'

In the QlikView reload the string defined between the keyword SQL and the terminating semicolon will be sent to the data source for processing. The key to success is there for to make sure it looks as valid SQL syntax for the given data source. By looking in the QlikView document reload log, you can analyze exactly what the SQL query looks like when executed.

If you in this case expect the above SQL statement to be based form the variable @FromDate, you will have to expand it as advised by Marcus.

QV_HospitalVisits '$(@FromDate)'

Further you need to look at the database settings in QlikView, so that the procedure execution is allowed by QlikView. See Edit Script Dialog ‒ QlikView‌ for where to find the "Open Database in Read and Write Mode" option.

[Anil_Babu_Samineni]

CAn you please post the Error screen shot

[Margaret]

That worked, thank you!!!

[Margaret]

I am able to execute the stored procedure in both SQL Server Management Studio and QlikView without an EXEC command.

Thanks for the explanation!