Creating trigger at runtime

Hi,
I am using VS 2005 and SQL server 2005.
I want to create a trigger at runtime. The trigger has to be created
when one of the rows in user-specified table is deleted. User will
specify the name of the table at runtime. Hence I cant create trigger
at development time. Can I create such a trigger from my C# code?
ManaMana wrote:
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
Create triggers when your tables are created. Do you really create
tables at runtime? This sounds like a strange requirement and perhaps
not an optimal design.
Use the CREATE TRIGGER statement to create a trigger. Bear in mind that
you'll have to give ddl admin rights to your users, which is not
usually recommended. Maybe if you explain a bit more about why you want
to do this then someone can suggest a better alternative.
David Portas
SQL Server MVP
--|||Mana
Can I ask you , why an user should specify a table name when he/she deletes
a row?
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136290048.321123.94070@.o13g2000cwo.googlegroups.com...
> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Triggers can be created in C# code if you have the proper permissions... But
I would warn against doing this... IF there is something you wish to do
permanently, just add the trigger and leave it there... Otherwise put the
code directly in the C# program, instead of creating a trigger...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mana" wrote:

> Hi,
> I am using VS 2005 and SQL server 2005.
> I want to create a trigger at runtime. The trigger has to be created
> when one of the rows in user-specified table is deleted. User will
> specify the name of the table at runtime. Hence I cant create trigger
> at development time. Can I create such a trigger from my C# code?
> Mana
>|||Hi Uri n David,
Let me explain my requirement elaborately.
I am building a web application which allows subscribers to subscribe
for the event of thier interest and receive notification when the event
occurs.
The event can be inserting or deleting a record from one of the tables
in database. (This is equirement of client. Cant help)The names of the
tables are exposed to user. And the user decides at runtime - first,
the name of the table and second, whether he should be notified on
insertion of record or deletion.
Since I dont not know table name and action (insert / delete) in
advance I cant write trigger in advance. I need to do it runtime.
I dont want to create triggers for all the tables existing in database.
Only for those that user is interested in.
Hope this explains my problem.
Mana|||Mana
I'd create one general table for auditing. When inserting/deletion is
occured so insert a row into this table which may have for instance a name
of the table , type of the operation and etc
"Mana" <DearManasi@.gmail.com> wrote in message
news:1136354013.575930.55610@.g14g2000cwa.googlegroups.com...
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
>|||Mana wrote:
> Hi Uri n David,
> Let me explain my requirement elaborately.
> I am building a web application which allows subscribers to subscribe
> for the event of thier interest and receive notification when the event
> occurs.
> The event can be inserting or deleting a record from one of the tables
> in database. (This is equirement of client. Cant help)The names of the
> tables are exposed to user. And the user decides at runtime - first,
> the name of the table and second, whether he should be notified on
> insertion of record or deletion.
> Since I dont not know table name and action (insert / delete) in
> advance I cant write trigger in advance. I need to do it runtime.
> I dont want to create triggers for all the tables existing in database.
> Only for those that user is interested in.
> Hope this explains my problem.
> Mana
Don't send notifications from a trigger. I explained why not in the
following thread:
http://groups.google.co.uk/group/mi...4dd2078d0df5312
Uri's suggestion is a better one: Log your changes to a table. Send out
notifications at suitable intervals based on that table. Have you
considered using SQL Server Notification Services? Both 2000 and 2005
versions are available. See:
http://www.microsoft.com/sql/techno...on/default.mspx
Hope this helps.
David Portas
SQL Server MVP
--|||Thanks, I think Uri's sugestion to keep an audit table is good.
Will implement that.
Thanks to all :)
Mana

Creating Test Data for a Table

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

u can use some existing table...transform and import that data...

or..there are a few data generator tools u can search and use them.... if u have visual studio 2005 for database developer, that has a cool data generation functionality too...

|||

Thanx, though I was looking for a T-SQL query equivalent solution

Maybe a while loop? I'm not so good at writing code in sql, any other suggestions please?

|||

JohDas wrote:

Hello, say I have a Table with 2 columns:

integer ID

varchar Description

How can I insert a large number of test rows inside this table for experimentation purposes?

Thanx in advance!

INSERT INTO
MyTable (id, Description)
(
SELECT
Id, Description
FROM
MyOtherTable
)|||

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

|||

ManiD wrote:

The following query may help you..

Create Table TestTable
(
Id int,
Description varchar(100)
)
go
SET NOCOUNT ON
Declare @.Count as int
Select @.Count = 10000
While @.Count >0
Begin
Insert Into TestTable values (@.Count, 'Description ' + Convert(Varchar,@.Count));
Select @.Count = @.Count - 1
End

You can change your Count init value as you want upto 2,147,483,647 ..

Nice, after seeing this I realised I mis understood the question |||Thanx a lot, this is the query I've been searching!!!|||

JohDas:

I try to avoid these WHILE loops as much as possible. I normally try to use a "numbers" table to generate my test data; this is normally much more efficient. One of the tools that I find helpful for generating repetitive data is the modulo "%" operator

-- --
-- Give a look to this link for a description of the use of a
-- "numbers" table:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
--
-- A table that can be used on a "test only" basis is the
-- "master.dbo.spt_values" table; but be forwarned: this type
-- of use is not supported! In fact, if you use this table with
-- SQL Server 2000 you will find that you get a different range
-- of values for the "number" column than you do with SQL Server
-- 2005.
--
-- Nonetheless, I still frequently use this table as a source of
-- values for test data. If the range of values from this table
-- is not sufficient to provide enough data, then I might
-- generate my data by cross joining the spt_values table with
-- itself.
-- --
select number,
'Description #' + convert (varchar(10), number%21) Description
from master.dbo.spt_values (nolock)
where name is null

-- Output:

-- number Description
-- -- --
-- 0 Description #0
-- 1 Description #1
-- 2 Description #2
-- ...
-- 20 Description #20
-- 21 Description #0
-- 22 Description #1
-- ...

|||

Thanx Mugambo, it's a more complex solution (to comprehend ;) ) but I think it'll be faster

Creating rows based on date range from another table

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
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|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
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

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
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 Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
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 Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

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

Creating Output in columns instead of Rows

Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).

Right now my output would look like this:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date

(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)

I have seen an example of PIVOT, but could not get that to work.

Any suggestions?

Thanks!select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling|||Thanks for the reply. Guess I should not have made my example so simple!

The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"

So the "field" Yr-Mnth is not a table field, but created through the select statement.

The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.

In this example, the output would have 13 columns of demand data, 1 listed for each month.

As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.

Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)

select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
order by
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId

(I don't have to use (+) in my table joins as there will always be a match)

Guess placing the data into columns instead of rows is not as simple as I had hoped!?!|||well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.

Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.

You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.

Let me know Monday if you have not figured it out, I am leaving the office.|||If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 ....
where ...
group by col1, col2

creating N xml documents from N rows

kudos to anyone who can answer this one;

I've got a table and I want to select rows in it so that the result contains an XML column. So going from

ID Parent Name Date

1 null Foo 2005-04-03

2 1 Bar 2005-04-03

to

ID Parent XML

1 null <element name="Foo" date="2005-04-03" />

2 1 <element name="Bar" date="2005-04-03" />

And for the life of me I can't find a method for creating the XML in an elegant way.

The 'FOR XML EXPLICIT' clause gives me all the formatting I could want, but forces you to munge all the XML into a single, xml-only result. That doesn't seem to be a help. Is there a clean way to do this?

If all else fails, though I shudder even to suggest it, is there a function to xml-escape strings so I can do something like

select

ID, Parent,

convert(xml, '<element name="' + quote(name) + '" date="' + quote(tostring(date)) + '" />")

from Elements

If you SQL Server 2005 you can use the following query,

Code Snippet

Create Table #data (

[ID] int ,

[Parent] Varchar(100) ,

[Name] Varchar(100) ,

[Date] datetime

);

Insert Into #data Values('1',NULL,'Foo','2005-04-03');

Insert Into #data Values('2','1','Bar','2005-04-03');

Select

Id

,Parent

,(Select [Name],[Date] from #Data element Where element.Id=Main.ID For XML AUTO) as XML

From

#Data Main

|||Wow -- thanks. That's absolutely perfect. Thanks so much.|||

this one also will work:

select
ID,
Parent,
(
select
quote(name) as "@.name",
quote(tostring(date)) as "@.daae"
for xml path('element'), type
) xml
from Elements

Creating multiple rows from a field with a list

OK. I'm being sent some data with a varchar field called my_elements that contains a 'list' of data items e.g. "4950,1,0%4954,2,0%4955,1,1"
I want to separate this into three records with a my_element field:
"4950,1,0"
"4954,2,0"
"4955,1,1"
How can I do this in SQL? Here's a template for what I want:

create table my_table
(
my_id int,
my_elements varchar(8000)
)
insert into my_table values (1,'4950,1,0%4954,2,0%4955,1,1')

-- Now I need some SQL to produce to create rows like these:
-- 1,'4950,1,0'
-- 1,'4954,2,0'
-- 1,'4955,1,1'
--
-- Or even better, as 4 numeric fields per row:
-- 1,4950,1,0
-- 1,4954,2,0
-- 1,4955,1,1

Also, I'd like to aviod using cursors if I can.

Any help appreciated. Thanks!do you have a fixed or variable number of data items?|||OK So this example Sucks But Hey

It Works for your example

SELECT my_id,SUBSTRING(my_elements,0,9) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,10,8) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,19,9) FROM my_table

tehe

GW|||Originally posted by Paul Young
do you have a fixed or variable number of data items?

Yeah, sorry, I forgot to point out, a variable number of data items|||mmmm

wonder if we could work out the entire length of the field ie. total number of seperate records

Then

Iterate through a loop using local variables to build a dynamic SQL Statement incrementing the substring position as we go and adding the UNIONS then execute that.

It's a thought

Is the physical length of the Data Items Consistent ?|||or:

if object_id('TEMPDB..#my_table') is not null drop table #my_table

create table #my_table (
my_id int
, my_elements varchar(8000))

insert into #my_table values (1,'4950,1,0%4954,2,0%4955,1,1')
insert into #my_table values (2,'4850,1,0%4854,2,0%4855,1,1')
insert into #my_table values (3,'4750,1,0%4754,2,0%4755,1,1')
insert into #my_table values (4,'4650,1,0%4654,2,0%4655,1,1')
insert into #my_table values (5,'4550,1,0%4554,2,0%4555,1,1')

declare @.Tbl table(my_id int, my_element1 int, my_element2 int, my_element3 int)
declare @.my_id int, @.my_elements varchar(8000)
, @.RecordSeperator char(1), @.ItemSeperator char(1)
, @.my_element varchar(12)
, @.RecordPosition int, @.ItemPosition int, @.LastRecordPosition int, @.LastItemPosition int
, @.Int1 int, @.Int2 int, @.Int3 int, @.Int4 int

select @.RecordSeperator = '%'
, @.ItemSeperator = ','

select @.my_id = min(my_id) from #my_table
while (@.my_id is not null) begin
select @.my_elements = my_elements from #my_table where my_id = @.my_id

set @.LastRecordPosition = 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)
while (@.RecordPosition > 0) begin
set @.my_element = substring(@.my_elements,@.LastRecordPosition,@.Record Position-@.LastRecordPosition)

set @.LastItemPosition = 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int1 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int2 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int3 = cast(substring(@.my_element,@.LastItemPosition,len(@. my_element)) as int)

raiserror('ID: %d ''%s'' %d - %d - %d.',0,1,@.my_id,@.my_element,@.Int1,@.Int2,@.Int3)

insert into @.Tbl values(@.my_id, @.Int1, @.Int2, @.Int3)

set @.LastRecordPosition = @.RecordPosition + 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)

end

select @.my_id = min(my_id) from #my_table where my_id > @.my_id
end

select * from @.Tbl|||Wow! Looks good! Thank you Paul.

Creating indexes on temp tables in stored procedures

Help,

I have a complex stored procedure (>1000 lines) that uses multiple temp tables with thousands of rows. These temp tables are joined together, and selected from many times.

I tried to improve the performance of the procedure by createing the recommended indexes on my temp tables, but the query plan ignores the indexes and continues to use table scans.

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Developer Edition (Build 2600: Service Pack 2)

Can you provide some samples of the queries your issuing against these tables?
And maybe some examples of the tables...

|||

The temp tables are used in a cursor (trust me, there's no other way). So these tables of upwards of 200,000 rows each need to be joined, to pull out just a few rows at a time. The tables are fairly simple, around 10 columns each. My issue is that SQL Server doesn't pay attention to indexes created on temp tables because the execution plan is predetermined (I think) and it doesn't take into account an index that doesn't exist at compile-time.

I may have solved my issue by creating another stored procedure that I call from the first. this sub-stored procedure creates indexes on the temp tables (that's all it does) and it looks like the execution plan is no longer doing table scans to join two tables together... it appears to be using the indexes I've created. If you have an easier way to get SQL to use a just-created index, I'm all ears.

FYI: pet peeve of mine

your = something you own (posessive)

you're = conjunction form of "you are"

|||

First off, if you are going to punish people for grammer (yes, that is grammar then you are going to be sorely dissappointed. First off no spell check. Second, no pay. Third, well, come on it is just a bit of help.

Second: the biggest pet peeve of them all around here is not posting your code and DDL so we can look at what you are doing.

Third: "The temp tables are used in a cursor (trust me, there's no other way)." This is rarely true. Almost any cursor can be dealt with in set based code. Some order based accumulations are faster in cursors (or so I have heard , but I haven't written a cursor for a non-system function in years.

You could try adding a hint to your query to force index use. That might work. Try the WITH RECOMPILE hint on the proc too. If nothing else, you can try declaring the cursor in a dynamic SQL call:

declare @.cursorDeclare varchar(max)

set @.cursorDeclare = 'declare bob cursor global for select ''hi'' as hi

open bob'

exec (@.cursorDeclare)

fetch next from bob

That might do the trick. Or, if there are just a few rows to be returned, this might work to spool the dynamic query into a temp table:

create table #tempper

(

value varchar(10)

)

insert into #tempper

exec ('select ''value''')

select *

from #tempper

|||Here, here to the cursor advice; Cursors Are Loathesome.

Creating DDL (create statements) and data (insert satement)

Hi,
I have about 10.000 rows of data to be copied to another server in another
city, I want to create a DDL (create statements) and data (insert
statements) via EM but I only get the DDL. How to create the insert
statement from EM? There's such a feature? Or should I create it from app?
TIA,
Hendrickhttp://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
--|||http://vyaskn.tripod.com/code/generate_inserts.txt
will do it. However, for 10K rows it might be easier and more efficient
to use BCP. 10,000 individual INSERTs in a script could be a slow and
cumbersome process.
David Portas
SQL Server MVP
--|||Great, thanks.
We'll consider the BCP utililty, too.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131038250.883762.17220@.f14g2000cwb.googlegroups.com...
> http://vyaskn.tripod.com/code/generate_inserts.txt
> will do it. However, for 10K rows it might be easier and more efficient
> to use BCP. 10,000 individual INSERTs in a script could be a slow and
> cumbersome process.
> --
> David Portas
> SQL Server MVP
> --
>

Creating DDL (create statements) and data (insert satement)

Hi,
I have about 10.000 rows of data to be copied to another server in another
city, I want to create a DDL (create statements) and data (insert
statements) via EM but I only get the DDL. How to create the insert
statement from EM? There's such a feature? Or should I create it from app?
TIA,
Hendrick
http://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
|||http://vyaskn.tripod.com/code/generate_inserts.txt
will do it. However, for 10K rows it might be easier and more efficient
to use BCP. 10,000 individual INSERTs in a script could be a slow and
cumbersome process.
David Portas
SQL Server MVP
|||Great, thanks.
We'll consider the BCP utililty, too.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131038250.883762.17220@.f14g2000cwb.googlegro ups.com...
> http://vyaskn.tripod.com/code/generate_inserts.txt
> will do it. However, for 10K rows it might be easier and more efficient
> to use BCP. 10,000 individual INSERTs in a script could be a slow and
> cumbersome process.
> --
> David Portas
> SQL Server MVP
> --
>

Creating DDL (create statements) and data (insert satement)

Hi,
I have about 10.000 rows of data to be copied to another server in another
city, I want to create a DDL (create statements) and data (insert
statements) via EM but I only get the DDL. How to create the insert
statement from EM? There's such a feature? Or should I create it from app?
TIA,
Hendrickhttp://vyaskn.tripod.com/code/generate_inserts.txt
--
David Portas
SQL Server MVP
--|||http://vyaskn.tripod.com/code/generate_inserts.txt
will do it. However, for 10K rows it might be easier and more efficient
to use BCP. 10,000 individual INSERTs in a script could be a slow and
cumbersome process.
--
David Portas
SQL Server MVP
--|||Great, thanks.
We'll consider the BCP utililty, too.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131038250.883762.17220@.f14g2000cwb.googlegroups.com...
> http://vyaskn.tripod.com/code/generate_inserts.txt
> will do it. However, for 10K rows it might be easier and more efficient
> to use BCP. 10,000 individual INSERTs in a script could be a slow and
> cumbersome process.
> --
> David Portas
> SQL Server MVP
> --
>

creating data for a histogram.

I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have a table, TableA with amongst other fields, a field for Qty.
> Qty can range from 0 to 100.
> How do I count the number of rows with a qty between 1 and 10, 11 and
> 20, 21 and 30, and so on using one SQL statement?

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

--
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 seems to just count the number of times each qty appears, just
like

SELECT qty AS Expr1, COUNT(*) AS Expr2
FROM [Temp]
GROUP BY qty

How should I change it so that it counts the the number of qtys between
each range of 10?

Regards,
Ciarn|||Hi Erland Sommarskog ,

You Always give helpfull and informative answers.
I changed the query a bit to show the LowRange as well HiRange
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
but I am facing a problem can You guide me on this

This query {select q=qty+10 from sales order by q} works but
{select q=qty+10 from sales group by q} does not work .SQL Server2000
is not recognising Aliased Columns in second case .
---
With regards
Jatinder Singh (System Analyst )|||(chudson007@.hotmail.com) writes:
> That seems to just count the number of times each qty appears, just
> like
> SELECT qty AS Expr1, COUNT(*) AS Expr2
> FROM [Temp]
> GROUP BY qty
>
> How should I change it so that it counts the the number of qtys between
> each range of 10?

The query I posted was:

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

I would expect to give the desired result, assuming that qty is integer.
If qty is float or decimal, it will indeed just be a roundabout way to
count single qtys.

I will have to admit that I did not test my query, but there is standard
recommendation that posting asking for help with queries should include:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired output given the sample data.

This makes it very easy for me or anyone else who anser to cut and paste
into Query Analyzer and test whatever we post.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> You Always give helpfull and informative answers.
> I changed the query a bit to show the LowRange as well HiRange
> SELECT LowRange,HiRange,COUNT(*)
> FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
> ,HiRange=((qty - 1) / 10) * 10 + 10
> FROM sales) AS ds
> GROUP BY lowRange ,HiRange
> but I am facing a problem can You guide me on this
> This query {select q=qty+10 from sales order by q} works but
> {select q=qty+10 from sales group by q} does not work .SQL Server2000
> is not recognising Aliased Columns in second case .

Correct. I believe that Access does this, but that's not in alignment with
the SQL standards.

Instead, the technique to use is a derived table as a above.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
I am facing another problem of displaying a summarised data along with
the detail data
i.e

Item Qty

Item1 10
Item1 10
Item1 20
40 ( Sum for Item1)
and so on .........

I wish to have a single query which runs on all RDBMS . Is it possible
?

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

This Query works but it would work on MS SQLServer .

---------------
With regards
Jatinder Singh (System Analyst )|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am facing another problem of displaying a summarised data along with
> the detail data
> i.e
> Item Qty
> Item1 10
> Item1 10
> Item1 20
> 40 ( Sum for Item1)
> and so on .........
> I wish to have a single query which runs on all RDBMS . Is it possible
> ?
> USE pubs
> SELECT type, price, advance
> FROM titles
> ORDER BY type
> COMPUTE SUM(price), SUM(advance) BY type
> This Query works but it would work on MS SQLServer .

Here is a query which I believe should be fairly portable. (But since
I only work with SQL Server, I can make no warranties):

SELECT type, x = '', price, advance
FROM titles
UNION
SELECT type, 'Total', SUM(price), SUM(advance)
FROM titles
GROUP BY type
ORDER BY type, x

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> > I am facing another problem of displaying a summarised data along
with
> > the detail data
> > i.e
> > Item Qty
> > Item1 10
> > Item1 10
> > Item1 20
> > 40 ( Sum for Item1)
> > and so on .........
> > I wish to have a single query which runs on all RDBMS . Is it
possible
> > ?
> > USE pubs
> > SELECT type, price, advance
> > FROM titles
> > ORDER BY type
> > COMPUTE SUM(price), SUM(advance) BY type
> > This Query works but it would work on MS SQLServer .
> Here is a query which I believe should be fairly portable. (But since
> I only work with SQL Server, I can make no warranties):
> SELECT type, x = '', price, advance
> FROM titles
> UNION
> SELECT type, 'Total', SUM(price), SUM(advance)
> FROM titles
> GROUP BY type
> ORDER BY type, x
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Hi Erland,
Thanks ,I think it will work on any RDBMS . Your analysis ablity is
really something. I cannot describe it in words.

Thanks Again
With warm regards
Jatinder Singh (System Analyst)|||Hi Erland,

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

With warm regards
Jatinder Singh (System Analyst)|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Can we similarly replace CUBE operator in SQL Server by using simple
> queries that will run on any RDBMS?

I don't use CUBE very often, so I may miss some fine detail. But the
two queries below returns the same result:

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id WITH CUBE
ORDER BY type, pub_id

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id
UNION
SELECT type, NULL, SUM(price), SUM(advance)
FROM titles
GROUP BY type
UNION
SELECT NULL, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY pub_id
UNION
SELECT NULL, NULL, SUM(price), SUM(advance)
FROM titles
ORDER BY type, pub_id

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland
I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds

In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds

Query 1:-
select RowId,PolicyNumber,EndoNumber,status,SF,case when SubQ is Null
then 'No' else 'Yes' end as Lock,RCount from
(
SELECT RowId,PolicyNumber,EndoNumber,status,SF,
(select status from InProcessData052005MstM WHERE status in ('pen') and
IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM IP
WHERE status in ('cur','pen')
) X
ORDER BY PolicyNumber

select getdate()

SELECT
RowId,PolicyNumber,EndoNumber,status,SF,dbo.fnTryG etPolicyCount(PolicyNumber,EndoNumber)
as RCount,
dbo.fnTryGetPolicyLock(PolicyNumber,EndoNumber) as Lock
--(select status from InProcessData052005MstM WHERE status in ('pen')
and IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
--(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM
WHERE status in ('cur','pen')
order by PolicyNumber
select getdate()

create function fnTryGetPolicyCount(@.p varchar(16),@.e varchar(3))
returns int
as
begin
return (select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and PolicyNumber=@.p and EndoNumber=@.e)
end

create function fnTryGetPolicyLock(@.p varchar(16),@.e varchar(3))
returns varchar(3)
as
begin
declare @.Lock varchar(3)
select @.Lock=status from InProcessData052005MstM WHERE status in
('pen') and PolicyNumber=@.p and EndoNumber=@.E
if @.Lock is null
set @.Lock='No'
else
set @.Lock='Yes'

return (@.Lock)
--(select status from InProcessData052005MstM WHERE status in ('pen')
and PolicyNumber=@.p and EndoNumber=@.E)
end

------------------
Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am bit confused by the output produced by the Query Analyzer and
> finding it bit difficult to decide which one of the following query is
> faster.
> In query one I am using Correlated subquery Approach and it consumes
> 78% of batch time when run with 2nd query but time of mere 20
> micrseconds
>
> In query two I am using functions (these functions berely takes
> PolicyNumber and Endrosment No to give output and does the same query )
> Approach and it consumes 22% of batch time when run with 1st query but
> time of 400 micrseconds

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- run query here
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'This query took ' + ltrim(str(@.tookms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFERS to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microseconds". I assume this is a typo for
"milliseconds".

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.

Waiting for your reply.

Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Thanks for your answer and time.
> Sorry for the typo error.
> I can be wrong in my assumption buut isn't it that the two queries are
> working in simliar fashion. Both are taking a value(or two) passing it
> to inner corelated query (funtion) and getting the result.

Just because two queries logically are the same, that does not mean that
performance is. There is quite some overhead with calls to saclar user-
defined functions. Also, when you stuff a subquery into a scalar function,
all the optimizer sees is a call, it does not see the contents of rhe
UDF, so it cannot take any shortcuts.

Table-valued functions are different. Particularly inline functions. Table-
valued inline functions are really just macros, and the query text is
pasted into the query, so the optimizer can rearrange as it likes.

As for the estimates you saw in Query Analyzer, they are just estimates, and
I would not pay too much attention on them.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks Again for your time .

Explaination is good. So may I consider that the UDF will always be
little slower because the Query Optimizer can never arrange it for
optimization. but using function make query more manageable

Please correct me if my assumption is wrong.

I have yet another question (query ) .
I have two tables
One empmast which store emp current designation
Other promotion table which store the promotions of an employee during
his service.It stores the information of employee designation promotion
date.

Empmast(empid int primary key,desigid int references desigmast
,.........)
PromotionDtls(empid int references Empmast,promotatedTo int references
desigmast, promotedFrom int references Desigmast,DateOfPromotion
smalldatetime)

EmpMast
empid desigid (current designation of employee)
1 3 ........................
2 1 ..................

PromotionDtls
empid promotedTo PromotedFrom effectiveDate
1 2 1 1-jan-2003
1 3 2 2-dec-2003
..........

Now I wish to use the designation Id in a query
such that if the employee data exists in Promotion Table the promotedTo
should be picked according to Effectivedate
otherwise the Empmast designation
e.g If I say desigId of employee having empid 1 on date 2-jun-2003 then
it should be desigId 2
I did this using isnull but I wish to find a better method.

select isnull( ( select top 1 promotedTo from promotionDtls where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
Jatinder Singh|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I have yet another question (query ) .

Sorry for not coming back to you earlier, but I had limited time for
some days to read the posts in the newsgroups, so I deferred the
difficult stuff until later.

A general advice is that it's better to post a new problem to a new
thread. Then other people might be more keen to answer it.

> I have two tables
> One empmast which store emp current designation
> Other promotion table which store the promotions of an employee during
> his service.It stores the information of employee designation promotion
> date.
> ...

I've now looked at the problem again, but I still could not really
understand what you are looking for. Since I don't like guessing, I
answer with the standard suggestion that you include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The first two makes it simple to copy and paste into Query Analyzer,
and the last makes it possible to actually produce a tested query, and
also helps to clarify what you are looking for.

It's not only that I'm lazy - neither do I like guessing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp