April 8, 2016 at 5:20 am
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
April 8, 2016 at 5:54 am
changing this to dynamic sql implies you need to do the exact same thing, but maybe to other tables with identical structure;
i don't see anything in the procedure that seems to need to be ported to dynamic SQL.
can you tell us WHY you need this changed to dynamic SQL? what are you trying to accomplish?
Lowell
April 8, 2016 at 7:10 am
I have personally read a lot of good articles on this topic (many from sqlservercentral.com itself). One of the things I've learned is to first ask the question, "Do we really need dynamic SQL?". There are certainly times when it is beneficial and the right course of action, but for the most part, there are ways around it depending on your SQL server version. At it's base level, generating dynamic SQL isn't anything more than creating a variable to hold a string, and then building that string one piece at a time until you have the query you want. But you really should research a few thing first to find out if dynamic SQL is the right solution for you. The permissions required on certain objects are different within the scope of a dynamic sql execution than from within a stored proc...will that affect you? Do you risk creating a scenario where SQL injection could occur? How will switching from a stored proc to dynamic SQL affect performance and will it be noticeable? If you choose to go with dynamic SQL, can you at least parameterize it?
I would encourage you to read up on it and make an informed decision rather than just implementing it. You'll need to trust me that even though not many will thank you for the effort of asking "Should we do this?", they'll certainly not hesitate to complain when something goes wrong if you don't.
-G
April 8, 2016 at 7:24 am
Is the reason you're doing this to solve a performance pressure situation?
April 8, 2016 at 7:33 am
I would use dynamic sql if you need to make a query Sargable and the where clause is, I quote from Gail, " Looks like it was drawn up by a committee."
Also, this I would only do for the procs where they are executed too often to consider option(recompile).
Your WHERE clause looks fine so I cannot see a reason why you would want to do DSQL.
If you are having performance issues, try getting rid of the NOT EXISTS and try a LEFT JOIN table WHERE table.column is null
I have heard that the optimiser could have problems calculating statistics when doing an anti-semi-join.
Take care when changing from not exists to left join as you may get more rows.
April 8, 2016 at 9:26 am
Thanks for your reply .I need to change this dynamically because database name differs in both staging and production environment so we will passing database name as a parameter .
April 8, 2016 at 9:34 am
Satiz (4/8/2016)
Thanks for your reply .I need to change this dynamically because database name differs in both staging and production environment so we will passing database name as a parameter .
And in which database will you be creating the proc?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 8, 2016 at 9:40 am
Satiz (4/8/2016)
Thanks for your reply .I need to change this dynamically because database name differs in both staging and production environment so we will passing database name as a parameter .
So the stored procedure name itself isn't changing? What is calling the stored procedure (an application, another stored proc, are you executing a saved script)? In this case, depending on how you go about it, you could dynamically build the stored procedure execute statement in such a way to avoid actually passing a dynamically created query into the engine.
-G
April 8, 2016 at 9:50 am
I will be creating it in staging and will test it if its works fine then i will copy the same procedure and recreate it prod without making any modification in prod
April 8, 2016 at 9:54 am
This procedure will be called in SSIS package .
April 8, 2016 at 9:54 am
Satiz (4/8/2016)
I will be creating it in staging and will test it if its works fine then i will copy the same procedure and recreate it prod without making any modification in prod
Those are environments, not databases.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 8, 2016 at 9:56 am
I apologize greatly if I'm just blind and am missing it, but I don't see where the database is specifically mentioned within the stored procedure. Where exactly would you pass in the database name?
-G
April 8, 2016 at 9:59 am
If you're calling it from an SSIS package, as long as the stored procedure name itself isn't changing, then the connection you've defined will determine which database it is executed under. Perhaps we should be examining how the package is built?
-G
April 8, 2016 at 10:19 am
Hi guys thanks for your replies.I tried to convert it dynamically but it is not working as expected can anyone please guide me .....
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 = 'production'
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
April 8, 2016 at 10:24 am
Phil Parkin (4/8/2016)
Satiz (4/8/2016)
I will be creating it in staging and will test it if its works fine then i will copy the same procedure and recreate it prod without making any modification in prodThose are environments, not databases.
I apologize.You are right. I only have mentioned it wrongly ..
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply