We help IT Professionals succeed at work.

SQL 2000 - Executing SP from the Query Analyzer

feesu
feesu asked
on
835 Views
Last Modified: 2012-06-21
Hi,
I need to execute a stored procedure from the query analyzer for testing purpose. The sp has one input parameter and 10 output ones. However, when i execute it passing the input parameter, it still asks for the out put ones!

----------------------------------
I'm using this:
----------------------------------
EXEC PortalSettings_Get @Tab_IKey=0      

----------------------------------
I get this:
----------------------------------
Server: Msg 201, Level 16, State 4, Procedure PortalSettings_Get, Line 0
Procedure 'PortalSettings_Get' expects parameter '@TabName', which was not supplied.

----------------------------------
The sp code is:
----------------------------------
CREATE PROCEDURE PortalSettings_Get
(
    @Tab_IKey         int,
    @TabName       nvarchar (50)  OUTPUT,
    @TabOrder      int OUTPUT,
    @AccessRoles     nvarchar (256) OUTPUT,

      @TabTitle nvarchar(50) OUTPUT,
      @ParentID int OUTPUT,
      @Level int  OUTPUT,
      @IsVisible int OUTPUT,
      @IconFile nvarchar(200) OUTPUT,
      @URL nvarchar(200) OUTPUT,
      @RefreshInterval int OUTPUT,
      @SkinSource nvarchar(50) OUTPUT,
      @Language_IKey int OUTPUT

)
AS

/* First, get Out Params */
IF @Tab_IKey = 0
      BEGIN
                SELECT TOP 1
                    @Tab_IKey         = sys_Tabs.IKey,
                    @TabOrder         = sys_Tabs.TabOrder,
                    @TabName         = sys_Tabs.TabName,
                    @AccessRoles    = sys_Tabs.AccessRoles,

                  @TabTitle =sys_Tabs.TabTitle,
                  @ParentID =sys_Tabs.ParentID,
                  @Level=sys_Tabs.[Level],
                  @IsVisible=sys_Tabs.IsVisible,
                  @IconFile=sys_Tabs.IconFile,
                  @URL=sys_Tabs.URL  ,
                  @RefreshInterval=sys_Tabs.RefreshInterval,
                  @SkinSource=sys_Tabs.SkinSource,
                  @Language_IKey=sys_Tabs.Language_IKey
                FROM
                    sys_Tabs
                ORDER BY
                    TabOrder
      END
ELSE
      BEGIN
                SELECT
                    @TabName       = sys_Tabs.TabName,
                    @TabOrder      = sys_Tabs.TabOrder,
                    @AccessRoles     = sys_Tabs.AccessRoles,

                  @TabTitle =sys_Tabs.TabTitle,
                  @ParentID =sys_Tabs.ParentID,
                  @Level=sys_Tabs.[Level],
                  @IsVisible=sys_Tabs.IsVisible,
                  @IconFile=sys_Tabs.IconFile,
                  @URL=sys_Tabs.URL  ,
                  @RefreshInterval=sys_Tabs.RefreshInterval,
                  @SkinSource=sys_Tabs.SkinSource,
                  @Language_IKey=sys_Tabs.Language_IKey
                FROM
                    sys_Tabs
                WHERE
                    IKey=@Tab_IKey
      END

/* Get Tabs list */
SELECT  
     *
FROM    
    sys_Tabs
ORDER BY
    TabOrder

/* Then, get the DataTable of module info */
SELECT  
    M.*,T.*,D.*
FROM
    sys_Modules M INNER JOIN sys_TabModules T ON M.IKey=T.Module_IKey INNER JOIN sys_ModuleDefinitions D ON D.IKey=M.ModuleDefinition_IKey
   
WHERE  
    T.Tab_IKey = @Tab_IKey
ORDER BY
      ModuleOrder
GO


Comment
Watch Question

Commented:
try like this

declare @Tab_IKey         int,     @TabName       nvarchar (50)  ,    @TabOrder      int ,     @AccessRoles     nvarchar (256)
declare       @TabTitle nvarchar(50) ,      @ParentID int ,      @Level int  ,      @IsVisible int ,      @IconFile nvarchar(200)
declare       @URL nvarchar(200) ,      @RefreshInterval int ,      @SkinSource nvarchar(50) ,      @Language_IKey int



EXEC PortalSettings_Get @Tab_IKey=0 , @TabName  output,    @TabOrder  output,     @AccessRoles     output,
@TabTitle output,      @ParentID output,      @Level output,      @IsVisible output,      @IconFile output,
@URL output,      @RefreshInterval output,      @SkinSource output,      @Language_IKey output

select @TabName  , @TabOrder  , @AccessRoles     ,
@TabTitle , @ParentID , @Level , @IsVisible , @IconFile ,
@URL ,      @RefreshInterval ,      @SkinSource ,      @Language_IKey
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
appari is right about how to call such a procedure properly.

just to show how to specify in SQL procedure a parameter to be "optional": just give it a default value!

CREATE PROCEDURE PortalSettings_Get
(
    @Tab_IKey         int,
    @TabName       nvarchar (50) = NULL OUTPUT,
    @TabOrder      int = NULL OUTPUT,
    @AccessRoles     nvarchar (256) = NULL OUTPUT,

      @TabTitle nvarchar(50) = NULL OUTPUT,
      @ParentID int = NULL OUTPUT,
      @Level int = NULL OUTPUT,
      @IsVisible int = NULL OUTPUT,
      @IconFile nvarchar(200) = NULL OUTPUT,
      @URL nvarchar(200) = NULL OUTPUT,
      @RefreshInterval int = NULL OUTPUT,
      @SkinSource nvarchar(50) = NULL OUTPUT,
      @Language_IKey int = NULL OUTPUT
)
AS
...

Author

Commented:
--------------------------------------------------
I executed what appari  sent, and got:
--------------------------------------------------
Server: Msg 119, Level 15, State 1, Line 7
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It worked thanks!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.