Hej på er..
Jag har suttit i en vecka och trasslat med Stored procedures. Har sökt lite och plockat lite allt eftersom jag hittat dem.. Mitt stora problem nu är att min SP returnerar två värden (jag vill bara ha tillbaka ett).
Då jag anropar SP'n med asp kod så använder jag inte "ADODB.command utan returnerar endast ett id soom jag plockar in mha av ettt recordset..
Felet jag får när SP'n anropas är: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
En anna fråga är och hur jag i SP koden tilldelar variabler värden? Ska jag använda Set eller SELECT, som synes i koden nedan kör jag med select och sista raden är den som returnerar id't.
Sen har jag tre sql frågor på rad som tillderlar variablerna @foundstatus, @foundid och @foundor värden, borde det inte gå slå ihop dem till en fråga?
USE [101078-7]
GO
/****** Object: StoredProcedure [dbo].[usp_findOrderRow] Script Date: 08/28/2011 13:50:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name: usp_findOrderHead
Description: Find matching orderrow and ordernumber. If match returns "" otherwise new row is created and new ID is returned
createsAuthor: Modification Log: Change
Description Date Changed By
Created procedure 2011-08-10 */
ALTER PROCEDURE [dbo].[usp_findOrderRow]
@order_line int = NULL,
@order_head int = NULL,
@prod_week smallint = NULL,
@door smallint = NULL,
@amount tinyint = NULL,
@door_type varchar(3) = NULL,
@day_width int = NULL,
@day_height int = NULL,
@in_out bit = NULL,
@door_frame varchar(1) = NULL,
@amount_glas tinyint = NULL,
@glas_placement int = NULL,
@window_space int = NULL,
@type_of_frame_door varchar(1) = NULL,
@folded_door varchar(1) = NULL,
@part_left varchar(6) = NULL,
@communello bit = NULL,
@type_communello varchar(1) = NULL,
@Wdoor_lock bit = NULL,
@lock_type varchar(4) = NULL,
@electric bit = NULL,
@electric_type varchar(2) = NULL,
@outer_color smallint = NULL,
@inner_color smallint = NULL,
@bolt varchar(1) = NULL,
@frame smallint = NULL,
@barn_package varchar(4) = NULL,
@instructions varchar(1) = NULL,
@removed bit = NULL,
@type_of_glas varchar(3) = NULL,
@water_class tinyint = NULL,
@air_class tinyint = NULL,
@wind_class tinyint = NULL,
@u_value float = NULL,
@status tinyint = NULL,
@prodplan_id int = NULL,
@pos smallint = NULL,
@ghost int = null
AS
DECLARE @foundStatus int, @foundid int, @foundOr int
SET @foundStatus = (SELECT status from pd_order_eco WHERE order_head=@order_head AND order_line=@order_line AND not @amount=0)
SET @foundId = (SELECT id from pd_order_eco WHERE order_head=@order_head AND order_line=@order_line AND not @amount=0)
SET @foundOr = (SELECT order_line from pd_order_eco WHERE order_head=@order_head AND order_line=@order_line AND not @amount=0)
IF NOT @foundStatus IS NULL AND @foundStatus < 2 AND @ghost=0 -- Orderrad finns men kan ersättas, ta bort befintlig rad, lägger till ny
BEGIN
DELETE FROM pd_inventory WHERE pp_id IN (SELECT id FROM pd_mounting_details WHERE order_data IN (SELECT id from pd_order_eco WHERE order_head=@order_head AND order_line=@order_line))
DELETE FROM pd_mounting_details WHERE order_data IN (SELECT id from pd_order_eco WHERE order_head=@order_head AND order_line=@order_line)
DELETE FROM pd_order_eco WHERE order_head=@order_head AND order_line=@order_line
INSERT INTO pd_order_eco (order_line,order_head,prod_week,door,amount,door_type,day_width,day_height,in_out,door_frame,amount_glas,glas_placement,window_space,type_of_frame_door,folded_door,part_left,communello,type_communello,Wdoor_lock,lock_type,electric,electric_type,outer_color,inner_color,bolt,frame,barn_package,instructions,removed,type_of_glas,water_class,air_class,wind_class,u_value,status,prodplan_id,pos)
VALUES (@order_line,@order_head,@prod_week,@door,@amount,@door_type,@day_width,@day_height,@in_out,@door_frame,@amount_glas,@glas_placement,@window_space,@type_of_frame_door,@folded_door,@part_left,@communello,@type_communello,@wdoor_lock,@lock_type,@electric,@electric_type,@outer_color,@inner_color,@bolt,@frame,@barn_package,@instructions,@removed,@type_of_glas,@water_class,@air_class,@wind_class,@u_value,@status,@prodplan_id,@pos)
Select @foundid=SCOPE_IDENTITY()
END
ELSE IF @ghost=1
BEGIN
INSERT INTO pd_order_eco (order_line,order_head,prod_week,door,amount,door_type,day_width,day_height,in_out,door_frame,amount_glas,glas_placement,window_space,type_of_frame_door,folded_door,part_left,communello,type_communello,Wdoor_lock,lock_type,electric,electric_type,outer_color,inner_color,bolt,frame,barn_package,instructions,removed,type_of_glas,water_class,air_class,wind_class,u_value,status,prodplan_id,pos)
VALUES (@order_line,@order_head,@prod_week,@door,@amount,@door_type,@day_width,@day_height,@in_out,@door_frame,@amount_glas,@glas_placement,@window_space,@type_of_frame_door,@folded_door,@part_left,@communello,@type_communello,@wdoor_lock,@lock_type,@electric,@electric_type,@outer_color,@inner_color,@bolt,@frame,@barn_package,@instructions,@removed,@type_of_glas,@water_class,@air_class,@wind_class,@u_value,@status,@prodplan_id,@pos)
Select @foundid=SCOPE_IDENTITY()
END
ELSE IF @foundStatus IS NULL
BEGIN
INSERT INTO pd_order_eco (order_line,order_head,prod_week,door,amount,door_type,day_width,day_height,in_out,door_frame,amount_glas,glas_placement,window_space,type_of_frame_door,folded_door,part_left,communello,type_communello,Wdoor_lock,lock_type,electric,electric_type,outer_color,inner_color,bolt,frame,barn_package,instructions,removed,type_of_glas,water_class,air_class,wind_class,u_value,status,prodplan_id,pos)
VALUES (@order_line,@order_head,@prod_week,@door,@amount,@door_type,@day_width,@day_height,@in_out,@door_frame,@amount_glas,@glas_placement,@window_space,@type_of_frame_door,@folded_door,@part_left,@communello,@type_communello,@wdoor_lock,@lock_type,@electric,@electric_type,@outer_color,@inner_color,@bolt,@frame,@barn_package,@instructions,@removed,@type_of_glas,@water_class,@air_class,@wind_class,@u_value,@status,@prodplan_id,@pos)
Select @foundid=SCOPE_IDENTITY()
END
else
begin
SELECT @foundid=1
end
select @foundid
Blev många frågor nu känner jag men alla tips och ideér är tacksamma...
Mvh