January 29, 2019 at 4:30 pm
This stored procedure accepts parameters:
CREATE PROCEDURE FindCustomersByState
@state char(2) = 'CA'
AS
BEGIN
SELECT FirstName, LastName, Phone, City, State,
Zip
FROM
Customers
WHERE
State = @state
END
In the SQL Editor I can specify a parameter to enter such as:
execute FindCustomersByState
@state = 'FL'
But how would the user know that a parameter is even required when they weren't prompted to enter one? When executing the stored procedure, it would be helpful if SQL displayed a dialog box to prompt the user to enter a parameter value. Why doesn't SQL do this? Or is there a way to get SQL to do this?
January 29, 2019 at 5:11 pm
SQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.
Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
January 29, 2019 at 8:29 pm
Lynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
This was a very helpful answer. Thank you.
In my original post, why are there so many blank lines? I'm pretty such I deleted those before I posted.
January 29, 2019 at 8:50 pm
michael.leach2015 - Tuesday, January 29, 2019 8:29 PMLynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
This was a very helpful answer. Thank you.
In my original post, why are there so many blank lines? I'm pretty such I deleted those before I posted.
The easiest way to overcome the faults currently built into the forum software is to copy your code from SSMS into Notepad... make any final adjustments you wish and then copy for NotePad and paste it between the SQL Code tags. Hopefully, the new forum software that they're moving to won't have such problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 29, 2019 at 9:01 pm
Jeff Moden - Tuesday, January 29, 2019 8:50 PMmichael.leach2015 - Tuesday, January 29, 2019 8:29 PMLynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
This was a very helpful answer. Thank you.
In my original post, why are there so many blank lines? I'm pretty such I deleted those before I posted.
The easiest way to overcome the faults currently built into the forum software is to copy your code from SSMS into Notepad... make any final adjustments you wish and then copy for NotePad and paste it between the SQL Code tags. Hopefully, the new forum software that they're moving to won't have such problems.
Thank you. I'll try that next time.
January 29, 2019 at 9:02 pm
Lynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
I right clicked and got the dialog box. There is a field called 'Output Parameter' which is set to No. I was unable to change it. Why couldn't I change that? What does that field do?
January 30, 2019 at 6:10 am
michael.leach2015 - Tuesday, January 29, 2019 9:02 PMLynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
I right clicked and got the dialog box. There is a field called 'Output Parameter' which is set to No. I was unable to change it. Why couldn't I change that? What does that field do?
It just lets you know if the parameter for the stored procedure was setup to provide output. You have to change the stored procedure to designate the parameter for OUT or OUTPUT if you want to actually get a return through the parameter. I haven't tried it because I don't use them but a Table Valued Parameter might behave the way I think you want variables from a stored procedure to behave.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2019 at 7:54 am
If you create the stored procedure with a default value for @state of NULL, then if the parameter is NULL you can display some help for the call, e.g.:CREATE PROCEDURE FindCustomersByState
@state char(2) = NULL
AS
BEGIN
IF @state IS NULL BEGIN
SELECT 'Sample Usage:' Usage UNION ALL
SELECT 'EXEC FindCustomersByState @State=''xx'''
RETURN
END
SELECT FirstName, LastName, Phone, City, State, Zip
FROM Customers
WHERE State = @state
END
Also, if you execute the stored procedure by right clicking the stored procedure in SSMS then it gives you a list of the parameters which you can set your own values.
January 30, 2019 at 8:50 am
michael.leach2015 - Tuesday, January 29, 2019 9:02 PMLynn Pettis - Tuesday, January 29, 2019 5:11 PMSQL Server isn't an interactive environment. Now, if you right click on a procedure in the Object Explorer and tell it to execute, you will get a dialog box to enter parameters. The draw back is the SSMS isn't a user tool, it is a developer/DBA tool.Most stored procedures are executed from within an application or middle tier where the developer knows the parameters and can write the code to pass the appropriate data.
I right clicked and got the dialog box. There is a field called 'Output Parameter' which is set to No. I was unable to change it. Why couldn't I change that? What does that field do?
When you right clicked on the procedure did you select Execute Procedure in the pop menu?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply