Conection Error

Whenever I try and connection to SQL Server, I keep
getting the following error: The procedure entry point
SetfilesecurityI could not be located in the dynamic link
library Msdart.dll. I have just installed MDAC 2.8 and re-
installed SQL Server, but I'm still encountering the same
problem. I am running SQL Server Personal edition SP3a on
Windows XP. Thanksmsdart.dll is an MDAC file. Try reinstalling MDAC, maybe something you
installed later wrote over that file.
Use the MDAC component checker (from microsoft.com/downloads) to see if you
have a file mismatch with msdart.dll.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I had (have) the same thing happening to me-2.8 hosed Delphi's ado
connection component. I always get this error when I try to create an
ado connection object.
I was able to fix it by manually replacing the files and changing
registry settings. MDAC will not rollback, the files are under
protection so I had to get a program to turn off Windows File
Protection, it was a huge hassle.
You need to get the 2.7 installer, extract the files, run the comparison
utility to identify what to change, get the utility to turn off file
protection, copy the files (and to dllcache), change the registry.
It worked for a day and now it's broken again. aargh!
the rule: DON'T UPGRADE TO MDAC 2.8.
I'm contemplating reformatting my hard disk and reinstalling
Win2K...WITHOUT MDAC 2.8.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
Did you ever figure out how to get past this error? I'm getting the
same error message, although the actual situation is somewhat different.
I just posted the following message at
http://forums.aspfree.com/t35176/shtml:
This error just started occuring today on an application I've been
working on for two years. The error message I receive is this:
The procedure entry point SetFileSecurityI could not be located in the
dynamic link library MSDART.dll.
The error occurs when I call:
System.Web.Mail.SmptMail.Send(message).
The error only occurs if I previously set the following property:
System.Web.Mail.SmtpMail.SmtpServer = "serverName"
I can't quite place my finger on the source of the problem. Here are two
potential causes of the problem:
A couple weeks ago, I had installed the 2005 Express verions of SQL
Server, c#, and Web Developer, but didn't notice any major problems
until now. Installing SQL Server 2005 caused a few minor issues with
Enterprise Manager on the Sql Server 2000 instance, but I was able to
solve those. I uinstalled those products, and repaired the installation
of VS2003 to no avail.
I almost ended up reformatting my hard drive over the weekend, after I
attempted to install a personal instance of Oracle 10g. After completing
the install, I was unable to reboot my computer. The system would boot
up, give about 150 warnings about delayed writes failing, and then crash
with STOP 0x00000027. The system was even hanging on reboot to safe
mode. It took me several hours to actually get my system to a bootable
state...After rebuilding the Master Boot Record, uninstalling oracle
from safe mode, about 25 reboots, a bios reset, yada, yada, yada...I
was back in.
I can't find much information on this error. One person here reported
the same problem when accessing SQL Server:
http://www.codecomments.com/sql/mes...C8d1e01c40513%2
4864b0f00%24a001280a%40phx.gbl %3E
They suggested it may have to do with installing MDAC 2.8, but I'm
pretty sure I've had that installed for a while. Anyways, the server is
running MDAC 2.8 without a problem, so I'm pretty sure it is something
else. Does anybody have any ideas?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Conditionaly hiding rows in a table in a single datagroup

Hello,
I have a report which takes data from a stored procedure and then reveals
some text on a row depending on whether or not a bit is true. The data coming
from teh stored procedure is a single row for a contact, and it displays a
number of rows in the report.
When we run the report into a PDF the data is all tidily on one page,
however in the web browser it is only showng 4 database rows per page (even
if there is only one additional line being displayed), this is because there
are so many rows (10), though the data isnt spaced out it is all at the top
of the report. Is there a way of getting more data to display in the report ?
Many Thanks
ChrisHi Chris,
I understood you would like to hide rows in a table, however I am not sure
in what condition you would like to hide the row? Would you please provide
us some detailed scenario examples?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Conditional Where wildcard problem

Hi,

I have a problem using the LIKE operator in a stored procedure. I have simplified the script so that it runs in query analyser and still have the same problem. The script is:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE CASE @.FirstName WHEN '' THEN PhoneList.FirstName ELSE @.FirstName END

This code produces no rows in the result. However if I change the second line to:
SELECT @.FirstName = 'Ben'
Then I get all of the rows with 'Ben' as the first name. If I change it to:
SELECT @.FirstName = 'Be%'
Then I get all of the rows with three character first names beginning with 'Be'. If I change it to:
SELECT @.FirstName = 'B%%'
Then I get all of the three character first names beginning with 'B'.

I need the conditional where so that if an empty string is passed it returns every row, which works fine as it is.

The % wildcard appears to be operating the same way as the _ wildcard. Has anyone seen this before?

This is SQL Server 2k SP3 on Win2003 server.

thanks
BenHi,

maybe you could try this:

DECLARE @.FirstName varchar (50)

SELECT @.FirstName = 'B%'

SELECT * FROM PhoneList
WHERE PhoneList.FirstName LIKE @.FirstName + '%'

If @.FIrstName is an empty string the statement should return all data.

;)

Conditional WHERE statement?

Hi all,

I have one for all the blackbelters out there: is there a way i can
make a stored procedure where i can control the where statement with
variables? I have to do some complex transformations to get compose a
fact table for MSAS and there a a lot of similarities between the
queries and a few differences because of different account methods
etc. (booking in starting date, booking stuff on order entry dates
etc) I want to put a combination of different rules in different
members of dimensions.

An example of what i mean:

CREATE STORED PROCEDURE dbo.FILLFACT (@.PAR1, @.PAR2)
AS
INSERT INTO FactTable (blah blah)

SELECT
IF @.PAR1 = 'OrderDate'
SourceView.Orderdate
ELSE
SourceView.StartDate
,
etc etc...

FROM
SourceView

WHERE
IF @.PAR2 = 'WholeTable'
1=1
IF @.PAR2 = 'Incomplete'
EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE
..., etc)

This way i could fill my fact table with

EXEC dbo.FillFact 'beginDate','Wholetable'
EXEC dbo.FillFact 'begindate', 'Rulebook1'
EXEC dbo.FillFact 'BeginDate', 'Exceptions'
etcetera.

This is not an actual SQL script i use, just an example of what i'm
talking about. Or maybe i could pass the where statement entirley as a
variable? But i can't use SET @.PAR1 = 'EndDate IS NULL' and then use
WHERE @.PAR1 can I?

I hope i'm making sense. Does anyone know if this is possible? Right
now i have a procedure that is composed of a dozen of sql scripts that
are mostly the same, but i have to copy it for every combination of
situations and then, of course, new stuff has to be added on 12
different places. Again and again.

Any thoughts?

TIA,

Gert-Jan van der Kamp[posted and mailed, please reply in news]

G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> I have one for all the blackbelters out there: is there a way i can
> make a stored procedure where i can control the where statement with
> variables? I have to do some complex transformations to get compose a
> fact table for MSAS and there a a lot of similarities between the
> queries and a few differences because of different account methods
> etc. (booking in starting date, booking stuff on order entry dates
> etc) I want to put a combination of different rules in different
> members of dimensions.

I believe that my article on dynamic search condition should give
you some ideas to work from. Look at
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's exectly what i mean, thanx!

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95F2F1FFAE1DEYazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> G.J. v.d. Kamp (gjvdkamp@.hotmail.com) writes:
> > I have one for all the blackbelters out there: is there a way i can
> > make a stored procedure where i can control the where statement with
> > variables? I have to do some complex transformations to get compose a
> > fact table for MSAS and there a a lot of similarities between the
> > queries and a few differences because of different account methods
> > etc. (booking in starting date, booking stuff on order entry dates
> > etc) I want to put a combination of different rules in different
> > members of dimensions.
> I believe that my article on dynamic search condition should give
> you some ideas to work from. Look at
> http://www.sommarskog.se/dyn-search.html.sqlsql

conditional where statement

I have a stored procedure that performs a search function with params:

@.username nvarchar(50)
@.country nvarchar(50)
and like 10 more.

A user may provide values for these params optionally.
So when the @.username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)
Currently my statement is:

select username,country from myUsers where
username=@.username andcountry=@.country

With this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...

What can I do to solve this?

Thanks!

SELECTFROM YourTableWHERE (@.usernameISNULL OR UserName = @.username )AND (@.countryISNULL OR Country = @.country )

|||

Thanks, but in this case the username field would not be ignored.
If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username, the statement would be:

SELECT
FROM YourTable
WHERE (@.usernameISNULL OR UserName = '' )

In this case the user with name "peter" would not be found. If a webvisitor does NOT provide a username, I want to return all rows regardless of the value in the username field...
Im just hoping I've explained myself clearly now :)
Thanks!

|||

use WHERE (UserName = COALESCE(@.UserName, UserName)) AND (Country = COALESCE(@.Country, Country)) AND etc.

COALESCE (or ISNULL if you prefer) will return the first value in the parameter list that is not null, so if you pass a NULL value for, say, @.UserName, that part of the WHERE clause will resolve to "WHERE UserName = UserName", which of course, is always true.

|||

Peter Smith:

If someone has filled in a username, say "peter", but the webvisitor would not want to search on any username

Can you explain what you mean by that? If there is a value in @.username, it will be searched against, else ignored. If there is a value provided and it does not exist in the table, obviously nothing will be returned. Incase the query doesnt work as expected, please provide some sample data, and sample scenarios and their expected outputs.

|||

Dinakar Nethi provided an excellent query for your issue. The key to implement is:

You need to set your input parameter to default NULL first.

@.UserName NVarchar(50) = NULL,

@.Country NVarchar(50) = NULL

|||

mmm, I see (now). I tested your query and it works :)
Thanks!

Conditional Sum/Runnining Total

I have a simple table in ssrs where data is returned from a stored procedure.

I have detail data group totals of the detail data.

I want to be able to create a sum of the detail data matching certain criteria.

i.e.

I have the following total field

sum(Fields!hours_m2.Value)

what I also want to be able to do is create a conditional formula like ...

sum(iif(Fields!Sort_Order.Value = "E1",Fields!hours_m2.Value,0))

When I create this on my report and preview it I get the following message in the field #Error.

Can someone please tell me where I've gone wrong and how to fix ... I know I can change the stored proc but I have 12 columns which I want to do the same thing with which would mean adding 12 columns to my stored proc.

Hello Derek,

In Visual Studio, click on the preview tab and run your report. Then look in the Output window (Ctrl+Alt+O), it should have a description as to what the error is. Can you post that error message?

Jarret

|||

One of the things I have found out about summing in ssrs is that it treats values of doubles and integers seperatley.

try changing the the "0" to "0.0" this will then tell ssrs it is a double type and your sum should work.

|||

description of error message

[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox101’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

|||

I have found that using the following

sum(iif(Fields!Sort_Order.Value = "E1" or Fields!Sort_Order.Value = "E2" or Fields!Sort_Order.Value = "F1",cdbl(Fields!hours_m2.Value),cdbl(0.0)))

works

Thanks for you responses

|||

The problem is as Mainiac said. Try this:

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

Hope this helps.

Jarret

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional Selection

Hi,
Hi,
I'm trying to construct a query (in a stored procedure) which will have a nu
mber of
selection criteria based on input parameters. There are a number of these p
arameters
whose selection conditions they represent which all have to be true for a ro
w to be
returned in the resultset.
The basic query is:
SELECT Store, StoreNumber
FROM Stores
WHERE ...
I'm trying to come up with the WHERE clause.
For example, I want to define a parameter named @.ExcludeSpecialties which if
it has
the value 1, means to return all stores but exclude stores whose StoreNumber
is in
the list (800, 802, 804). If the parameter has the value 0, then it means "
don't
care" and all StoreNumbers should be returned.
One could certainly argue that there probably should have been an column in
the
Stores row to indicate the store is a specialty store, rather than using a h
ard-wired
list of numbers. But the current data schema cannot be easily changed. Bes
ides, the
list never changes.
Indeed, there is a Franchise bit column in the row which is selected by anot
her
parameter called @.ExcludeFranchise whose WHERE predicate could be written as
:
WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
and if all the parameters were like this, I wouldn't be posting. Sadly, for
the
Specialties test I'm stuck with a NOT IN list.
This is easy enough to do in an IF/ELSE block, but there are several such si
milar
parameters whose values may be specified in any combination. This, I think,
makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possib
le
combinations would grow very quickly.
I'm hoping there is a simple solution to this NOT IN list, and it's just tha
t I can't
see it.
Can anyone help?
Thanks,
-- JeffTry this first ( Several popular approaches are details here ):
http://www.sommarskog.se/dyn-search.html
Anith|||try this in your where clause. Let me know if this helps
((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))|||You could store the specialties flag in a seperate table, with StoreNumber
as the key, then query against it instead of using the hardcoded list. This
way, when a new specialty store opens, or one of the existing stores
changes, you will just insert a row into the table and not have to touch the
code. It would be better to have it in the original table, but if you can't
change the original, maybe adding a new table is an option...
create table SpecialtyStores
(StoreNumber integer, Specialty bit) -- add PK and FK info here
SELECT Store, StoreNumber
FROM Stores
left outer join SpecialtyStores as spec
on stores.StoreNumber = spec.StoreNumber
WHERE
Specialty = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE 1 END
"Jeff Mason" <je.mason@.comcast.net> wrote in message
news:vvl1525lk0m8baqqpv5vs5q1g36a9c6jpa@.
4ax.com...
> Hi,
> Hi,
> I'm trying to construct a query (in a stored procedure) which will have a
number of
> selection criteria based on input parameters. There are a number of these
parameters
> whose selection conditions they represent which all have to be true for a
row to be
> returned in the resultset.
> The basic query is:
> SELECT Store, StoreNumber
> FROM Stores
> WHERE ...
> I'm trying to come up with the WHERE clause.
> For example, I want to define a parameter named @.ExcludeSpecialties which
if it has
> the value 1, means to return all stores but exclude stores whose
StoreNumber is in
> the list (800, 802, 804). If the parameter has the value 0, then it means
"don't
> care" and all StoreNumbers should be returned.
> One could certainly argue that there probably should have been an column
in the
> Stores row to indicate the store is a specialty store, rather than using a
hard-wired
> list of numbers. But the current data schema cannot be easily changed.
Besides, the
> list never changes.
> Indeed, there is a Franchise bit column in the row which is selected by
another
> parameter called @.ExcludeFranchise whose WHERE predicate could be written
as:
> WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise
END
> and if all the parameters were like this, I wouldn't be posting. Sadly,
for the
> Specialties test I'm stuck with a NOT IN list.
> This is easy enough to do in an IF/ELSE block, but there are several such
similar
> parameters whose values may be specified in any combination. This, I
think, makes
> IF/ELSE impractical as the number of IF/ELSE statements to handle all
possible
> combinations would grow very quickly.
> I'm hoping there is a simple solution to this NOT IN list, and it's just
that I can't
> see it.
> Can anyone help?
> Thanks,
> -- Jeff|||On Thu, 27 Apr 2006 08:13:02 -0700, Omnibuzz <Omnibuzz@.discussions.microsoft
.com>
wrote:

>try this in your where clause. Let me know if this helps
>((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))
Duh.
That did it. I knew it was something simple. I was having a Brain Fog, I gu
ess.
Thank you.
-- Jeff

Conditional Query

Hi,

I'm trying to construct a query (in a stored procedure) which will have a number of
selection criteria based on input parameters. There are a number of these parameters
whose selection conditions they represent which all have to be true for a row to be
returned in the resultset.

The basic query is:

SELECT Store, StoreNumber
FROM Stores
WHERE ...

I'm trying to come up with the WHERE clause.

For example, I want to define a parameter named @.ExcludeSpecialties which if it has
the value 1, means to return all stores but exclude stores whose StoreNumber is in
the list (800, 802, 804). If the parameter has the value 0, then it means "don't
care" and all StoreNumbers should be returned.

One could certainly argue that there probably should have been an column in the
Stores row to indicate the store is a specialty store, rather than using a hard-wired
list of numbers. But the current data schema cannot be easily changed. Besides, the
list never changes.

Indeed, there is a Franchise bit column in the row which is selected by another
parameter called @.ExcludeFranchise whose WHERE predicate could be written as:

WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END

and if all the parameters were like this, I wouldn't be posting. Sadly, for the
Specialties test I'm stuck with a NOT IN list.

This is easy enough to do in an IF/ELSE block, but there are several such similar
parameters whose values may be specified in any combination. This, I think, makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possible
combinations would grow very quickly.

I'm hoping there is a simple solution to this NOT IN list, and it's just that I can't
see it.

Can anyone help?

Thanks,

-- JeffJeff Mason (je.mason@.comcast.net) writes:
> I'm trying to construct a query (in a stored procedure) which will have
> a number of selection criteria based on input parameters. There are a
> number of these parameters whose selection conditions they represent
> which all have to be true for a row to be returned in the resultset.

I have an article on by web site that discusses a couple of alternatives,
both with static and dynamic SQL:
http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 27 Apr 2006 11:10:01 -0400, Jeff Mason wrote:

(snip)
>For example, I want to define a parameter named @.ExcludeSpecialties which if it has
>the value 1, means to return all stores but exclude stores whose StoreNumber is in
>the list (800, 802, 804). If the parameter has the value 0, then it means "don't
>care" and all StoreNumbers should be returned.

Hi Jeff,

WHERE ( @.ExcludeSpecialties = 0 OR StoreNumber NOT IN (800, 802, 804) )

(snip)
>Indeed, there is a Franchise bit column in the row which is selected by another
>parameter called @.ExcludeFranchise whose WHERE predicate could be written as:
>WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
>and if all the parameters were like this, I wouldn't be posting. Sadly, for the
>Specialties test I'm stuck with a NOT IN list.

That is indeed a common method to write such queries.

Do read the article Erland posted a link to - it describes a bunch of
methods to achieve what you need, with all their strengths and
weaknesses. Good stuff!

--
Hugo Kornelis, SQL Server MVP

conditional query

Just wondering if someone could provide a brief example of how to do this. I
have a stored procedure and I need a condition where statement, for example
inputs are
@.name varchar(25)
@.color varchar(25)
if color is not 'none ' I want
select * from table1
where table1.name = @.name
and table1.color = @.color
if color is 'none' I want
select * from table1 where table1.name=@.name.
Thanks.
--
Paul G
Software engineer.One way
If @.color <> 'none'
select * from table1
where table1.name = @.name
Else
select * from table1
where table1.name = @.name
and table1.color = @.color
But there are lots of ways to to this type of processing. See
http://www.sommarskog.se/dyn-search.html
for a good discussion of ways to do this.
Tom
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:34367D41-4D61-4C01-ABC5-C5DD9DB969BD@.microsoft.com...
> Just wondering if someone could provide a brief example of how to do this.
> I
> have a stored procedure and I need a condition where statement, for
> example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||create proc myProc
@.name varchar(25),
@.color varchar(25)
as
if @.color != 'none'
select * from table1
where table1.name = @.name and table1.color = @.color
if @.color = 'none'
select * from table1
where table1.name=@.name
Linchi
"Paul" wrote:
> Just wondering if someone could provide a brief example of how to do this. I
> have a stored procedure and I need a condition where statement, for example
> inputs are
> @.name varchar(25)
> @.color varchar(25)
> if color is not 'none ' I want
> select * from table1
> where table1.name = @.name
> and table1.color = @.color
> if color is 'none' I want
> select * from table1 where table1.name=@.name.
> Thanks.
> --
> Paul G
> Software engineer.|||This does it all in one query.
SELECT *
FROM table1
WHERE table1.name = @.name
AND (@.color = 'none '
OR table1.color = @.color)
Note that it MIGHT not perform as well as the alternatives using two
individual queries.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Jan 2008 11:17:02 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>Just wondering if someone could provide a brief example of how to do this. I
>have a stored procedure and I need a condition where statement, for example
>inputs are
>@.name varchar(25)
>@.color varchar(25)
>if color is not 'none ' I want
>select * from table1
>where table1.name = @.name
> and table1.color = @.color
>if color is 'none' I want
>select * from table1 where table1.name=@.name.
>Thanks.|||thanks for the responses. I simplified the example as I actually have
several items in the select statement as well as several in the where clause
as well as joins. I may have to use two seperate queries but may try to do it
with a sing query using OR if possible.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> This does it all in one query.
> SELECT *
> FROM table1
> WHERE table1.name = @.name
> AND (@.color = 'none '
> OR table1.color = @.color)
> Note that it MIGHT not perform as well as the alternatives using two
> individual queries.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 29 Jan 2008 11:17:02 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Just wondering if someone could provide a brief example of how to do this. I
> >have a stored procedure and I need a condition where statement, for example
> >
> >inputs are
> >@.name varchar(25)
> >@.color varchar(25)
> >
> >if color is not 'none ' I want
> >select * from table1
> >where table1.name = @.name
> > and table1.color = @.color
> >
> >if color is 'none' I want
> >select * from table1 where table1.name=@.name.
> >Thanks.
>|||On Tue, 29 Jan 2008 12:34:23 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>thanks for the responses. I simplified the example as I actually have
>several items in the select statement as well as several in the where clause
>as well as joins. I may have to use two seperate queries but may try to do it
>with a sing query using OR if possible.
Be aware that the warning about possible performance problems of the
all-in-one version becomes more apt as the query becomes more complex.
You may not have any problem, only trying it will determine that, but
be aware of the possibility.
Roy Harvey
Beacon Falls, CT|||ok thanks for the additional information. I have it running in the live
database, using 3 separate queries based on the condition of two input
parameters and the longest query is about 2 seconds. Fortunately the
database is relatively small and some indexes have been put into place to
enhance performance.
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> On Tue, 29 Jan 2008 12:34:23 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >thanks for the responses. I simplified the example as I actually have
> >several items in the select statement as well as several in the where clause
> >as well as joins. I may have to use two seperate queries but may try to do it
> >with a sing query using OR if possible.
> Be aware that the warning about possible performance problems of the
> all-in-one version becomes more apt as the query becomes more complex.
> You may not have any problem, only trying it will determine that, but
> be aware of the possibility.
> Roy Harvey
> Beacon Falls, CT
>

Conditional Order By Stored Procedure

I need to create a conditional if or case statement in SQL Server 2000
for a stored procedure. Basically if the value passed in is 1,2 or 3 then
it will order by either NEWID(), a text field or a datetime feild.
Not done much dynamic sql so any help would be appreciated.
Fuzzy
The approach I typically take is this:

SELECT
someColumns
FROM
someTable
WHERE
CASE WHEN @.sortValue = 1 THEN NEWID() END,
CASE WHEN @.sortValue = 2 THEN someTextColumn END,
CASE WHEN @.sortValue = 3 THEN someDateTimeColumn END,
defaultSortColumn -- just in case the @.sortValue is not 1, 2, or 3, Iknow the results will be sorted by *something*

|||My full stored procedure is listed below but i assume i have to specify the order
by clause somewhere it keeps returning a incorrect syntax near CASE error message
The sproc
CREATE PROCEDURE [dbo].[sp_call_accomSearch]
(
@.accomType As Int,
@.sgleroom As Int,
@.dbleroom As Int,
@.twinroom As Int,
@.tripleroom As Int,
@.Garage As Int,
@.Phone As Int,
@.Altitude As Int,
@.CarPark As Int,
@.Tv As Int,
@.TownCentre As Int,
@.SwimPool As Int,
@.Radio As Int,
@.NearSlopes As Int,
@.DgsAdmit As Int,
@.Safe As Int,
@.CrossCtry As Int,
@.SuitDisable As Int,
@.Balcony As Int,
@.OnTLake As Int,
@.Solarium As Int,
@.Suite As Int,
@.QutZone As Int,
@.BeautyCb As Int,
@.Minibar As Int,
@.Tennis As Int,
@.WhirlPl As Int,
@.Elevator As Int,
@.Sauna As Int,
@.PriceRgLow As Int,
@.PriceRgHigh As Int,
@.DateFromTotal As DateTime,
@.DateToTotal As DateTime,
@.selfcatering As Int,
@.halfboard As Int,
@.fullboard As Int,
@.roomphone As Int,
@.Suitdisableroom As Int,
@.CountryID As Int,
@.OrderBy As Int
)
AS
SELECT
tblaccommodation.accommodationID,
tblaccommodation.[name],
tblaccommodation.address1,
tblaccommodation.address2,
tblaccommodation.town,
tblaccommodation.postcode,
tblaccommodation.country,
tblaccommodation.email,
tblaccommodation.contact,
tblaccommodation.editorial,
(SELECT [name] FROM tblresort WHERE resortID = resortname) As ResortName,
(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As SgleRoomTotal,

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As dbleRoomTotal,
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As twinRoomTotal,
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) As tripleRoomTotal
FROM
tblaccommodation
WHERE

tblaccommodation.accomType = case
when @.accomType = 1 then @.accomType
else tblaccommodation.accomType
end AND

(SELECT SUM(sgleroom) As sgleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.Sgleroom AND

(SELECT SUM(dbleroom) As dbleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.dbleroom AND
(SELECT SUM(twinroom) As twinroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.twinroom AND
(SELECT SUM(tripleroom) As tripleroom
FROM tblrooms
WHERE
tblrooms.tv = case
when @.Tv = 1 then @.Tv
else tblrooms.tv
end AND
tblrooms.Radio = case
when @.Radio = 1 then @.Radio
else tblrooms.Radio
end AND
tblrooms.Balcony = case
when @.balcony = 1 then @.balcony
else tblrooms.balcony
end AND
tblrooms.ensuite = case
when @.Suite = 1 then @.Suite
else tblrooms.ensuite
end AND
tblrooms.Minibar = case
when @.Minibar = 1 then @.Minibar
else tblrooms.Minibar
end AND
tblrooms.Roomphone = case
when @.Roomphone = 1 then @.Roomphone
else tblrooms.Roomphone
end AND
tblrooms.Suitdisableroom = case
when @.Suitdisableroom = 1 then @.Suitdisableroom
else tblrooms.Suitdisableroom
end AND
tblrooms.accommodationid = tblaccommodation.accommodationid) >= @.tripleroom AND

tblaccommodation.Garage = case
when @.Garage <> 0 then @.Garage
else tblaccommodation.Garage
end AND
tblaccommodation.Phone = case
when @.Phone <> 0 then @.Phone
else tblaccommodation.Phone
end AND
tblaccommodation.Altitude = case
when @.Altitude <> 0 then @.Altitude
else tblaccommodation.Altitude
end AND
tblaccommodation.Carpark = case
when @.Carpark <> 0 then @.Carpark
else tblaccommodation.Carpark
end AND

tblaccommodation.TownCentre = case
when @.TownCentre <> 0 then @.TownCentre
else tblaccommodation.TownCentre
end AND
tblaccommodation.SwimPool = case
when @.SwimPool <> 0 then @.SwimPool
else tblaccommodation.SwimPool
end AND
tblaccommodation.NearSlopes = case
when @.NearSlopes <> 0 then @.NearSlopes
else tblaccommodation.NearSlopes
end AND
tblaccommodation.DgsAdmit = case
when @.DgsAdmit <> 0 then @.DgsAdmit
else tblaccommodation.DgsAdmit
end AND
tblaccommodation.Safe = case
when @.Safe <> 0 then @.Safe
else tblaccommodation.Safe
end AND
tblaccommodation.CrossCtry = case
when @.CrossCtry <> 0 then @.CrossCtry
else tblaccommodation.CrossCtry
end AND
tblaccommodation.SuitDisable = case
when @.SuitDisable <> 0 then @.SuitDisable
else tblaccommodation.SuitDisable
end AND
tblaccommodation.OnTLake = case
when @.OnTLake <> 0 then @.OnTLake
else tblaccommodation.OnTLake
end AND
tblaccommodation.Solarium = case
when @.Solarium <> 0 then @.Solarium
else tblaccommodation.Solarium
end AND
tblaccommodation.QutZone = case
when @.QutZone <> 0 then @.QutZone
else tblaccommodation.QutZone
end AND
tblaccommodation.BeautyCb = case
when @.BeautyCb <> 0 then @.BeautyCb
else tblaccommodation.BeautyCb
end AND
tblaccommodation.Tennis = case
when @.Tennis <> 0 then @.Tennis
else tblaccommodation.Tennis
end AND
tblaccommodation.Whirlpl = case
when @.Whirlpl <> 0 then @.Whirlpl
else tblaccommodation.Whirlpl
end AND
tblaccommodation.Elevator = case
when @.Elevator <> 0 then @.Elevator
else tblaccommodation.Elevator
end AND
tblaccommodation.Sauna = case
when @.Sauna <> 0 then @.Sauna
else tblaccommodation.Sauna
end AND

tblaccommodation.PriceRgLow >= @.PriceRgLow AND
tblaccommodation.PriceRgLow <= @.PriceRgHigh AND
tblaccommodation.PriceRgHigh <= @.PriceRgHigh AND

tblaccommodation.FromDT >= @.DateFromTotal AND
tblaccommodation.ToDT <= @.DateToTotal AND
tblaccommodation.selfcatering = case
when @.selfcatering<> 0 then @.selfcatering
else tblaccommodation.selfcatering
end AND
tblaccommodation.halfboard = case
when @.halfboard<> 0 then @.halfboard
else tblaccommodation.halfboard
end AND
tblaccommodation.fullboard = case
when @.fullboard<> 0 then @.fullboard
else tblaccommodation.fullboard
end AND
tblaccommodation.Country = @.CountryID AND
tblaccommodation.displayAcc = '1'
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDT END,
GO
|||First, I made a mistake in my example code. I mistakenly used aWHERE clause instead of an ORDER BY clause. Sorry to be confusing:-(
You are missing the ORDER BY, and you had an unneeded comma at the end. Try:

tblaccommodation.displayAcc = '1'
ORDER BY
CASE WHEN @.OrderBy = 1 THEN NEWID() END,
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND

|||LOL ... i am confused most of the time anyway usually why i am on here so much
I am still getting an sql error still when i try to check the syntax of the query in question any
thoughts what is going wrong here not found anything on google groups like it yet. I have
listed the error below.
Fuzzy

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as
part of the expression identfying a column position. Variables are only allowed when ordering
by an expression referencing a column name
|||Hmmm, it's not liking the NEWID(). I suggest this as aworkaround, but it will force NEWID() to be generated for each row inthe resultset, which *might* be a performance hit if you have a lot ofrows:
ORDER BY
CASE WHEN @.OrderBy = 2 THEN name END,
CASE WHEN @.OrderBy = 3 THEN FromDTEND,
NEWID()
|||

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
While either version will work for you, I think this one might be a little cleaner so if someone else needs to update it, they can see exactly how it is supposed to use the new ID (if orderBy is 1).
Just my 1.50
Nick

|||

nick-w wrote:

You Can also do:

SELECT [all of your fields]
FROM (
SELECT [all of your fields], newID() as ID
FROM ...
) X
ORDER BY
CASE WHEN @.orderBy = 1 THEN ID END,
CASE WHEN @.orderBy = 2 THEN name END,
CASE WHEN @.orderBy = 3 THEN FromDT END
Whileeither version will work for you, I think this one might be a littlecleaner so if someone else needs to update it, they can see exactly howit is supposed to use the new ID (if orderBy is 1).

I had thought about that, but had become concerned that NEWID()would be calculated for every row in every table, not just for theresultset. That sent me on a fruitless hunt to find a referencefor the processing sequence of all of the different portions of aSELECT statement so that I could confirm or deny that concern. When I couldn't find a reference (does anyone have one?) , I decidedon the approach I recommended. But, with that being said, I muchprefer the approach you've recommended as it doesn't make assumptionsand as you said it is cleaner. :-)

conditional logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Conditional AND statement in stored procedure Select query?

This should be simple but I can't figure it out. So I have a select query an
d
I want to run a conditional AND if a value is not NULL. Here is the type of
statment I want to run.
-- Declared values not shown.
SELECT * FROM MyTABLE
WHERE city = @.city
AND department = @.department
IF @.statusCode is NOT NULL
AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
END
-- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
and splits them out for the IN clause. It works fine if a string is actually
passed but I simply want to execute the IN clause if the @.statusCode value
passed to the procedure is not NULL.
I looked at the WHEN THEN clause but that only seems to work for equalities
AND status = WHEN @.statusCode IS NULL THEN ...
Thanks folks.SQL will short-circuit when the first part of an OR condition fails, so
this should work (performance may suffer though)
...
and (@.statusCode is null
or status in (select item from dbo.tsqlSplit(@.statusCode))
another option is to default the @.statusCode variable to the list of
possible status codes. then you won't have to check if @.statusCode is null.
Ramez wrote:
> This should be simple but I can't figure it out. So I have a select query
and
> I want to run a conditional AND if a value is not NULL. Here is the type o
f
> statment I want to run.
> -- Declared values not shown.
> SELECT * FROM MyTABLE
> WHERE city = @.city
> AND department = @.department
> IF @.statusCode is NOT NULL
> AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
> END
> -- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
> and splits them out for the IN clause. It works fine if a string is actual
ly
> passed but I simply want to execute the IN clause if the @.statusCode value
> passed to the procedure is not NULL.
> I looked at the WHEN THEN clause but that only seems to work for equalitie
s
> AND status = WHEN @.statusCode IS NULL THEN ...
> Thanks folks.

condition in script

hi

I need to alter a procedure depend on some information .

if A is true then

alter procedure .... < code 1>

else

alter procedure .... <code 2>

is it possible?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1557658&SiteID=1

Check out my post in there.

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
Todd
Can you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd
|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of th
e
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted r
ow
> to an audit table. .Net is calling the same stored procedure to update th
e
> same row in the base table 4 times. The stored procedure subtracts a pass
ed
> in value from a column in the base table. After the code runs, the value
in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of
the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger t
o
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],
[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[Tota
lAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
>