sp with input parameters

  • I have a stored procedure which has a parameter @user_id

    If i eneter @user_id the stored procedure should return data for that user_id only,But if i wont enter any user_id it should calculate data for all the users.

    CASE1:

    EXEC sp_abc  45

    It should calculate for user_id 45

    CASE2: EXEC sp_abc

    IT should calculate for all the users in the system

    how can i implement this.

    Thanks.

  • Try this:

    CREATE Procedure sp_abc 

     (

      @Userid int =0  

    &nbsp

    As set nocount on

    if @userid=0

       Begin   

           Calculate data for all users

       End

    Else

       Begin   

           Calculate data for user_id=@userid

       End

     

  • Within the stored procedure set the @user_id to have a default value, check for this default value, if it's the default run one query, it it's the other run the other with the @user_id parameter within the query.



    Shamless self promotion - read my blog http://sirsql.net

  • thanks for ur help

  • If your procedure is doing a single select statement, you can do it without an IF block:

    create procedute sp_abc

       @userid  int null

    AS

        SELECT * FROM users WHERE users.userid = @userid OR @userid IS NULL

    This will keep your code nice and clean and save having to maintain two select statements. Of course, if you wanted to do more intensive work than a select, you can always use the IF/ELSE blocks like Bavineni suggested.

     


    Julian Kuiters
    juliankuiters.id.au

Viewing 5 posts - 1 through 4 (of 4 total)

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