How to convert this procedure as dynamic SQL

Question

Post reply

How to convert this procedure as dynamic SQL

Satiz

Mr or Mrs. 500

Points: 508
More actions
April 8, 2016 at 5:18 am

#309777

Hi Experts I have a requirement to convert the normal procedure to Dynamic sql could you please help me in doing this Thanks in advance.
Create procedure [dbo].[load_orders] @member varchar(255), @id varchar(255)
As
Begin
Declare
--to hold value upon success (1) or fail (2) insert for hasLoaded, defualt to 0
@hasloadedResult int = 0,
--defaulting to 0
@max_RawOrderKey bigint = 0,
--the error variables
@err_num int = 0,
@err_sev int,
@err_state int ,
@err_pro varchar(100),
@err_lin int,
@err_mes varchar(2500)
SELECT @max_RawOrderKey = CONVERT(BIGINT, ISNULL(MAX(rawKey), 0)) FROM [dbo].[temp_raw_orders] where loaded = 0;
Begin try
INSERT INTO dbo.Final_Orders
(
[FirstName]
,[LastName]
,[MiddleName]
,[DOB]
,[Gender]
,[HomePhone]
,[WorkPhone]
,[CellPhone]
,[PreferredPhone]
,[Street1]
,[Street2]
,[City]
,[State]
,[Zip]
)
Select
@member[member]
,@id [id]
,LEFT(ord.FirstName,100) [FirstName]
,LEFT(ord.LastName,100) [LastName]
,''[MiddleName]
, (ord.DOB) [DOB]
, CASE WHEN Isnull(Gender,'') = 'Male' Then 'M'
WHEN Isnull(Gender,'') = 'Female' Then 'F'
ELSE 'N' END [Gender]
,LEFT(ord.HomePhone, 10) [HomePhone]
,LEFT(ord.WorkPhone, 10) [WorkPhone]
,LEFT(ord.CellPhone, 10) [CellPhone]
,IIF(isnull(ord.PreferredPhone,'')<>'',ord.PreferredPhone,
(IIF(isnull(ord.[CellPhone],'')<>'',ord.[CellPhone],
IIF(isnull(ord.[HomePhone],'')<>'',ord.[HomePhone],
IIF(isnull(ord.[WorkPhone],'')<>'',ord.[WorkPhone],''))))) [PreferredPhone]
,LEFT(ord.[Street1],100) [Street1]
,LEFT(ord.[Street2],100) [Street2]
,LEFT(ord.[City],50) [City]
,left(ord.[state],2) [state]
, ord.[Zip] Zip
FROM [dbo].[temp_raw_Orders] ord
LEFT OUTER JOIN dbo.Load_tb lp ON ( lp.member =@member AND lp.id=@id )
WHERE ord.HasLoaded=0
AND ord.Rawkey<=@max_RawOrderKey
AND NOT EXISTS (
SELECT 1 FROM dbo.Final_Orders ro
WHERE ro.member = @member
AND ro.id =@id
AND ro.RecordID = ord.RecordID
)
end try
BEGIN CATCH
--pop the error vars
select
@err_num = ERROR_NUMBER(),
@err_sev = ERROR_SEVERITY() ,
@err_state = ERROR_STATE() ,
@err_pro = coalesce(ERROR_PROCEDURE(), '[dbo].[load_orders]' ),
@err_lin = ERROR_LINE() ,
@err_mes = ERROR_MESSAGE()
END CATCH
--the insert was successful
IF @err_num = 0
BEGIN
SET @hasloadedResult = 1;
END
--if the insert has failed
ELSE
BEGIN
SET @hasloadedResult = 2;
END
--Make sure the hasLoaded Flag is int in the temp RAW table
UPDATE [dbo].[temp_raw_Orders]
SET HasLoaded = @hasloadedResult
FROM [dbo].[temp_raw_Orders]
WHERE
rawKey <= @max_RawOrderKey
and
hasloaded = 0;
--raise an error if insert failed after the correct update is performed
IF @hasloadedResult = 2
BEGIN
--to hold the error message
DECLARE @err_str varchar(max) = 'The Insert failed for procedure '+@err_pro+' on line '+cast(@err_lin as varchar(5))+' with error number '+cast(@err_num as varchar(5))+'.'+
'The error has the following message: '+@err_mes;
--raise the error
RAISERROR( @err_str, @err_sev, @err_state );
end
end