case statement TSQL

I'm trying to do something like the following:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
ORDER BY
lead_id
This causes an error at the >. How do a dynamic where clause like this?
Thanks for any help"gl" <gl@.discussions.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
>
Well, before showing you how to do this, I'm obliged to warn you against it.
Multiple different queries should be presented to SQL Server seperately so
the server can make intelligent choices about how to execute the query.
Such a compound query usually requires a full scan to execute. But if you
break it up, one or more of the resulting queries may be very cheap:
if @.lead_id_lookup = 0 then
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id >= @.lead_id
ORDER BY
lead_id
end
else if @.lead_id_lookup > 0
begin
SELECT *
FROM
leads WITH (NOLOCK)
WHERE
CASE lead_id = @.lead_id
ORDER BY
lead_id
end
Now, finally, to answer your question:
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
CASE WHEN @.lead_id_lookup = 0 AND lead_id >= @.lead_id THEN 1
WHEN @.lead_id_lookup > 0 AND lead_id = @.lead_id
THEN 1
ELSE 0 END = 1
ORDER BY
lead_id
David|||gl
untested
SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
lead_id = CASE WHEN @.lead_id_lookup = 0 THEN
lead_id >= @.lead_id
WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
END
ORDER BY
lead_id
"gl" <gl@.discussi
ons.microsoft.com> wrote in message
news:C9CE7143-7D55-4786-B059-1A18B1256360@.microsoft.com...
> I'm trying to do something like the following:
> SELECT
> *
> FROM
> leads WITH (NOLOCK)
> WHERE
> CASE WHEN @.lead_id_lookup = 0 THEN lead_id >= @.lead_id
> WHEN @.lead_id_lookup > 0 THEN lead_id = @.lead_id
> ORDER BY
> lead_id
> This causes an error at the >. How do a dynamic where clause like this?
> Thanks for any help|||SELECT
*
FROM
leads WITH (NOLOCK)
WHERE
(@.lead_id_lookup = 0 AND lead_id >= @.lead_id)
OR (@.lead_id_lookup > 0 AND lead_id = @.lead_id )
ORDER BY
lead_id

Cascade delete contraints - accessible through TSQL?

Hi all,

I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal.

Thanks for any info in advance!

I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?

You will have to drop and recreate the FK constraints to add the cascase option. Using DDLs is the only supported mechanism to achieve it.|||

Ok. is there a easy way to add and drop all contraints?

Capturing operations and tSQL executed under a user account

All,
I am looking for a way to capture all of the SQL code executed against a
database by a specific user.
Currently, a SQL 2005 database is being maintained by a third party that
connects remotely and does occasional "maintenance." The client would like
to bring this operation in house and is looking for a way to capture these
activities so that they can be documented.
The accuracy of this is essential so we are looking for a way to do a direct
capture.
Suggestions?
Ryan Hanisco
MCSE, MCTS: SQL 2005, Project+
Chicago, IL
Remember: Marking helpful answers helps everyone find the info they need
quickly.
On Jun 7, 4:06 pm, Ryan Hanisco
<RyanHani...@.discussions.microsoft.com> wrote:
> All,
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would like
> to bring this operation in house and is looking for a way to capture these
> activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a direct
> capture.
> Suggestions?
The easy way is to use Profiler. (That's what they called it in SQL
2000, it's undoubtedly there in SQL 2005 but possibly under a new
name.)
If you have to do it the hard way, look at the sp_trace stored
procedure.
|||Ryan Hanisco (RyanHanisco@.discussions.microsoft.com) writes:
> I am looking for a way to capture all of the SQL code executed against a
> database by a specific user.
> Currently, a SQL 2005 database is being maintained by a third party that
> connects remotely and does occasional "maintenance." The client would
> like to bring this operation in house and is looking for a way to
> capture these activities so that they can be documented.
> The accuracy of this is essential so we are looking for a way to do a
> direct capture.
This is precisely what SQL Profiler is good for. Or a server side trace,
if you want to reduce the load.
Although, this appears to me a somewhat funny thing to document procedures.
It sounds more like eavesdropping to me.n
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

capture error messages from dynamic tsql

How does one go about capturing error messages for a procedure that executes
dynamic sql...
for example ten lines out of 1000 produce an error... can the error be
captured and returned to the user?
--
Regards,
JamieI'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
USE tempdb
CREATE TABLE t(c1 int CHECK (c1 < 10))
GO
CREATE PROC p AS
EXEC('INSERT INTO t (c1) VALUES (20)')
GO
--Verify error
EXEC p
GO
--Capture using TRY and CATCH
BEGIN TRY
EXEC p
END TRY
BEGIN CATCH
DECLARE @.errStr nvarchar(4000)
SET @.errStr = ERROR_MESSAGE()
PRINT @.errStr
END CATCH
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> How does one go about capturing error messages for a procedure that executes
> dynamic sql...
> for example ten lines out of 1000 produce an error... can the error be
> captured and returned to the user?
> --
> Regards,
> Jamie|||I should have added we are still using SQL 2000. Tested this in 2005 and it
works great. Thanks.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
> USE tempdb
> CREATE TABLE t(c1 int CHECK (c1 < 10))
> GO
> CREATE PROC p AS
> EXEC('INSERT INTO t (c1) VALUES (20)')
> GO
> --Verify error
> EXEC p
> GO
> --Capture using TRY and CATCH
> BEGIN TRY
> EXEC p
> END TRY
> BEGIN CATCH
> DECLARE @.errStr nvarchar(4000)
> SET @.errStr = ERROR_MESSAGE()
> PRINT @.errStr
> END CATCH
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> > How does one go about capturing error messages for a procedure that executes
> > dynamic sql...
> > for example ten lines out of 1000 produce an error... can the error be
> > captured and returned to the user?
> > --
> > Regards,
> > Jamie
>
>

Capture CPU Utilization in TSQL

Happy New Year everyone!

I would like to capture CPU Utilization % using TSQL. I know this can
be done using PerfMon but I would like to run TSQL command (maybe once
every 5 minutes) and see what is the CPU Utilization at that instant so
that I can insert the value in a table and run reports based on the
data.

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Any help would be appreciated.

ThanksSQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

--
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|||Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?

DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint

SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS

WAITFOR DELAY '0:00:01'

SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS

SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff

Thanks for your time and help!

Vishal

Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

>
Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.
>
But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.
>
>
>
--
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

|||Thanks everyone for reading this and your responses. I was able to find
the correct solution in another Google post:

DECLARE
@.CPU_BUSY int
, @.IDLE int

SELECT
@.CPU_BUSY = @.@.CPU_BUSY
, @.IDLE = @.@.IDLE

WAITFOR DELAY '000:00:01'

SELECT
(@.@.CPU_BUSY - @.CPU_BUSY)/((@.@.IDLE - @.IDLE + @.@.CPU_BUSY - @.CPU_BUSY) *
1.00) *100 AS CPUBusyPct

This solution was posted by Gert-Jan Strik on Tues, Jan 14 2003 6:55
PM.
Here is the URL for the thread:
http://groups.google.com/group/comp...f67728586773e8b
Thanks again,

Vishal

SQLJunkie wrote:

Quote:

Originally Posted by

Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?
>
DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint
>
SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS
>
WAITFOR DELAY '0:00:01'
>
SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS
>
SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff
>
Thanks for your time and help!
>
>
Vishal
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

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