Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26

    Question Unanswered: what's wrong with this Stored Proc

    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    ALTER   PROCEDURE [insert_JobCards]
    	(@jobcardID_18 		[int] OUTPUT,
    	 @CustomerID_1 		[int],
    	 @EmployeeID_2 		[int],
    	 @PersonReporting_3 	[varchar](50),
    	 @NatureOfProblem_4 	[char](1),
    	 @HandedToID_5 		[int],
    	 @InvoiceNumber_6 	[numeric],
    	 @DateReceived_7 	[datetime],
    	 @DateRequired_8 	[datetime],
    	 @MakeAndModel_9 	[varchar](50),
    	 @SerialNumber_10 	[varchar](50),
    	 @SoftwareProblem_11 	[varchar](255),
    	 @DateFinished_12 	[datetime],
    	 @DatePickedUp_13 	[datetime],
    	 @SalesTaxRate_14 	[numeric],
    	 @JobSite_15 		[char](2),
    	 @Accessories_16 	[varchar](100),
    	 @Agreement_17 		[smallint])
    AS 
    Declare @JobCardID int
    INSERT INTO [DBSQL].[dbo].[JobCards] 
    	 ([CustomerID],
    	 [EmployeeID],
    	 [PersonReporting],
    	 [NatureOfProblem],
    	 [HandedToID],
    	 [InvoiceNumber],
    	 [DateReceived],
    	 [DateRequired],
    	 [MakeAndModel],
    	 [SerialNumber],
    	 [SoftwareProblem],
    	 [DateFinished],
    	 [DatePickedUp],
    	 [SalesTaxRate],
    	 [JobSite],
    	 [Accessories],
    	 [Agreement])
    VALUES 
    	( @CustomerID_1,
    	 @EmployeeID_2,
    	 @PersonReporting_3,
    	 @NatureOfProblem_4,
    	 @HandedToID_5,
    	 @InvoiceNumber_6,
    	 @DateReceived_7,
    	 @DateRequired_8,
    	 @MakeAndModel_9,
    	 @SerialNumber_10,
    	 @SoftwareProblem_11,
    	 @DateFinished_12,
    	 @DatePickedUp_13,
    	 @SalesTaxRate_14,
    	 @JobSite_15,
    	 @Accessories_16,
    	 @Agreement_17)
    
    GO
    select @jobcardID_18 = ('select @@identity')
    
    if @@error= 1 
    begin
    	print "Error, Parameter missing"
    	return(1)
    end
    else
    Begin
    	if @@error = 0
    	return(0)
    end
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    I want to return jobcardID_18 or return parameter.. but it gives me the following errors:
    'Must declare the variable '@jobcardID_18'' for jobcardID_18. and
    A RETURN statement with a return value cannot be used in this context.

    what am i doing wrong? i want to assign the @@identity value to the jobcardID_18 field and set the return value to either 0 or whatever the case maybe.

  2. #2
    Join Date
    Mar 2003
    Posts
    2
    Remove the 'GO'-statement after your INSERT statement

  3. #3
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    thats not the problem..i have removed that yet problem persists.

    i think the problem is due to the return values..

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    don't know if this is exactly what you want but I did clean up a few problems...

    Code:
    SET QUOTED_IDENTIFIER OFF 
    SET ANSI_NULLS ON 
    GO
    ALTER   PROCEDURE insert_JobCards(
      @jobcardID_18       int = null OUTPUT
    , @CustomerID_1       int
    , @EmployeeID_2       int
    , @PersonReporting_3  varchar(50)
    , @NatureOfProblem_4  char(1)
    , @HandedToID_5       int
    , @InvoiceNumber_6    numeric
    , @DateReceived_7     datetime
    , @DateRequired_8     datetime
    , @MakeAndModel_9     varchar(50)
    , @SerialNumber_10    varchar(50)
    , @SoftwareProblem_11 varchar(255)
    , @DateFinished_12    datetime
    , @DatePickedUp_13    datetime
    , @SalesTaxRate_14    numeric
    , @JobSite_15         char(2)
    , @Accessories_16     varchar(100)
    , @Agreement_17       smallint)
    AS 
    
    Declare @Error int
    
    INSERT INTO DBSQL.dbo.JobCards 
           (CustomerID, EmployeeID, PersonReporting, NatureOfProblem, HandedToID, InvoiceNumber
         , DateReceived, DateRequired, MakeAndModel, SerialNumber, SoftwareProblem, DateFinished
         , DatePickedUp, SalesTaxRate, JobSite, Accessories, Agreement)
    VALUES (@CustomerID_1, @EmployeeID_2, @PersonReporting_3, @NatureOfProblem_4, @HandedToID_5, @InvoiceNumber_6
         , @DateReceived_7, @DateRequired_8, @MakeAndModel_9, @SerialNumber_10, @SoftwareProblem_11, @DateFinished_12
         , @DatePickedUp_13, @SalesTaxRate_14, @JobSite_15, @Accessories_16, @Agreement_17)
    
    select @Error = @@error, @jobcardID_18 = @@IDENTITY
    
    if @Error = 1 
      print "Error, Parameter missing"
    
    return @Error
    go
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    thanks paul.. it worked fine..

    i m actually trying to call this from another procedure which will be a master(single record)/detail(multiple records) insert.

    have u tried doing that?

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    yup, I do it all the time.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    can u please post an example?

    this particular one was for the master insert.
    my present requirement is that besides this insert i also have to insert into 3 detail tables(different multiple rows for each).

    I have never done this before and so its taking me a while.
    Can you throw some light please?

    thanks.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    how about this?
    Code:
    use Northwind
    go
    declare @OrderID int, @CustomerID nchar(5), @EmployeeID int, @ShipperID int
          , @Error int, @RowCount int
    
    -- Get some user info
    select @CustomerID = CustomerID From Customers where CompanyName = 'Alfreds Futterkiste'
    select @EmployeeID = EmployeeID from Employees where LastName = 'King' and FirstName = 'Robert'
    select @ShipperID = ShipperID From shippers where CompanyName = 'Speedy Express'
    
    begin transaction
    -- Add an Order Record
    INSERT INTO Orders(CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
    select CustomerID, @EmployeeID, getdate(), datediff(dd,-5,getdate()), datediff(dd,+2,getdate()), @ShipperID, 20, CompanyName, Address, City, Region, PostalCode, Country
      from Customers 
     where CustomerID = @CustomerID
    
    select @Error = @@error
         , @OrderID = @@identity
    
    -- Add three detail records
    INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount)
    select @OrderID, ProductID, UnitPrice, 10, 0
      from Products 
     where ProductName = 'Original Frankfurter grüne Soße'
    
    select @Error = @Error + @@error
    
    INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount)
    select @OrderID, ProductID, UnitPrice, 50, .5
      from Products 
     where ProductName = 'Chocolade'
    
    select @Error = @Error + @@error
    
    INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount)
    select @OrderID, ProductID, UnitPrice, 150, .10
      from Products 
     where ProductName = 'Wimmers gute Semmelknödel'
    
    select @Error = @Error + @@error
    
    -- Test for Success or failure
    if @Error = 0 begin
      commit transaction
    
      -- Print out Order
      select *
        from Orders
       where OrderID = @OrderID
      
      select od.*
        from Orders o
        join [Order Details] od on o.OrderID = od.OrderID
       where o.OrderID = @OrderID
    
    end else begin
      rollback transaction
      raiserror('Unable to add order.',16,1)
    end
    go
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    great...

    only 1 thing..

    I want to pass those values for the detail records from the application. In arrays. since arrays are not supported the next best method would be to pass a comma separated string containing values of each field.

    something like this
    Code:
    Create procedure someproc 
            (jobcardID output, mfield1 [int], mfield2 [varchar](100), noT [int],
                                        tField1[varchar](2000), tfield2[varchar](2000))
    
    as
    Declare mjobcardID int
    declare count int
    declare mtfield1 int
    declare mtfield2 varchar(100)
    exec masterstoredproc mfield1, mfield2
    if @@error = 0 
    Begin
    while count < noT
    Begin
           --do some extracting from tfield1 and tfield2
          mtfield1 = left.....
          mtfield2 = left.....
          exec transStoredProc mjobcardID, mtfield1, mtfield2
    End
    End
    return @@error
    is this the correct way to do it? is this method optimal?

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    more like ...

    Code:
    Create procedure someproc (
      @jobcardID int output
    , @mfield1   int
    , @mfield2   varchar(100)
    , @noT       int
    , @tField1   varchar(2000)
    , @tfield2   varchar(2000))
    
    as
    
    Declare @mjobcardID int
          , @count      int
          , @mtfield1   int
          , @mtfield2   varchar(100)
          , @Return     int
          , @ReturnTmp  int
    
    exec @Return = masterstoredproc mfield1, mfield2
    
    if @Return = 0 Begin
      while @count < noT Begin
           --do some extracting from tfield1 and tfield2
          @mtfield1 = left.....
          @mtfield2 = left.....
          exec @ReturnTmp = transStoredProc mjobcardID, mtfield1, mtfield2
          @Return = @Return + @ReturnTmp
      End
    End
    return @Return
    go
    Is this the correct way to do it? It is a way to do it, not my first choice, but the question is does it work in your environment and is it maintainable?
    Is this method optimal? Can't really say as I don't know your over all design or constraints. My philosophy is get something working end to end and then you can work on improvments.
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    i want to call this stored procedure from a vb application, but at the same time i want to avoid the roundabouts for inserting into detail table using stored procedure.

    coz i would be calling the stored proc for each row in the detail table(s), and thus causing unnecessary network overhead.

    is this method suited for that kind or is there any other better way?

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    sure, why not? As long as your data will fit in the parameters.

    If you end up with more data per row or more detail rows, you could also do a bulk insert into a temporary table and call a proc to insert into the master table and them process the temp table.
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    yes but the bulk insert also will be doing roundtrips which i want to avoid.

    i will post my final BIG stored proc here soon as i m done, so u can help point the flaws there.

    thanks a tonne.

  14. #14
    Join Date
    Mar 2003
    Location
    somewhere between home and office
    Posts
    26
    got that stored procedure done.. a small problem though.. the error handling part is not working..
    if anything goes wrong in the loop then it doesnt handle it properly, the mreturncode variable is not populated as a result the previous inserts go through.

    Also tell me is the explicit conversion of data type better than implicit? i m using explicit conversion only where required.

    Code:
    alter procedure insertJobcard
    (	@JobCardID_0		[int] output,
    	@CustomerID_1 		[int],
    	@EmployeeID_2 		[int],
    	@PersonReporting_3 		[varchar](50),
    	@NatureOfProblem_4 		[char](1),	
    	@HandedToID_5 		[int],
    	@InvoiceNumber_6	 	[numeric],	
    	@DateReceived_7 		[datetime],
    	@DateRequired_8 		[datetime],
    	@MakeAndModel_9 		[varchar](50),
    	@SerialNumber_10 		[varchar](50),
    	@SoftwareProblem_11 		[varchar](255),
    	@DateFinished_12 		[datetime],
    	@DatePickedUp_13 		[datetime],
    	@SalesTaxRate_14 		[numeric],
    	@JobSite_15 		[char](2),
    	@Accessories_16 		[varchar](100),
    	@Agreement_17 		[smallint],
    --for jobcarddetails
    	@JobCardDetailRows_18	[smallint],	 --for number of rows
    	@JobCardDetailID_19		[varchar](1000), --int
    	@Description_20 		[varchar](2000), --varchar(100)
    	@ActionTaken_21 		[varchar](2000), --varchar(100)
    	@AttendedBy_22		[varchar](500),  --int
    	@DateTakenUp_23 		[varchar](1000), --[datetime],
    	@DateFinished_24 		[varchar](1000), --[datetime],
    --for jobcardlabor
    	@JobCardLaborRows_25	[smallint],
    	@JobCardLaborID_26 		[varchar](500), /*[int],*/
    	@EmployeeID_27 		[varchar](500), --[int],
    	@BillableHours_28 		[varchar](500), /*[float]*/
    	@BillingRate_29		[varchar](500), --[money],
    	@Comment_30 		[varchar](3000),--varchar(255)
    --for jobcardparts
    	@JobCardPartsRows_31	[smallint],
    	@JobCardPartID_32 		[varchar](500), /*[int]*/
    	@PartID_33 		[varchar](2000),/*[varchar](20)*/ 
    	@Quantity_34 		[varchar](500), /*[float]*/
    	@UnitPrice_35 		[varchar](500), --[money]
    	@separator char(1) ) 
    AS
    -- for the Master Table
    set nocount on
    Declare @mJobCardID int --the jobcardid which is returned by the stored proc
    Declare @mReturnCode int
    
    exec @mReturnCode = DBSQL.dbo.insert_JobCards 
    	@mJobCardID output, @CustomerID_1, @EmployeeID_2, @PersonReporting_3 ,	
    	@NatureOfProblem_4 ,@HandedToID_5 , @InvoiceNumber_6,
    	@DateReceived_7 , @DateRequired_8 , @MakeAndModel_9 ,
    	@SerialNumber_10 , @SoftwareProblem_11,	@DateFinished_12 ,
    	@DatePickedUp_13 , @SalesTaxRate_14 , @JobSite_15 ,
    	@Accessories_16 ,@Agreement_17
    if @mReturnCode = 0
    Begin
    declare	@separator_position 	int -- This is used to locate each separator character
    it is returned
    declare	@miDetailsCount 	int
    declare	@mJobCardDetailID 	int
    declare	@mDescription 	varchar(100) --varchar(100)
    declare	@mActionTaken 	varchar(100) --varchar(100)
    declare	@mAttendedBy	int  	--int
    declare	@mDateTakenUp 	datetime --[datetime],
    declare	@mDateFinished 	datetime --[datetime],
    --declaration for jobcardLabor
    declare	@mJobCardLaborID 	int
    declare	@mEmployeeID 	int
    declare	@mBillableHours 	float
    declare	@mBillingRate	money
    declare	@mComment 	varchar(255)
    --declaration for jobcardparts
    declare	@mJobCardPartID 	int
    declare	@mPartID 		varchar(20)
    declare	@mQuantity 	float
    declare	@mUnitPrice 	money
    
    set @miDetailsCount=1
    -- Loop through the string searching for separtor characters
    while @miDetailsCount <= @JobCardDetailRows_18 
    --patindex('%' + @separator + '%' , @array) <> 0 
    begin
    
      -- patindex matches the a pattern against a string
      -- extract the values from the arrays which are passed.
      select @separator_position =  patindex('%' + @separator + '%' , @JobCardDetailID_19)
      select @mJobCardDetailID = left(@JobCardDetailID_19, @separator_position - 1) 
      -- This replaces what we just processed with an empty string so that value is not recalculated
      select @JobCardDetailID_19 = stuff(@JobCardDetailID_19, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @Description_20)
      select @mDescription = ltrim(rtrim(left(@Description_20, @separator_position - 1)))
      select @Description_20 = stuff(@Description_20, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @ActionTaken_21)
      select @mActionTaken = ltrim(rtrim(left(@ActionTaken_21, @separator_position-1)))
      select @ActionTaken_21 = stuff(@ActionTaken_21, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @AttendedBy_22)
      select @mAttendedBy = left(@AttendedBy_22, @separator_position-1) 
      select @AttendedBy_22  = stuff (@AttendedBy_22, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @DateTakenUp_23)
      select @mDateTakenUp = left(@DateTakenUp_23, @separator_position-1) 
      select @DateTakenUp_23 = stuff (@DateTakenUp_23, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @DateFinished_24)
      select @mDateFinished = left(@DateFinished_24, @separator_position-1) 
      select @DateFinished_24 = stuff (@DateFinished_24, 1, @separator_position, '')
    
      exec DBSQL.dbo.insert_JobCardDetails 
    	@mJobCardDetailID, @mJobCardID, @mDescription, @mActionTaken, @mAttendedBy,
    	 @mDateTakenUp, @mDateFinished
    
      if @@error <> 0
      Begin
    	set @mReturnCode = (@mReturnCode + @@error)
    	Break
      End
      Set @miDetailsCount = @miDetailsCount + 1
    end -- jobcard Detail while loop
    
    if @mReturnCode = 0 
    Begin
    Set @miDetailsCount = 1
    --For the jobCardLabor table insert
    while (@miDetailsCount <= @JobCardLaborRows_25)
    begin
    
      -- patindex matches the a pattern against a string
      -- extract the values from the arrays which are passed.
      select @separator_position =  patindex('%' + @separator + '%' , @JobCardLaborID_26)
      select @mJobCardLaborID = left(@JobCardLaborID_26, @separator_position - 1) 
      select @JobCardLaborID_26 = stuff(@JobCardLaborID_26, 1, @separator_position, '')
      -- This replaces what we just processed with an empty string so that value is not recalculated
    
      select @separator_position =  patindex('%' + @separator + '%' , @EmployeeID_27)
      select @mEmployeeID = left(@EmployeeID_27, @separator_position - 1) 
      select @EmployeeID_27 = stuff(@EmployeeID_27, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @BillableHours_28)
      select @mBillableHours = left(@BillableHours_28, @separator_position-1) 
      select @BillableHours_28 = stuff(@BillableHours_28, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @BillingRate_29)
      select @mBillingRate = cast(left(@BillingRate_29, @separator_position-1) as money)
      select @BillingRate_29 = stuff (@BillingRate_29, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @Comment_30)
      select @mComment = ltrim(rtrim(left(@Comment_30, @separator_position-1)))
      select @Comment_30 = stuff (@Comment_30 , 1, @separator_position, '')
    
      exec DBSQL.dbo.insert_JobCardLabor 
    	@mJobCardLaborID, @mJobCardID, @mEmployeeID, @mBillableHours, @mBillingRate, @mComment
    
      if @@error <> 0
      Begin
    	SET @mReturnCode = @mReturnCode + @@error
    	Break
      End
      Set @miDetailsCount = @miDetailsCount + 1
    end -- jobcard Labor while loop
    End -- Jobcard Labor IF..
    
    
    if @mReturnCode = 0 
    Begin
    Set @miDetailsCount = 1
    --For the jobCardParts table insert
    while @miDetailsCount <= @JobCardPartsRows_31
    begin
    
      -- patindex matches the a pattern against a string
      -- extract the values from the arrays which are passed.
      select @separator_position =  patindex('%' + @separator + '%' , @JobCardPartID_32)
      select @mJobCardPartID = left(@JobCardPartID_32, @separator_position - 1) 
      -- This replaces what we just processed with an empty string so that value is not recalculated
      select @JobCardPartID_32 = stuff(@JobCardPartID_32, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @PartID_33)
      select @mPartID = left(@PartID_33, @separator_position - 1)
      select @PartID_33 = stuff(@PartID_33, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @Quantity_34)
      select @mQuantity = left(@Quantity_34, @separator_position-1) 
      select @Quantity_34 = stuff(@Quantity_34, 1, @separator_position, '')
    
      select @separator_position =  patindex('%' + @separator + '%' , @UnitPrice_35)
      select @mUnitPrice = cast(left(@UnitPrice_35, @separator_position-1) as money)
      select @UnitPrice_35 = stuff (@UnitPrice_35, 1, @separator_position, '')
    
      exec DBSQL.dbo.insert_JobCardParts
    	@mJobCardPartID, @mJobCardID, @mPartID, @mQuantity, @mUnitPrice
    
      if @@error <> 0
      Begin
    	SET @mReturnCode = @mReturnCode + @@error
    	Break
      End
      Set @miDetailsCount = @miDetailsCount + 1
    end -- jobcard Part while loop
    End -- Jobcard Part IF..
    Set @JobCardID_0 = @mJobCardID
    return @@error
    end--main proc
    set nocount off
    thanks
    Last edited by nihar; 04-04-03 at 04:45.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •