bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.
easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>
|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegro ups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?

> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/technetmag/issues/2006/01/BoostPerformance/default.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?
> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

bulk insert vs CLR stored proc doing transactioned insert

I have 1) a windows application (which was the test bed used for
building this code) that takes some data and expands them into much
larger datasets and inserts them into another table. An input table of
81 rows produces 12,000 resulting rows. I then created a CLR Stored
procedure to do the equivalent task, based on this code,which rather
than using the bulk insert, creates inserts within a transaction.
The windows application completes this operation in 1.9 secs on
average.
The CLR stored procedure completes this operation in 3.2 secs on
average.
I want to optimise this as much as possible, as it is a crucial
operation processing large amounts of data (30 million rows output/
day) - is it possible to re-produce the bulk insert type performance
levels achieved by the vanilla .net Win app within a CLR stored
procedure? I realise bulk insert is highly optimised, but i would be
disappointed if it were not to produce better performance from a CLR
stored procedure running within the sql server context.
Thanks for reading.easyfx,
Actually, there is no reason to expect the CLR to be faster than an
optimized special purpose statement like BULK INSERT. In general, for data
intensive work the CLR is not as fast as pure T-SQL.
The CLR is best used for things that are difficult in TSQL. See the
following article for a brief discussion:
http://www.microsoft.com/technet/te...l
t.aspx
RLF
"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
> Thanks for reading.
>|||"easyfx" <easyforexsignals@.gmail.com> wrote in message
news:1174984934.074902.172730@.o5g2000hsb.googlegroups.com...
>I have 1) a windows application (which was the test bed used for
> building this code) that takes some data and expands them into much
> larger datasets and inserts them into another table. An input table of
> 81 rows produces 12,000 resulting rows. I then created a CLR Stored
> procedure to do the equivalent task, based on this code,which rather
> than using the bulk insert, creates inserts within a transaction.
> The windows application completes this operation in 1.9 secs on
> average.
> The CLR stored procedure completes this operation in 3.2 secs on
> average.
> I want to optimise this as much as possible, as it is a crucial
> operation processing large amounts of data (30 million rows output/
> day) - is it possible to re-produce the bulk insert type performance
> levels achieved by the vanilla .net Win app within a CLR stored
> procedure? I realise bulk insert is highly optimised, but i would be
> disappointed if it were not to produce better performance from a CLR
> stored procedure running within the sql server context.
>
Well considering that Bulk Insert was written by the same team that wrote
the engine, has survived a lot of optimizations, I suspect you'll have a
hard time matching its performance.
Why not use bulk insert itself?

> Thanks for reading.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

BULK insert from a text file name stored into a variable

Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @.filename

When I try to run the bulk insert instruction ... from @.filename it doesn't work..
do you know why?

Thank you in advanceAccoding to the destructions for BULK INSERT (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_4fec.asp), it takes a literal string for the file name. To sidestep this requirement, you can resort to dynamic SQL using the EXECUTE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) character string syntax. It isn't pretty, but it should get the job done for you.

-PatP

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

Building Dynamic Sql in Stored Proc issue

Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECT N_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROM dbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this? Help!!

ThanksYou can store your parameters into a 15-column temporary table and then join the main query with it.|||Alternativley...use and exec statemnt ( not for the weak of stomach )

declare @.var_1 varch(10), @.var_2 varchar (10)
set @.var_1 = 'value_1'
set @.var_2 = 'value_2'

exec( ' select * from table where x = ' +@.var_1+ ' and y = ' +@.var_2+ '')

so :

exec('..............
Where upper(title) like %'+@.parameter_value'%
and company_id = '@.parameter_value'
and Nmt_practice_code = '@.parameter_value'')

You effectively have to enclose the whole statement in an exec statement.
Its messy this way, and you will take a while to debig to get it right.

rdjabarov has a good idea.....its more elegant than my approach....

Build Dynamic Query Using sp_executesql

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @.PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @.SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@.PracticeCode+' SELECT
SurgeryKey,'+
@.extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @.SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

ThanksManaged to figure it out all by myself. Think I was a bit premature in posting this one...sorry :-)

Here's my solution in case anyone is intertested:

DECLARE @.SQLString nvarchar(500)
DECLARE @.Parameters nvarchar(500)


SET @.Parameters = '@.Date DateTime'
-- insert data into proper tables with extract date added
SET @.SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@.PracticeCode+' SELECT
SurgeryKey,
@.Date,
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql@.SQLString,@.Parameters,@.Date= @.ExtractDate

Build conditional WHERE clause

Hi All,

I'm building a simple stored proc to be the basis of an SQL Report.

I've set up my parameters to default to NULL, as the users will have the option to filter the parameters to build the report. I have 2 datetime params which are causing me a problem.

I have a condition which checks if these 2 params are NULL, if they are, it just continues and executes the SQL query, and all is well. However, if they contain dates, I am trying to build a where condition in the variable named @.where_clause, and then I append this to the end of my SQL statement.

- If my @.where_clause is empty, the stored proc returns the desired results
- If the @.where_clause is NOT empty, my stored proc executes but returns NO results
- And last, if I modify the stored proc and remove the WHERE clause completely, and just add the "AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time". to the end of my statement in plain SQL, then it works.

** It only seems to not work when I put build my WHERE clause in a variable **

I hope this is not too confusing.

Here is the stored proc as it is now....not working.

ALTER PROCEDURE [dbo].[spReport_ProductSerialAssemblyResults]
-- Add the parameters for the stored procedure here
@.pec varchar(30) = NULL,
@.rel varchar(30) = NULL,
@.ver varchar(10) = NULL,
@.sn varchar(50) = NULL,
@.start_time datetime = NULL,
@.end_time datetime = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @.where_clause varchar(MAX)

set @.where_clause = '';

if ((@.start_time is not null) and (@.end_time is not null))
BEGIN
set @.where_clause = ' AND tblProducts.start_time >= @.start_time AND tblProducts.start_time <= @.end_time';
END



-- Insert statements for procedure here
SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1) AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial) + @.where_clause;


END

To do what you are trying to do where you take a character variable and execute it as SQL code you need to use dynamic SQL. See the EXECUTE statement, and the sp_executesql stored procedure for info on dynamic SQL.

However, in your case dynamic SQL is not necessary (and it should be avoided unless necessary). What you can do in your SELECT statement is simply this

SELECT * FROM tblProducts
WHERE (tblProducts.in_production = 1)
AND tblProducts.pec = COALESCE(@.pec, tblProducts.pec)
AND tblProducts.release = COALESCE(@.rel, tblProducts.release)
AND tblProducts.version = COALESCE(@.ver, tblProducts.version)
AND tblProducts.serial = COALESCE(@.sn, tblProducts.serial)
AND (tblProducts.start_time >= @.start_time
AND tblProducts.start_time <= @.end_time
OR (@.start_time IS NULL OR @.end_time IS NULL))

|||

Please take a look at the link below for various solutions that doesn't require dynamic SQL code.

http://www.sommarskog.se/dyn-search.html

|||Thank you both for the great feedback...