bulk insert and trigger

Some application insert record to MyTable using the bulk insert statement
without the clause "With fire_triggers".In MyTable i need that the triggers
fire.
I can't modify the bulk insert statement. How i can fire the triggers on my
table?
thx
In this case, you need to run sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Building a Where Clause

I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'

IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'

IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'

IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'

IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'

IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'

IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'

IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'

IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'

IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'

IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where

END

To answer my own post, this is how you build a dynamic query in a stored proc:

ALTERPROCEDURE [dbo].[cg_JobSearch]

-- Add the parameters for the stored procedure here

@.JobNoStart varchar(10)='',

@.JobNoEnd varchar(10)= @.JobNoStart,

@.OrderDateStart varchar(10)='',

@.OrderDateEnd varchar(10)= @.OrderDateStart,

@.DueDateStart varchar(10)='',

@.DueDateEnd varchar(10)= @.DueDateStart,

@.ProofDateStart varchar(10)='',

@.ProofDateEnd varchar(10)= @.ProofDateStart,

@.CloseDateStart varchar(10)='',

@.CloseDateEnd varchar(10)= @.CloseDateStart,

@.CogsDateStart varchar(10)='',

@.CogsDateEnd varchar(10)= @.CogsDateStart,

@.ProductName varchar(200)='',

@.CustomerNumber varchar(12)='',

@.SalesPerson varchar(3)='',

@.CSR varchar(15)='',

@.Closedint= 0,

@.Canceledint= 0,

@.FinalShipint= 0,

--@.Invoiced int = 0,

@.where varchar(8000)='',

@.sql varchar(8000)=''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd

IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd

IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd

IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd

IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd

IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd

IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName

IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber

IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson

IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR

IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'

SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'

SET @.sql= @.sql+ @.where

PRINT @.sql

EXEC(@.sql)

END

|||

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...

Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))

The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.

If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.

|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||

This is how I ended up doing mine.

CREATE PROCEDURE [dbo].[pe_getAppraisals]

-- Add the parameters for the stored procedure here

@.PTypenvarChar(500),

@.ClientnvarChar(500),

@.PageSizeINT

AS

DECLARE

@.l_SelectnvarChar(4000),

@.l_FromnvarChar(4000),

@.l_SetWherebit,

@.l_PTypenvarChar(500),

@.l_ClientnvarChar(500)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

--Initialize SetWhere to test if a parameter has Added the keyword WHERE

--Initialize the Where statement in case all parameters are null

SET @.l_SetWhere= 0

--Create WHERE portion of the SQL SELECT Statement

IF(@.PTypeISNOTNULL)AND(@.PType<>'')

BEGIN

SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType

SET @.l_SetWhere= 1

End

ELSESET @.PType=''

IF(@.ClientISNOTNULL)AND(@.Client<>'')

BEGIN

IF @.l_SetWhere= 0

BEGIN

SET @.l_Client=' WHERE o.ClientID='+ @.Client

SET @.l_SetWhere= 1

END

ELSESET @.l_Client=' AND o.ClientID='+ @.Client

END

ELSESET @.l_Client=''

--Build the SQL SELECT Statement

SET @.l_Select=

'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,

o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,

o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,

o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,

o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,

ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'

SET @.l_From=

'Orders AS o LEFT OUTER JOIN

OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN

OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN

OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN

OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN

PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN

PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN

Logins AS l ON o.AppraiserID = l.LoginID'

Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)

Building a conditional WHERE clause

Hello experts, I have a sproc that gets several params, default is null. I'
d
like to build a WHERE clause on the fly for just the params with values.
Something like
SELECT * from foo
WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)IF is a control flow statement... You cannot use it in a query.
How about :
WHERE a = COALESCE(@.a, a)
AND b = COALESCE(@.b, b)
You can also use CASE but it's a bit more drawn out:
WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
(And the latter will almost certainly lead to scans rather than ss, if a
and/or be is indexed.)
A
On 3/5/05 12:45 PM, in article
3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
<Coffeeguy@.discussions.microsoft.com> wrote:

> Hello experts, I have a sproc that gets several params, default is null.
I'd
> like to build a WHERE clause on the fly for just the params with values.
> Something like
> SELECT * from foo
> WHERE if( @.a IS NOT NULL foo.a = @.a) and if( @.b IS NOT NULL foo.b = @.b)
>
>|||Aaaah, thanks!
"Aaron [SQL Server MVP]" wrote:

> IF is a control flow statement... You cannot use it in a query.
> How about :
> WHERE a = COALESCE(@.a, a)
> AND b = COALESCE(@.b, b)
> You can also use CASE but it's a bit more drawn out:
> WHERE a = CASE WHEN @.a IS NOT NULL THEN @.a ELSE a END
> AND b = CASE WHEN @.b IS NOT NULL THEN @.b ELSE b END
> (And the latter will almost certainly lead to scans rather than ss, if
a
> and/or be is indexed.)
> A
>
> On 3/5/05 12:45 PM, in article
> 3AD07FB0-A316-4ED0-A29D-EB3E5F06499A@.microsoft.com, "Coffee guy"
> <Coffeeguy@.discussions.microsoft.com> wrote:
>
>

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...