Toad World® Forums

Issues with create stored procedure feature

I have noticed an issue with the create stored procedure function that is available by highlighting some code and right clicking with the mouse. Normally it takes the code I have highlighted and creates a stored procedure without any problem but this time it left out large sections of code where I declared temp tables and created input parameters for them. I made a quick example and attached a screenshot of the problem I am seeing. I am running version 5.6.0.568.

Denny

Hello,Denny!
What result are you waiting? Your script contains description of 2 parameters with datatype = Table. “Create Stored Procedure” action executes convertion all “declare @parameter” to procedure parameters. Please describe your waiting result.

Maria.

Hi Maria,

Many times when I create a stored procedure I don’t want all of the declared variables to be input parameters. I normally create a lot of variables that are used locally inside of the sp. Especially when creating a sp that is used by a report.

In the case of the example I posted, I would use something like this inside the sp to fill the TempTable for use later on in the sp.

INSERT INTO @TempTable1
SELECT col1, col2, col3, col4
FROM dbo.erptable erp
WHERE erp.SKU like ‘77%WX’

Denny

Hi,Denny!
If you will use script as like

INSERT INTO @TempTable1
SELECT col1, col2, col3, col4
FROM dbo.erptable erp
WHERE erp.SKU like ‘77%WX’

when “Create Stored Procedure” action doesn`t convert to sp parameters. Only construction "declare @parameter’ will be processed in sp parameter. We will plan to improve “Create Stored Procedure” processing in next release.

Maria.

Maria,

As implemented, the create stored procedure function from the menu does not meet my needs. Using it would actually cause more work because I have to manually re-enter all my declare statements and remove all of the un-needed input parameters.

Denny