Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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'));
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'));
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
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.
Great information, thanks. I think I can still do what I want with the popup input, we shall see!