Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simple passing parameters into stored procedure

Hello all. I am trying to figure out how to correctly pass a variable created with an InputBox, into a SQL stored procedure.

This particular stored procedure wants two parameters, one of which can be ignored in this case. My problem is that I get the 245 error saying it cannot convert my varchar variable to int.

So I'm:

  • declaring a variable via an InputBox called 'LocationCode'
  • writing a simple stored procedure that has two parameters which is:

SQL EXEC [ETTDS].[spLook] @parse_field='', @usid_facode='LocationCode';

The user should enter a Location Code into the input box, and the reload should display the results in a multibox (for now; just trying to get this part to work) which is on the sheet already.

So WHY is my variable being forced as a varchar, OR, is the stored procedure expecting a varchar and getting int? In which case the question is similar, how do I convert my variables to what the database is expecting to receive?

Sorry I'm such a noob, very little SQL knowledge.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

LET LocationCode = input('Enter value for LocationCode', 'Input box');

SQL EXEC [ETTDS].[spLook] @parse_field='', @usid_facode='$(LocationCode)';

If you want to know what type your stored procedure needs, you should ask your friendly local database administrator.

'$(LocationCode)' will put the value of the variable LocationCode in single quotes, so it is treated as a string. If it must be a number then leaving out the quotes should do. Filling in a string instead of numbers will trip things up though. You could try a num#(LocationCode) first to try to make a number of whatever was input.

LET LocationCode = num#(input('Enter value for LocationCode', 'Input box'));


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

LET LocationCode = input('Enter value for LocationCode', 'Input box');

SQL EXEC [ETTDS].[spLook] @parse_field='', @usid_facode='$(LocationCode)';

If you want to know what type your stored procedure needs, you should ask your friendly local database administrator.

'$(LocationCode)' will put the value of the variable LocationCode in single quotes, so it is treated as a string. If it must be a number then leaving out the quotes should do. Filling in a string instead of numbers will trip things up though. You could try a num#(LocationCode) first to try to make a number of whatever was input.

LET LocationCode = num#(input('Enter value for LocationCode', 'Input box'));


talk is cheap, supply exceeds demand
Not applicable
Author

Woot! That works. Except it's in the form of a popup input box. I was hoping the user could enter the number into the sheet object Input Box to get the same results. Is this not the intended function of the Input Box?

Thanks for the quick reply btw

Gysbert_Wassenaar

The reload/load script can't access or interact with any UI objects like charts and sheet input boxes. It can use the variables of the document.

The input boxes you can use on sheets are for allowing the user to easily change values of variables which can then be used for example to change selections or change a calculation.


talk is cheap, supply exceeds demand
Not applicable
Author

Great information, thanks. I think I can still do what I want with the popup input, we shall see!