Post reply

Parameters in Stored Procedures....

  • September 13, 2004 at 9:41 am

    #85374

    Hi, I have a question, I have found some sp on the database that has the next sintaxis (the sp calls this sp):

    EXEC spName @Param1 = @Param1, @Param2 = @Param2, @Param3 = @Param3, @Param4= @Param4 output,  @Param5= @Param5 output

    Can you tell me why is this way??  I tray to run it and it gives me the next error

    Server: Msg 8145, Level 16, State 2, Procedure spName , Line 0

    Param1 is not a parameter for procedure spName.

    This works on sp that was made a long time a go, I am using part of that sp for another thing that is almost the same and needs the same calling.

    I modify the calling like this:

    EXEC spName @Param1, @Param2, @Param3, @Param4= @Param4 output,  @Param5= @Param5 output

    And this works, but I want to know why they put it like the first code I wrote you and if this a right way to write or call a stored procedure.

    I hope I explain my self, if I didn´t please let me know, so I write more.

    thanks a lot for your help,

     

  • September 13, 2004 at 12:48 pm

    #522825

    Analau,

    The first method is passing arguments using named parameters.  With this method, exec myproc @p1=@v1, @p2=@v2 output means that the procedure myproc has a parameter named @p1 which you want to initialize with the local variable @v1, and the procedure also has a parameter named @p2 which you want to associate with the local variable @v2, allowing the value of @v2 to be changed by myproc.

    The second method is passing arguments using positional parameters.  With this method, exec myproc @v1, @v2 output means that you want to initialize the first parameter of myproc (whatever it's called) with the value of the local variable @v1, and you want to associate the second parameter of myproc (whatever it's called) with the local variable @v2 , allowing the value of @v2 to be changed by myproc.

    So, since the first approach didn't work for you, I'm guessing that your procedure spName does not have a parameter named @Param1.  You can verify this by running exec sp_help spName and reviewing the parameters that are listed in the output.

    Cheers,

    Chris

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply