Hi Experts I have a requirement to convert the normal procedure to Dynamic sql .I have converted it but it is not working as expected could any one help me in doing this .
Thanks in advance
Normal Stored Procedure
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
Converted Dynamic SQL
ALTER procedure [dbo].[load_orders] @member varchar(255), @id varchar(255)
As
BEGIN
--SET NOCOUNT ON
DECLARE
@sqlStringvariables varchar(max),
--the select statement
@sqlStringSelect varchar(max),
--Insert Statement
@sqlStringInsert varchar(max),
--Catch
@sqlStringcatch varchar(max),
--update
@sqlStringupdate varchar(max),
--stage or production, to get the correct connect
@connection varchar(20) = case
when @@ServerName = 'Staging'
then '[order_Test]'
when @@ServerName = 'p'
then '[Orders]'
else ''
end
Set @sqlStringvariables='
Declare
--to hold value upon success (1) or fail (2) insert for hasLoaded, default 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 '+@connection+'.[dbo].[temp_raw_orders] where loaded = 0;'
Set @sqlStringInsert='
INSERT INTO '+@connection+'.dbo.Final_Orders
(
[member]
,[id]
,[FirstName]
,[LastName]
,[MiddleName]
,[DOB]
,[Gender]
,[HomePhone]
,[WorkPhone]
,[CellPhone]
,[PreferredPhone]
,[Street1]
,[Street2]
,[City]
,[State]
,[Zip]
)'
Set @sqlStringSelect='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]
,(ord.HomePhone, 10) [HomePhone]
,(ord.WorkPhone, 10) [WorkPhone]
,(ord.CellPhone, 10) [CellPhone]
,'+@connection+'.[dbo].[fn_RemoveNonNumeric_len](
IIF(isnull(ord.PreferredPhone,'''')<>'''',ord.PreferredPhone,
(IIF(isnull(ord.[CellPhone],'''')<>'''',ord.[CellPhone],
IIF(isnull(ord.[HomePhone],'''')<>'''',ord.[HomePhone],
IIF(isnull(ord.[WorkPhone],'''')<>'''',ord.[WorkPhone],''''))))),10) [PreferredPhone]
,LEFT(ord.[Street1],100) [PStreet1]
,LEFT(ord.[Street2],100) [Street2]
,LEFT(ord.[City],50) [City]
,left(ord.[state],2) [state]
,ord.[Zip][Zip]
from '+@connection+'.[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 '+@connection+'.[dbo].[Final_Orders] ro
WHERE ro.member = '''+@member+'''
AND ro.id ='''+@id+'''
AND ro.recordID = ord.recordID
)'
set @sqlStringcatch='
--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()'
set @sqlStringupdate='
IF @err_num = 0
BEGIN
SET @hasloadedResult = 1;
END
--if the insert has failed
ELSE
BEGIN
SET @hasloadedResult = 2;
END
UPDATE '+@connection+'.[dbo].[temp_raw_Orders]
SET HasLoaded = @hasloadedResult
FROM '+@connection+'.[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'
BEGIN TRY
Exec (@sqlStringvariables+@sqlStringInsert+@sqlStringSelect+@sqlStringupdate)
END TRY
BEGIN CATCH
Exec (@sqlStringvariables+@sqlStringcatch)
END CATCH
end