Parameters in Stored Procedures....

• analau

  SSC Veteran

  Points: 271

  More actions

  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,

   

• chrisleonard

  SSCertifiable

  Points: 5030

  More actions

  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