Toad World® Forums

Can't set variable to output in stored procedure


#1

I think I’ve reported this before but I can’t remember. If you create
a new stored procedure and add a variable you cannot set the variable to an
output variable unless you create the stored procedure and then edit it. This
is in the most recent beta drop.

Thanks,


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com


#2

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !
image004.png


#3

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !
image001.gif


#4

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !
image002.jpeg


#5

Sorry, I should have been more clear. It works if you type it in the editor.
However if you use the TOAD create stored procedure windows, there is a button
at the bottom to specify parameters for the stored procedure. It’s a pop
up box that you put in the name, type, and other various information. Near the
bottom of the popup there use to be a Output parameter that you could set to
true or false. I’m not on my work machine right now but I will screen shot
it and send the screenshots of what I mean.


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Fri, Apr 9, 2010 at 10:47 PM, Hank Freeman wrote:

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !

 

1.        Using 2008 Adventure works database

2.        Go to this link à              http://support.
microsoft. com/kb/262499

3.        Pick up the Output stored procedure example and run it and
you will find that it works.. (Pasted below)

4.        I am using Window 7 ultimate , SQL Server 2008 Developer 64
bit

 

I suggest others on this board try the above to see if it works.

Here are the working results.

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

 

/**

http://support. microsoft. com/kb/262499

**/

CREATE PROCEDURE Myproc

    @parm varchar( 10 ),

    @parm1OUT varchar( 30 ) OUTPUT,

    @parm2OUT varchar( 30 ) OUTPUT

    AS

      SELECT @parm1OUT = 'parm 1' + @parm

     SELECT @parm2OUT = 'parm 2' + @parm

GO

DECLARE @SQLString NVARCHAR( 500 )

DECLARE @ParmDefinition NVARCHAR( 500 )

DECLARE @parmIN VARCHAR( 10 )

DECLARE @parmRET1 VARCHAR( 30 )

DECLARE @parmRET2 VARCHAR( 30 )

SET @parmIN = ' returned'

SET @SQLString = N 'EXEC Myproc @parm,

                             @parm1OUT OUTPUT,
@parm2OUT OUTPUT'

SET @ParmDefinition = N '@parm varchar(10),

                      @parm1OUT varchar(30) OUTPUT,

                      @parm2OUT varchar(30) OUTPUT'



EXECUTE sp_executesql

    @SQLString ,

    @ParmDefinition ,

    @parm = @parmIN ,

    @parm1OUT = @parmRET1 OUTPUT, @parm2OUT = @parmRET2 OUTPUT

 

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"

go

drop procedure Myproc

image002.jpeg


#6

Sorry, I should have been more clear. It works if you type it in the editor.
However if you use the TOAD create stored procedure windows, there is a button
at the bottom to specify parameters for the stored procedure. It’s a pop
up box that you put in the name, type, and other various information. Near the
bottom of the popup there use to be a Output parameter that you could set to
true or false. I’m not on my work machine right now but I will screen shot
it and send the screenshots of what I mean.


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Fri, Apr 9, 2010 at 10:47 PM, Hank Freeman wrote:

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !

 

1.        Using 2008 Adventure works database

2.        Go to this link à              http://support.
microsoft. com/kb/262499

3.        Pick up the Output stored procedure example and run it and
you will find that it works.. (Pasted below)

4.        I am using Window 7 ultimate , SQL Server 2008 Developer 64
bit

 

I suggest others on this board try the above to see if it works.

Here are the working results.

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

 

/**

http://support. microsoft. com/kb/262499

**/

CREATE PROCEDURE Myproc

    @parm varchar( 10 ),

    @parm1OUT varchar( 30 ) OUTPUT,

    @parm2OUT varchar( 30 ) OUTPUT

    AS

      SELECT @parm1OUT = 'parm 1' + @parm

     SELECT @parm2OUT = 'parm 2' + @parm

GO

DECLARE @SQLString NVARCHAR( 500 )

DECLARE @ParmDefinition NVARCHAR( 500 )

DECLARE @parmIN VARCHAR( 10 )

DECLARE @parmRET1 VARCHAR( 30 )

DECLARE @parmRET2 VARCHAR( 30 )

SET @parmIN = ' returned'

SET @SQLString = N 'EXEC Myproc @parm,

                             @parm1OUT OUTPUT,
@parm2OUT OUTPUT'

SET @ParmDefinition = N '@parm varchar(10),

                      @parm1OUT varchar(30) OUTPUT,

                      @parm2OUT varchar(30) OUTPUT'



EXECUTE sp_executesql

    @SQLString ,

    @ParmDefinition ,

    @parm = @parmIN ,

    @parm1OUT = @parmRET1 OUTPUT, @parm2OUT = @parmRET2 OUTPUT

 

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"

go

drop procedure Myproc

image004.png


#7

Sorry, I should have been more clear. It works if you type it in the editor.
However if you use the TOAD create stored procedure windows, there is a button
at the bottom to specify parameters for the stored procedure. It’s a pop
up box that you put in the name, type, and other various information. Near the
bottom of the popup there use to be a Output parameter that you could set to
true or false. I’m not on my work machine right now but I will screen shot
it and send the screenshots of what I mean.


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Fri, Apr 9, 2010 at 10:47 PM, Hank Freeman wrote:

Charles, help me understand this issue, for I cannot reproduce it… Here is
what I did, which is nothing special !

 

1.        Using 2008 Adventure works database

2.        Go to this link à              http://support.
microsoft. com/kb/262499

3.        Pick up the Output stored procedure example and run it and
you will find that it works.. (Pasted below)

4.        I am using Window 7 ultimate , SQL Server 2008 Developer 64
bit

 

I suggest others on this board try the above to see if it works.

Here are the working results.

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

 

/**

http://support. microsoft. com/kb/262499

**/

CREATE PROCEDURE Myproc

    @parm varchar( 10 ),

    @parm1OUT varchar( 30 ) OUTPUT,

    @parm2OUT varchar( 30 ) OUTPUT

    AS

      SELECT @parm1OUT = 'parm 1' + @parm

     SELECT @parm2OUT = 'parm 2' + @parm

GO

DECLARE @SQLString NVARCHAR( 500 )

DECLARE @ParmDefinition NVARCHAR( 500 )

DECLARE @parmIN VARCHAR( 10 )

DECLARE @parmRET1 VARCHAR( 30 )

DECLARE @parmRET2 VARCHAR( 30 )

SET @parmIN = ' returned'

SET @SQLString = N 'EXEC Myproc @parm,

                             @parm1OUT OUTPUT,
@parm2OUT OUTPUT'

SET @ParmDefinition = N '@parm varchar(10),

                      @parm1OUT varchar(30) OUTPUT,

                      @parm2OUT varchar(30) OUTPUT'



EXECUTE sp_executesql

    @SQLString ,

    @ParmDefinition ,

    @parm = @parmIN ,

    @parm1OUT = @parmRET1 OUTPUT, @parm2OUT = @parmRET2 OUTPUT

 

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"

go

drop procedure Myproc

image001.gif


#8

Thanks Charles !

I look forward to your next Email

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image002.jpeg


#9

Thanks Charles !

I look forward to your next Email

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image001.gif


#10

Thanks Charles !

I look forward to your next Email

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image004.png


#11

Thanks Charles !

I look forward to your next Email

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary
image002.jpeg


#12

[ Attachment(s) from Charles Haines included below]

Sorry it took so long to get this but I’ve been at a conference in Vegas
(which BTW I got to talk to your team that works on the Oracle plugin for VS,
pretty cool stuff).

Ok, this first screen shot (the AlterSP_ExistingPar ameter.png) shows me editing
and existing parameter on an existing stored procedure. You’ll notice the
option for setting it as an output parameter or not.

The next screen shot (AlterSP_NerParmter .png) is editing and existing stored
procedure but trying to add a new parameter. You’ll notice that the option
to set it as an output parameter or not is no longer there.

The final screen shot (NewSP_NewParameter .png) is a new store procedure adding
a new parameter. Once again the option to set the parameter as an output
parameter is not there.

Hope this helps.

Thanks,


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Sat, Apr 10, 2010 at 4:38 PM, Hank Freeman wrote:

Thanks Charles !

I look forward to your next Email

 

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

image002.jpeg


#13

[ Attachment(s) from Charles Haines included below]

Sorry it took so long to get this but I’ve been at a conference in Vegas
(which BTW I got to talk to your team that works on the Oracle plugin for VS,
pretty cool stuff).

Ok, this first screen shot (the AlterSP_ExistingPar ameter.png) shows me editing
and existing parameter on an existing stored procedure. You’ll notice the
option for setting it as an output parameter or not.

The next screen shot (AlterSP_NerParmter .png) is editing and existing stored
procedure but trying to add a new parameter. You’ll notice that the option
to set it as an output parameter or not is no longer there.

The final screen shot (NewSP_NewParameter .png) is a new store procedure adding
a new parameter. Once again the option to set the parameter as an output
parameter is not there.

Hope this helps.

Thanks,


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Sat, Apr 10, 2010 at 4:38 PM, Hank Freeman wrote:

Thanks Charles !

I look forward to your next Email

 

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

image004.png


#14

[ Attachment(s) from Charles Haines included below]

Sorry it took so long to get this but I’ve been at a conference in Vegas
(which BTW I got to talk to your team that works on the Oracle plugin for VS,
pretty cool stuff).

Ok, this first screen shot (the AlterSP_ExistingPar ameter.png) shows me editing
and existing parameter on an existing stored procedure. You’ll notice the
option for setting it as an output parameter or not.

The next screen shot (AlterSP_NerParmter .png) is editing and existing stored
procedure but trying to add a new parameter. You’ll notice that the option
to set it as an output parameter or not is no longer there.

The final screen shot (NewSP_NewParameter .png) is a new store procedure adding
a new parameter. Once again the option to set the parameter as an output
parameter is not there.

Hope this helps.

Thanks,


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Sat, Apr 10, 2010 at 4:38 PM, Hank Freeman wrote:

Thanks Charles !

I look forward to your next Email

 

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

image001.gif


#15

[ Attachment(s) from Charles Haines included below]

Sorry it took so long to get this but I’ve been at a conference in Vegas
(which BTW I got to talk to your team that works on the Oracle plugin for VS,
pretty cool stuff).

Ok, this first screen shot (the AlterSP_ExistingPar ameter.png) shows me editing
and existing parameter on an existing stored procedure. You’ll notice the
option for setting it as an output parameter or not.

The next screen shot (AlterSP_NerParmter .png) is editing and existing stored
procedure but trying to add a new parameter. You’ll notice that the option
to set it as an output parameter or not is no longer there.

The final screen shot (NewSP_NewParameter .png) is a new store procedure adding
a new parameter. Once again the option to set the parameter as an output
parameter is not there.

Hope this helps.

Thanks,


Charles Haines
Code Monkey

Recorded Books, LLC
http://www.recorded books.com

On Sat, Apr 10, 2010 at 4:38 PM, Hank Freeman wrote:

Thanks Charles !

I look forward to your next Email

 

 

Hank Freeman

Senior Systems, Database/Data Warehouse Architect

hfreeman@msn. com

678.414.0090 my cell Primary

image002.jpeg