Ayuda con Procedimiento Almacenado,

[warbandit69]

Hola gente, como hago para capturar el valor mayor de un insert dentro de un procedimiento almacenado? y poder usar dicho dato, seria algo asi?

Código:

CREATE PROCEDURE ejemplo
(
@valor_1 VARCHAR(MAX),
@valor_2 VARCHAR(MAX),
.
.
.
@valor_n VARCHAR(MAX)
)
AS
    BEGIN

         INSERT INTO tabla_ejemplo (columna_1, columna_2, ...,columna_n)
         VALUES (@valor_1, @valor_2,...,@valor_n)

         SET @id_tabla_ejemplo = SELET MAX(id) FROM tabla_ejemplo

         INSERT INTO tabla_ejemplo2 (id_requerido, columna_1, columna_2, ...,columna_n)
         VALUES (@valor_1, @valor_2,...,@valor_n)


    END

Lo intente de esta manera pero no me esta tomando el valor, por favor me podrian ayudar a ver como es y muchas gracias por adelantado foreros

[Libras]

Eso es con una funcion

create function name funcion
begin
INSERT INTO tabla_ejemplo (columna_1, columna_2, ...,columna_n)
VALUES (@valor_1, @valor_2,...,@valor_n)

SET @id_tabla_ejemplo = SELET MAX(id) FROM tabla_ejemplo

INSERT INTO tabla_ejemplo2 (id_requerido, columna_1, columna_2, ...,columna_n)
VALUES (@valor_1, @valor_2,...,@valor_n)

return @id_tabla_ejemplo

end


Saludos!!

[warbandit69]

Gracias amigo, mira lo que realice yo, a ver que me aconsejas

CREATE PROCEDURE inserting_new_client
(
@cd_company VARCHAR(MAX),
@cd_email VARCHAR(MAX),
@cd_FirstName VARCHAR(MAX),
@cd_LastName VARCHAR(MAX),
@cd_Terms VARCHAR(MAX),
@cd_DriverLicense VARCHAR(MAX),
@cd_OktoFax BIT,
@cd_BillPreference VARCHAR(MAX),
@custAddress VARCHAR(MAX),
@custCellNumber VARCHAR(MAX),
@custCity VARCHAR(MAX),
@custFax VARCHAR(MAX),
@custGaragecode VARCHAR(MAX),
@custPhone VARCHAR(MAX),
@custState VARCHAR(MAX),
@custWorkNumber VARCHAR(MAX),
@custZipCode VARCHAR(MAX),
@cust_gatecode VARCHAR(MAX),
@billAddress VARCHAR(MAX),
@billCity VARCHAR(MAX),
@billFax VARCHAR(MAX),
@billName VARCHAR(MAX),
@billPhone VARCHAR(MAX),
@billState VARCHAR(MAX),
@billZipCode VARCHAR(MAX),
@shipAddess VARCHAR(MAX),
@shipCity VARCHAR(MAX),
@shipContactName VARCHAR(MAX),
@shipFax VARCHAR(MAX),
@shipPhone VARCHAR(MAX),
@shipState VARCHAR(MAX),
@shipZipCode VARCHAR(MAX),
@fad_AnnualService VARCHAR(MAX),
@fad_CarbonChange VARCHAR(MAX),
@fad_Delivery VARCHAR(MAX),
@fad_EmergencyServiceCall VARCHAR(MAX),
@fad_Estimate VARCHAR(MAX),
@fad_HourlyLaborFee VARCHAR(MAX),
@fad_Installation VARCHAR(MAX),
@fad_IronBlockmediaChange VARCHAR(MAX),
@fad_Membrain VARCHAR(MAX),
@fad_MonthlyService VARCHAR(MAX),
@fad_PotassiumService VARCHAR(MAX),
@fad_ResinChange VARCHAR(MAX),
@fad_ROFilters VARCHAR(MAX),
@fad_SaltDelivery VARCHAR(MAX),
@fad_ServiceCall VARCHAR(MAX),
@fad_WarrantedService VARCHAR(MAX),
@fad_WaterAnalysis VARCHAR(MAX),
@fd_TaxRate VARCHAR(MAX),
@ss_s_MonthlyService VARCHAR(MAX),
@ss_s_CarbonChange VARCHAR(MAX),
@ss_s_Delivery VARCHAR(MAX),
@ss_s_Installation VARCHAR(MAX),
@ss_s_Membrain VARCHAR(MAX),
@ss_s_PotassiumService VARCHAR(MAX),
@ss_s_ResinChange VARCHAR(MAX),
@ss_s_ROFilters VARCHAR(MAX),
@ss_s_SaltDelivery VARCHAR(MAX),
@ss_s_ServiceCall VARCHAR(MAX),
@ss_s_WarrantedService VARCHAR(MAX),
@ss_s_AnnualService VARCHAR(MAX),
@ss_SaltDelivery VARCHAR(MAX),
@ss_ServiceCall VARCHAR(MAX),
@ss_AnnualService VARCHAR(MAX),
@ss_CarbonChange VARCHAR(MAX),
@ss_Delivery VARCHAR(MAX),
@ss_Installation VARCHAR(MAX),
@ss_Membrain VARCHAR(MAX),
@ss_MonthlyService VARCHAR(MAX),
@ss_PotassiumService VARCHAR(MAX),
@ss_ResinChange VARCHAR(MAX),
@ss_ROFilters VARCHAR(MAX),
@ss_WarrantedService VARCHAR(MAX),
@ld_AnnualService DATE,
@ld_CarbonChange DATE,
@ld_Delivery DATE,
@ld_Installation DATE,
@ld_Membrain DATE,
@ld_MonthlyService DATE,
@ld_PotassiumService DATE,
@ld_ResinChange DATE,
@ld_ServiceCall DATE,
@ld_WarrantedService DATE,
@ld_ROFilters DATE,
@ld_SaltDelivery DATE,
@r_AnnualService BIT,
@r_CarbonChange BIT,
@r_Membrain BIT,
@r_monthlyservice BIT,
@r_ResinChange BIT,
@r_ROFilters BIT,
@r_SaltDelivery BIT,
@pd_PumpMake VARCHAR(MAX),
@pd_PumpModel VARCHAR(MAX),
@pd_PumpSerial VARCHAR(MAX),
@pd_InstallDate DATE,
@pd_WarrantyExpires DATE,
@pd_GPD VARCHAR(MAX),
@pd_Speed VARCHAR(MAX),
@pd_Stroke VARCHAR(MAX),
@fd_Type VARCHAR(MAX),
@fd_WarrantyExpires DATE,
@fd_InstallDate DATE,
@fd_Regens VARCHAR(MAX),
@fd_RegensGallons VARCHAR(MAX),
@Directions VARCHAR(MAX),
@AccountAlerts VARCHAR(MAX),
@Notes VARCHAR(MAX),
@fd_AWT BIT
)
AS

BEGIN TRY


DECLARE @sql_id NUMERIC

INSERT INTO w_clients_1
(company, email, driverlicense, fistname, lastname, terms, oktofax, billpreference)
VALUES (@cd_company, @cd_email, @cd_DriverLicense, @cd_FirstName, @cd_LastName, @cd_Terms, @cd_OktoFax, @cd_BillPreference);

COMMIT;


SET @sql_id = (SELECT MAX(p.id_client)
FROM waterdatabase.w_clients_1);



INSERT INTO w_clients_customer
(id_client,cust_address, cust_city, cust_phone, cust_cell, cust_gatecode, cust_state, cust_fax, cust_worknumber, cust_garagecode, cust_zipcode)
VALUES (@sql_id,@custAddress,@custCity,@custPhone,@custCe llNumber,@cust_gatecode,@custState,@custFax,@custW orkNumber,@custGaragecode,@custZipCode);



INSERT INTO w_clients_billing
(id_client, bill_contact, bill_city, bill_phone, bill_address, bill_state, bill_fax, bill_zipcode)
VALUES (@sql_id,@billName,@billCity,@billPhone,@billAddre ss,@billState,@billFax,@billZipCode);



INSERT INTO w_clients_shiping
(id_client, ship_contact, ship_city, ship_phone, ship_address, ship_state, ship_fax, ship_zipcode)
VALUES (@sql_id,@shipContactName,@shipCity,@shipPhone,@sh ipAddess,@shipState,@shipFax,@shipZipCode);




/* Creo que este Insert esta de mas */
INSERT INTO fees_defaults
(id_client, hourlylaborfee, saltdelivery, potassiumservice, installation, carbonchange, rofilters, wateranalysis, emergencyservicecall, taxrate, annualservice,
servicecall, warrantedservice, delivery, resinchange, membrain, estimate, ironblockmediachange)
VALUES (@sql_id,@fad_HourlyLaborFee,@fad_SaltDelivery,@fa d_PotassiumService,@fad_Installation,@fad_CarbonCh ange,@fad_ROFilters,@fad_WaterAnalysis,@fad_Emerge ncyServiceCall,@fd_TaxRate,@fad_AnnualService,@fad _ServiceCall,@fad_WarrantedService,@fad_Delivery,@ fad_ResinChange,@fad_Membrain,@fad_Estimate,@fad_I ronBlockmediaChange);


INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Monthly Service',@ld_MonthlyService,N'Every ' + @ss_s_MonthlyService + N' ' + @ss_MonthlyService,null,@fad_MonthlyService,@r_mon thlyservice);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Annual Service',@ld_AnnualService,N'Every ' + @ss_s_AnnualService + N' ' + @ss_AnnualService,null,@fad_AnnualService,@r_Annua lService);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Salt Delivery',@ld_SaltDelivery,N'Every ' + @ss_s_SaltDelivery + N' ' + @ss_SaltDelivery,null,@fad_SaltDelivery,@r_SaltDel ivery);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Service Call',@ld_ServiceCall,N'Every ' + @ss_s_ServiceCall + N' ' + @ss_ServiceCall ,null,@fad_ServiceCall,null);


INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Carbon Change',@ld_CarbonChange,N'Every ' + @ss_s_CarbonChange + N' ' + @ss_CarbonChange ,null,@fad_CarbonChange,@r_CarbonChange);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Resin Change',@ld_ResinChange,N'Every ' + @ss_s_ResinChange + N' ' + @ss_ResinChange ,null,@fad_ResinChange,@r_ResinChange);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'R/O Filters',@ld_ROFilters,N'Every ' + @ss_s_ROFilters + N' ' + @ss_ROFilters ,null,@fad_ROFilters,@r_ROFilters);




INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Membrain',@ld_Membrain,N'Every ' + @ss_s_Membrain + N' ' + @ss_Membrain ,null,@fad_Membrain,@r_Membrain);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Emergency Service Call',null,null ,null,@fad_EmergencyServiceCall,null);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Potassium Service',@ld_PotassiumService,N'Every ' + @ss_s_PotassiumService + N' ' + @ss_PotassiumService ,null,@fad_PotassiumService,null);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Warranted Service',@ld_WarrantedService,N'Every ' + @ss_s_WarrantedService + N' ' + @ss_WarrantedService ,null,@fad_WarrantedService,null);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Installation',@ld_Installation,N'Every ' + @ss_s_Installation + N' ' + @ss_Installation ,null,@fad_Installation,null);



INSERT INTO customerschedule
(idcustomer, service, last_date, when_schedule, forecast_date, package_schedule, recurring)
VALUES (@sql_id,N'Delivery',@ld_Delivery,N'Every ' + @ss_s_Delivery + N' ' + @ss_Delivery ,null,@fad_Delivery,null);



INSERT INTO w_pump_client_details
(id_client, pd_PumpMake, pd_PumpModel, pd_PumpSerial, pd_InstallDate, pd_WarrantyExpires, pd_GPD, pd_Speed, pd_Stroke, fd_Type,
fd_WarrantyExpires, fd_InstallDate, fd_Regens, fd_RegensGallons,fd_AWT)
VALUES (@sql_id,@pd_PumpMake,@pd_PumpModel,@pd_PumpSerial ,@pd_InstallDate,@pd_WarrantyExpires,@pd_GPD,@pd_S peed,@pd_Stroke,@fd_Type,@fd_WarrantyExpires,@fd_I nstallDate,@fd_Regens,@fd_RegensGallons,@fd_AWT);



INSERT INTO w_client_moreinfo
(id_client, Directions, AccountAlerts, Notes)
VALUES (@sql_id,@Directions,@AccountAlerts,@Notes);

COMMIT;

END TRY

BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;


END CATCH

GO