You must Sign In to post a response.
  • Category: SQL Server

    Stored procedure can't return outpurt parameter value

    Hi,

    My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP could anybody
    guide to me what's wrong in my SP.

    alter Procedure BD_InsertMobileClient_GpsLogPlanogram
    (
    @SalesPersonID int,
    @MobileRowID int,
    @StatusCode int output
    )
    AS
    INSERT INTO dbo.tblGpsplanogram
    (
    SalespersonID,
    MobileGpsRowID

    )
    VALUES
    (
    @SalesPersonID,
    @MobileRowID
    )

    Declare @CurrentrowID int;
    set @CurrentrowID =SCOPE_IDENTITY()
    select @StatusCode = MobileGpsRowID from tblGpsplanogram
    where ID = @CurrentrowID
    RETURN @StatusCode

    Thanks and regards
    brite
  • #762745
    Hi,

    First off all check the output parameter value in backend what it returns exactly, if it returns the data perfectly then debug your code and check it one more time to find the issue.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #762779

    hi, Brite
    Try to like this Example

    USE [Example_db]
    GO

    /****** Object: StoredProcedure [dbo].[Insert_support_plan] Script Date: 08/28/2015 13:32:59 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[Insert_support_plan]
    @Mode varchar(50),
    @support_id varchar(max),
    @emp_id varchar(max),
    @branch varchar(max),
    @company varchar(max),
    @department varchar(max),
    @customername varchar(max),
    @address varchar(max),
    @city varchar(max),
    @state varchar(max),
    @country varchar(max),
    @mobile varchar(max),
    @phone varchar(max),
    @email1 varchar(max),
    @email2 varchar(max),
    @instn varchar(max),
    @model_or_serialno varchar(max),
    @division varchar(max),
    @product varchar(max),
    @descriptionofwork varchar(max),

    @visitmode varchar(max),
    @action varchar(max),
    @reportingdate varchar(max),
    @timefrom varchar(max),
    @timeto varchar(max),
    @client varchar(max),
    @action_to_be_taken varchar(max),
    @comments varchar(max),
    @regdate varchar(max),
    @regby varchar(max),
    @modifydate varchar(max),
    @modifyby varchar(max),
    @deleteddate varchar(max),
    @deletedby varchar(max),
    @output varchar(40) output
    AS
    BEGIN
    if @Mode ='insert'
    begin
    declare @dupi int;
    select @dupi=Count(support_id) from tbl_support_plan where support_id=@support_id
    if(@dupi>0)
    begin
    set @output='Support ID already Exists !'
    end
    else
    begin
    insert into tbl_support_plan(support_id,emp_id,branch,company,department,customername,address,city,state,country,mobile,phone,email1,email2,instn,model_or_serialno,division,product,descriptionofwork,visitmode,action,reportingdate,timefrom,timeto,client,action_to_be_taken,comments,regdate,regby,status)values(@support_id,@emp_id,@branch,@company,@department,@customername,@address,@city,@state,@country,@mobile,@phone,@email1,@email2,@instn,@model_or_serialno,@division,@product,@descriptionofwork,@visitmode,@action,@reportingdate,@timefrom,@timeto,@client,@action_to_be_taken,@comments,@regdate,@regby,'A')
    set @Output='Registered Successfully !'
    end
    end
    if @mode='update'
    begin
    update tbl_support_plan set company=@company,department=@department,customername=@customername,address=@address,city=@city,state=@state,country=@country,mobile=@mobile,phone=@phone,email1=@email1,email2=@email2,instn=@instn,model_or_serialno=@model_or_serialno,division=@division,product=@product,descriptionofwork=@descriptionofwork,visitmode=@visitmode,action=@action,reportingdate=@reportingdate,timefrom=@timefrom,timeto=@timeto,client=@client,action_to_be_taken=@action_to_be_taken,comments=@comments,modifydate=@modifydate,modifyby=@modifyby where support_id=@support_id
    set @Output='Updated Successfully !'
    end
    if @mode='delete'
    begin
    update tbl_support_plan set status='D', deleteddate=@deleteddate, deletedby=@deletedby Where support_id=@support_id
    set @Output='Deleted Successfully !'

    end
    END

    GO

    my code is little bit big am sorry. try to understand then implement the same way it will work.
    Hope this information will help you.

    Paul.S


Sign In to post your comments