Can we handle ALL errors within a stored proceudre?

Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.

Can we handle all errors within a stored proceudre.

Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:

> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:

> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:

> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

Can we boost up the priority of a procedure

Hi,
I have a stored procedure which works fine under normal load.
But under heavy load on SQL Server, my procedure is taking very long time to
execute.
Is there any way to increase the priority of my procedure so that it get pro
per time slot.
My scenario is something is like this:
My procedure PMain is continously reading a table T1 processing rows one by
one and delete all rows of the table which are processed.
There are set of procedures which are continously inserting rows in table T1
which will be read by procedure PMain.
Procedure PMain is able to process with almost same speed as rows are added
to table T1.
But situation becomes worse when my another application fires a select query
on 20 tables and each table containing 30000 rows. This application takes r
ound about 1 hour to complete and during this time performance of my procedu
re goes down badly which I don't want.
Please suggest me some ways so that I can improve performance of this proced
ure.
Thanks in advance.
PushkarPushkar (pushkartiwari@.gmail.com) writes:
> I have a stored procedure which works fine under normal load. But under
> heavy load on SQL Server, my procedure is taking very long time to
> execute. Is there any way to increase the priority of my procedure so
> that it get proper time slot.
> My scenario is something is like this:
> My procedure PMain is continously reading a table T1 processing rows
> one by one and delete all rows of the table which are processed. There
> are set of procedures which are continously inserting rows in table T1
> which will be read by procedure PMain.
> Procedure PMain is able to process with almost same speed as rows are
> added to table T1.
> But situation becomes worse when my another application fires a select
> query on 20 tables and each table containing 30000 rows. This
> application takes round about 1 hour to complete and during this time
> performance of my procedure goes down badly which I don't want.
No, there is no process priority in SQL Server.
It is not clear to me whether the other application access the table(s)
that PMain works with or some completely unrelated tables. In the former
case, there could be blocking issues, in the latter there is only a case
of competition of resources.
Unfortunately, without knowing what is is Pmain, I can't give any advice.
But it could be the case that Pmain is not optimally written - or does
not have the opitimal indexing to work with. As long as the server is
not loaded, the performance is good anyway.
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

Can this stored procedure be optimised?

Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:

> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.

Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO

Can this be done with an output parameter?

Hi I want to make a Function in my User class that adds new members into the db. What I want to do is add the users details in using a stored procedure and input parameters and then return a parameter indicating their userid value and set it to a variable.

My userid column in the my db is auto incrementing with a seed of 1 and a step value of 1.

How could I create an output parameter that would return their new user id and then how would i set it to an integer variable.

Thanks::How could I create an output parameter that would return their new user id and then how
::would i set it to an integer variable

do you mean from the front end or inside the stored proc ?

if you mean the stored proc:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...) select @.userid=@.@.IDENTITY
..

HTH|||Use the OUTPUT Parameter of the Stored Procedure. Refer to SQL Server BOL for further assitance ...|||Hi yeah I mean by using the output param in the sp.

How does the @.@.Idendity work then?

How would i set this to a varialble in my front end. Would I use

Dim UserID as Integer

Dim objParam as New SqlParameter("@.@.Idendity", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objParam.Value = UserID
objComm.Parameters.Add(objParam)

Thanks|||First, you should use SCOPE_IDENTITY(), not @.@.IDENTITY.

Next, use a SP like this:

create procedure <name> ( @.param1 nvarchar(25),@.param2 int, @.userid int OUTPUT)
as
insert into ( ....) values (...)
select @.userid=SCOPE_IDENTITY()

then

Dim objParam as New SqlParameter("@.UserID", SqlDbType.Int)
objParam.Direction = ParameterDirection.Output
objComm.Parameters.Add(objParam)

Then after the command is run, check objComm.Parameters("@.UserID") for the UserID|||Many thanks Douglas. We may have to start calling you superman on here lol as you come to everyones rescue.|||Thanks. I do have the glasses, though no cape, and no one wants to se me in tights<g>.

can this be done in SQL2000

Hi I need to create a stored procedure that can do the following but now sure
if it can be done with sql2000.
1. read in existing data from a table.
2. create the current Julian date.
3. make a comparison and add a sequence number onto this number if it is a
specific Julian date.
I think all of this could probably be done with 2005 since it allows using
C#,vb for TSQL.
Paul G
Software engineer.On Wed, 30 Aug 2006 08:18:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I need to create a stored procedure that can do the following but now sure
>if it can be done with sql2000.
>1. read in existing data from a table.
Stored procedures are very good at retrieving data from database
tables.
>2. create the current Julian date.
SQL Server has sufficient tools for date manipulation that calculating
Julian date from the current date (getdate()) should be no problem. It
does, however, require knowing which definition of Julian date is
intended.
>3. make a comparison and add a sequence number onto this number if it is a
>specific Julian date.
Comparison of what to what? By "this number" do you mean the Julian
data calculated in item 2? What specific Julian date? One passed as
a parameter to the stored procedure? One retrieved from the table in
item 1?
>I think all of this could probably be done with 2005 since it allows using
>C#,vb for TSQL.
I am sure it can be done in 2005. It is unclear from the information
give that it will require C# or VB, but if it turns out to be
complicated they are available. Perhaps if you provided a bit more
detail someone will be able to suggest an appropriate approach.
>Paul G
>Software engineer.
Roy Harvey
Beacon Falls, CT|||Hi thanks for the response. Here are more details on what I am trying to do.
There is a table (table1) that has hundreds of records that look like
JTB-ABC-MDTC-06200-0001
JCV-BCD-ABAM-06201-0001
JTB-ABC-MDAC-06200-0002
I need the stored procedure to take the value
JCV-BCD-RBSV
and build the rest of it based on the following conditions and then save it
to a table.
Get the current julian date, today would be 06242, two hundred forty two day
of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
and only the last section still to be built. This is done by doing the
following.
1. Compare the jul date for the day the stored procedure will run (06242)
and find all records in table 1 with the same date (call this subset a).
2. Next out of subset a find all that match the first 6 letters (create
subset b).
3. Next out of subset b find the greatest value of the last 4 numbers (say
it was 0002).
4. Finally increment this value by 1 and use it to finish the newly created
value, so we would have JVC-BCD-RBSV-06242-0003.
This is easy to do in vb or C#.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> On Wed, 30 Aug 2006 08:18:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I need to create a stored procedure that can do the following but now sure
> >if it can be done with sql2000.
> >
> >1. read in existing data from a table.
> Stored procedures are very good at retrieving data from database
> tables.
> >2. create the current Julian date.
> SQL Server has sufficient tools for date manipulation that calculating
> Julian date from the current date (getdate()) should be no problem. It
> does, however, require knowing which definition of Julian date is
> intended.
> >3. make a comparison and add a sequence number onto this number if it is a
> >specific Julian date.
> Comparison of what to what? By "this number" do you mean the Julian
> data calculated in item 2? What specific Julian date? One passed as
> a parameter to the stored procedure? One retrieved from the table in
> item 1?
> >I think all of this could probably be done with 2005 since it allows using
> >C#,vb for TSQL.
> I am sure it can be done in 2005. It is unclear from the information
> give that it will require C# or VB, but if it turns out to be
> complicated they are available. Perhaps if you provided a bit more
> detail someone will be able to suggest an appropriate approach.
> >Paul G
> >Software engineer.
> Roy Harvey
> Beacon Falls, CT
>|||Of course all the parts of that complex column should be individual
table columns, but I will not belabor that point.
There is nothing about that which requires going to C# or VB. This
should give you some ideas. Note that I used a view, but each of the
pieces could have been substringed out when references. However since
all the real work is one in one SQL command, all those references
would make it a good bit more opaque. Another advantage to the view
is that it is possible to make it an indexed view, which could make a
major difference in performance.
CREATE TABLE Table1
(StrungOut char(23) NOT NULL)
INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
GO
CREATE VIEW Table1_V
AS
SELECT StrungOut,
First6 = SUBSTRING(StrungOut,1,6),
JDate = SUBSTRING(StrungOut,14,5),
JYear = SUBSTRING(StrungOut,14,2),
JDay = SUBSTRING(StrungOut,16,3),
Last4 = SUBSTRING(Strungout,20,4)
FROM Table1
GO
CREATE TABLE Table2
(StrungOut char(23) NOT NULL)
GO
CREATE PROC Demonstration
@.front char(12)
AS
INSERT Table2
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
FROM Table1_V
WHERE JDate = right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
GO
EXEC Demonstration 'JCV-BCD-RBSV'
SELECT *
FROM Table2
StrungOut
--
JCV-BCD-RBSV-06242-0006
Also, in the spec you mentioned matching on the first six characters,
but I wondered if it might have actually been the first seven.
One final point worth making. It would be quite practical to modify
this so that rather than taking in a string as a parameter, it
produced a new row such as this for each Front6 in Table1 that matches
the current date.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 09:09:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi thanks for the response. Here are more details on what I am trying to do.
>There is a table (table1) that has hundreds of records that look like
>JTB-ABC-MDTC-06200-0001
>JCV-BCD-ABAM-06201-0001
>JTB-ABC-MDAC-06200-0002
>I need the stored procedure to take the value
>JCV-BCD-RBSV
>and build the rest of it based on the following conditions and then save it
>to a table.
>Get the current julian date, today would be 06242, two hundred forty two day
>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>and only the last section still to be built. This is done by doing the
>following.
>1. Compare the jul date for the day the stored procedure will run (06242)
>and find all records in table 1 with the same date (call this subset a).
>2. Next out of subset a find all that match the first 6 letters (create
>subset b).
>3. Next out of subset b find the greatest value of the last 4 numbers (say
>it was 0002).
>4. Finally increment this value by 1 and use it to finish the newly created
>value, so we would have JVC-BCD-RBSV-06242-0003.
>This is easy to do in vb or C#.|||An alternate coding of the WHERE clause:
WHERE JYear = right(convert(char(4),datepart(year,getdate())),2)
AND JDay = right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
Roy
On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
>Of course all the parts of that complex column should be individual
>table columns, but I will not belabor that point.
>There is nothing about that which requires going to C# or VB. This
>should give you some ideas. Note that I used a view, but each of the
>pieces could have been substringed out when references. However since
>all the real work is one in one SQL command, all those references
>would make it a good bit more opaque. Another advantage to the view
>is that it is possible to make it an indexed view, which could make a
>major difference in performance.
>CREATE TABLE Table1
>(StrungOut char(23) NOT NULL)
>INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>GO
>CREATE VIEW Table1_V
>AS
>SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
>GO
>CREATE TABLE Table2
>(StrungOut char(23) NOT NULL)
>GO
>CREATE PROC Demonstration
>@.front char(12)
>AS
>INSERT Table2
>SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
>GO
>EXEC Demonstration 'JCV-BCD-RBSV'
>SELECT *
> FROM Table2
>StrungOut
>--
>JCV-BCD-RBSV-06242-0006
>Also, in the spec you mentioned matching on the first six characters,
>but I wondered if it might have actually been the first seven.
>One final point worth making. It would be quite practical to modify
>this so that rather than taking in a string as a parameter, it
>produced a new row such as this for each Front6 in Table1 that matches
>the current date.
>Roy Harvey
>Beacon Falls, CT
>On Wed, 30 Aug 2006 09:09:02 -0700, Paul
><Paul@.discussions.microsoft.com> wrote:
>>Hi thanks for the response. Here are more details on what I am trying to do.
>>There is a table (table1) that has hundreds of records that look like
>>JTB-ABC-MDTC-06200-0001
>>JCV-BCD-ABAM-06201-0001
>>JTB-ABC-MDAC-06200-0002
>>I need the stored procedure to take the value
>>JCV-BCD-RBSV
>>and build the rest of it based on the following conditions and then save it
>>to a table.
>>Get the current julian date, today would be 06242, two hundred forty two day
>>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>>and only the last section still to be built. This is done by doing the
>>following.
>>1. Compare the jul date for the day the stored procedure will run (06242)
>>and find all records in table 1 with the same date (call this subset a).
>>2. Next out of subset a find all that match the first 6 letters (create
>>subset b).
>>3. Next out of subset b find the greatest value of the last 4 numbers (say
>>it was 0002).
>>4. Finally increment this value by 1 and use it to finish the newly created
>>value, so we would have JVC-BCD-RBSV-06242-0003.
>>This is easy to do in vb or C#.|||Hi thanks for the response. Yes the table was already setup and there was
not time to restructure. Will take a look at what you have. Was not aware
that you can do a lot with SQL.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> Of course all the parts of that complex column should be individual
> table columns, but I will not belabor that point.
> There is nothing about that which requires going to C# or VB. This
> should give you some ideas. Note that I used a view, but each of the
> pieces could have been substringed out when references. However since
> all the real work is one in one SQL command, all those references
> would make it a good bit more opaque. Another advantage to the view
> is that it is possible to make it an indexed view, which could make a
> major difference in performance.
> CREATE TABLE Table1
> (StrungOut char(23) NOT NULL)
> INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> GO
> CREATE VIEW Table1_V
> AS
> SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
> GO
> CREATE TABLE Table2
> (StrungOut char(23) NOT NULL)
> GO
> CREATE PROC Demonstration
> @.front char(12)
> AS
> INSERT Table2
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate => right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> GO
> EXEC Demonstration 'JCV-BCD-RBSV'
> SELECT *
> FROM Table2
> StrungOut
> --
> JCV-BCD-RBSV-06242-0006
> Also, in the spec you mentioned matching on the first six characters,
> but I wondered if it might have actually been the first seven.
> One final point worth making. It would be quite practical to modify
> this so that rather than taking in a string as a parameter, it
> produced a new row such as this for each Front6 in Table1 that matches
> the current date.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi thanks for the response. Here are more details on what I am trying to do.
> >There is a table (table1) that has hundreds of records that look like
> >JTB-ABC-MDTC-06200-0001
> >JCV-BCD-ABAM-06201-0001
> >JTB-ABC-MDAC-06200-0002
> >
> >I need the stored procedure to take the value
> >JCV-BCD-RBSV
> >and build the rest of it based on the following conditions and then save it
> >to a table.
> >Get the current julian date, today would be 06242, two hundred forty two day
> >of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >and only the last section still to be built. This is done by doing the
> >following.
> >1. Compare the jul date for the day the stored procedure will run (06242)
> >and find all records in table 1 with the same date (call this subset a).
> >2. Next out of subset a find all that match the first 6 letters (create
> >subset b).
> >3. Next out of subset b find the greatest value of the last 4 numbers (say
> >it was 0002).
> >4. Finally increment this value by 1 and use it to finish the newly created
> >value, so we would have JVC-BCD-RBSV-06242-0003.
> >This is easy to do in vb or C#.
>|||I see were you get the current julian date, JYear and JDay but I did not see
if combine these to get the 06242 for example for today. thanks.
Paul G
Software engineer.
"Roy Harvey" wrote:
> An alternate coding of the WHERE clause:
> WHERE JYear => right(convert(char(4),datepart(year,getdate())),2)
> AND JDay => right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> Roy
> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> wrote:
> >Of course all the parts of that complex column should be individual
> >table columns, but I will not belabor that point.
> >
> >There is nothing about that which requires going to C# or VB. This
> >should give you some ideas. Note that I used a view, but each of the
> >pieces could have been substringed out when references. However since
> >all the real work is one in one SQL command, all those references
> >would make it a good bit more opaque. Another advantage to the view
> >is that it is possible to make it an indexed view, which could make a
> >major difference in performance.
> >
> >CREATE TABLE Table1
> >(StrungOut char(23) NOT NULL)
> >
> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >
> >GO
> >CREATE VIEW Table1_V
> >AS
> >SELECT StrungOut,
> > First6 = SUBSTRING(StrungOut,1,6),
> > JDate = SUBSTRING(StrungOut,14,5),
> > JYear = SUBSTRING(StrungOut,14,2),
> > JDay = SUBSTRING(StrungOut,16,3),
> > Last4 = SUBSTRING(Strungout,20,4)
> > FROM Table1
> >GO
> >
> >CREATE TABLE Table2
> >(StrungOut char(23) NOT NULL)
> >GO
> >
> >CREATE PROC Demonstration
> >@.front char(12)
> >AS
> >
> >INSERT Table2
> >SELECT @.front + '-' +
> > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> > '-' +
> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> > FROM Table1_V
> > WHERE JDate => > right(convert(char(4),datepart(year,getdate())),2) +
> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> > AND First6 = SUBSTRING(@.front,1,6)
> >GO
> >
> >EXEC Demonstration 'JCV-BCD-RBSV'
> >
> >SELECT *
> > FROM Table2
> >
> >StrungOut
> >--
> >JCV-BCD-RBSV-06242-0006
> >
> >Also, in the spec you mentioned matching on the first six characters,
> >but I wondered if it might have actually been the first seven.
> >
> >One final point worth making. It would be quite practical to modify
> >this so that rather than taking in a string as a parameter, it
> >produced a new row such as this for each Front6 in Table1 that matches
> >the current date.
> >
> >Roy Harvey
> >Beacon Falls, CT
> >
> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> ><Paul@.discussions.microsoft.com> wrote:
> >
> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >>There is a table (table1) that has hundreds of records that look like
> >>JTB-ABC-MDTC-06200-0001
> >>JCV-BCD-ABAM-06201-0001
> >>JTB-ABC-MDAC-06200-0002
> >>
> >>I need the stored procedure to take the value
> >>JCV-BCD-RBSV
> >>and build the rest of it based on the following conditions and then save it
> >>to a table.
> >>Get the current julian date, today would be 06242, two hundred forty two day
> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >>and only the last section still to be built. This is done by doing the
> >>following.
> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >>and find all records in table 1 with the same date (call this subset a).
> >>2. Next out of subset a find all that match the first 6 letters (create
> >>subset b).
> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >>it was 0002).
> >>4. Finally increment this value by 1 and use it to finish the newly created
> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >>This is easy to do in vb or C#.
>|||JYear and JDay are the pieces of the Julian date in Table1, so they
are only used for the comparison.
In the assignment:
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getdate())),2) +
right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
the Julian date part of the output string is the second and third
lines. BUT, I just thought of an alternate way to code the
assignment. We went to all that trouble to make sure Table1_V.JDate
matched the current day, so instead of using the current day we could
just use Table1_V.JDate.
SELECT @.front + '-' +
JDate +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
However, I'm guessing that IF there is no matching row with the
current date you might actually want to create the new row with 0000
or 0001 as the final part of the string. In that case I would keep to
using the derivation from getdate() as it will be easier to code in
the row-not-found branch.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 10:32:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I see were you get the current julian date, JYear and JDay but I did not see
>if combine these to get the 06242 for example for today. thanks.
>Paul G
>Software engineer.
>
>"Roy Harvey" wrote:
>> An alternate coding of the WHERE clause:
>> WHERE JYear =>> right(convert(char(4),datepart(year,getdate())),2)
>> AND JDay =>> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> AND First6 = SUBSTRING(@.front,1,6)
>> Roy
>> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
>> wrote:
>> >Of course all the parts of that complex column should be individual
>> >table columns, but I will not belabor that point.
>> >
>> >There is nothing about that which requires going to C# or VB. This
>> >should give you some ideas. Note that I used a view, but each of the
>> >pieces could have been substringed out when references. However since
>> >all the real work is one in one SQL command, all those references
>> >would make it a good bit more opaque. Another advantage to the view
>> >is that it is possible to make it an indexed view, which could make a
>> >major difference in performance.
>> >
>> >CREATE TABLE Table1
>> >(StrungOut char(23) NOT NULL)
>> >
>> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>> >
>> >GO
>> >CREATE VIEW Table1_V
>> >AS
>> >SELECT StrungOut,
>> > First6 = SUBSTRING(StrungOut,1,6),
>> > JDate = SUBSTRING(StrungOut,14,5),
>> > JYear = SUBSTRING(StrungOut,14,2),
>> > JDay = SUBSTRING(StrungOut,16,3),
>> > Last4 = SUBSTRING(Strungout,20,4)
>> > FROM Table1
>> >GO
>> >
>> >CREATE TABLE Table2
>> >(StrungOut char(23) NOT NULL)
>> >GO
>> >
>> >CREATE PROC Demonstration
>> >@.front char(12)
>> >AS
>> >
>> >INSERT Table2
>> >SELECT @.front + '-' +
>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
>> > '-' +
>> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
>> > FROM Table1_V
>> > WHERE JDate =>> > right(convert(char(4),datepart(year,getdate())),2) +
>> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
>> > AND First6 = SUBSTRING(@.front,1,6)
>> >GO
>> >
>> >EXEC Demonstration 'JCV-BCD-RBSV'
>> >
>> >SELECT *
>> > FROM Table2
>> >
>> >StrungOut
>> >--
>> >JCV-BCD-RBSV-06242-0006
>> >
>> >Also, in the spec you mentioned matching on the first six characters,
>> >but I wondered if it might have actually been the first seven.
>> >
>> >One final point worth making. It would be quite practical to modify
>> >this so that rather than taking in a string as a parameter, it
>> >produced a new row such as this for each Front6 in Table1 that matches
>> >the current date.
>> >
>> >Roy Harvey
>> >Beacon Falls, CT
>> >
>> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
>> ><Paul@.discussions.microsoft.com> wrote:
>> >
>> >>Hi thanks for the response. Here are more details on what I am trying to do.
>> >>There is a table (table1) that has hundreds of records that look like
>> >>JTB-ABC-MDTC-06200-0001
>> >>JCV-BCD-ABAM-06201-0001
>> >>JTB-ABC-MDAC-06200-0002
>> >>
>> >>I need the stored procedure to take the value
>> >>JCV-BCD-RBSV
>> >>and build the rest of it based on the following conditions and then save it
>> >>to a table.
>> >>Get the current julian date, today would be 06242, two hundred forty two day
>> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
>> >>and only the last section still to be built. This is done by doing the
>> >>following.
>> >>1. Compare the jul date for the day the stored procedure will run (06242)
>> >>and find all records in table 1 with the same date (call this subset a).
>> >>2. Next out of subset a find all that match the first 6 letters (create
>> >>subset b).
>> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
>> >>it was 0002).
>> >>4. Finally increment this value by 1 and use it to finish the newly created
>> >>value, so we would have JVC-BCD-RBSV-06242-0003.
>> >>This is easy to do in vb or C#.|||ok thanks for the information.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||I tried out the sample and it worked. Anyhow just had a quick last question.
Do you know if there is a way to conditionally run a job? I am thinking of
scheduling the stored procedure but in some cases if a manual data entry has
taken place (through an asp.net web application) I will not want to add a new
record with the scheduled job. I guess this could be properly handled in the
stored procedure(scheduled job), possibly perform some type of table check to
see when the last entry took place to know weather or not to add a record.
Thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getdate())),2) +
> right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I see were you get the current julian date, JYear and JDay but I did not see
> >if combine these to get the 06242 for example for today. thanks.
> >Paul G
> >Software engineer.
> >
> >
> >"Roy Harvey" wrote:
> >
> >> An alternate coding of the WHERE clause:
> >>
> >> WHERE JYear => >> right(convert(char(4),datepart(year,getdate())),2)
> >> AND JDay => >> right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> AND First6 = SUBSTRING(@.front,1,6)
> >>
> >> Roy
> >>
> >> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> >> wrote:
> >>
> >> >Of course all the parts of that complex column should be individual
> >> >table columns, but I will not belabor that point.
> >> >
> >> >There is nothing about that which requires going to C# or VB. This
> >> >should give you some ideas. Note that I used a view, but each of the
> >> >pieces could have been substringed out when references. However since
> >> >all the real work is one in one SQL command, all those references
> >> >would make it a good bit more opaque. Another advantage to the view
> >> >is that it is possible to make it an indexed view, which could make a
> >> >major difference in performance.
> >> >
> >> >CREATE TABLE Table1
> >> >(StrungOut char(23) NOT NULL)
> >> >
> >> >INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> >> >INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> >> >INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> >> >
> >> >GO
> >> >CREATE VIEW Table1_V
> >> >AS
> >> >SELECT StrungOut,
> >> > First6 = SUBSTRING(StrungOut,1,6),
> >> > JDate = SUBSTRING(StrungOut,14,5),
> >> > JYear = SUBSTRING(StrungOut,14,2),
> >> > JDay = SUBSTRING(StrungOut,16,3),
> >> > Last4 = SUBSTRING(Strungout,20,4)
> >> > FROM Table1
> >> >GO
> >> >
> >> >CREATE TABLE Table2
> >> >(StrungOut char(23) NOT NULL)
> >> >GO
> >> >
> >> >CREATE PROC Demonstration
> >> >@.front char(12)
> >> >AS
> >> >
> >> >INSERT Table2
> >> >SELECT @.front + '-' +
> >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3) +
> >> > '-' +
> >> > RIGHT(convert(char(5),(convert(int,MAX(Last4)) + 1) + 10000),4)
> >> > FROM Table1_V
> >> > WHERE JDate => >> > right(convert(char(4),datepart(year,getdate())),2) +
> >> > right(convert(char(4),datepart(dayofyear,getdate())+1000),3)
> >> > AND First6 = SUBSTRING(@.front,1,6)
> >> >GO
> >> >
> >> >EXEC Demonstration 'JCV-BCD-RBSV'
> >> >
> >> >SELECT *
> >> > FROM Table2
> >> >
> >> >StrungOut
> >> >--
> >> >JCV-BCD-RBSV-06242-0006
> >> >
> >> >Also, in the spec you mentioned matching on the first six characters,
> >> >but I wondered if it might have actually been the first seven.
> >> >
> >> >One final point worth making. It would be quite practical to modify
> >> >this so that rather than taking in a string as a parameter, it
> >> >produced a new row such as this for each Front6 in Table1 that matches
> >> >the current date.
> >> >
> >> >Roy Harvey
> >> >Beacon Falls, CT
> >> >
> >> >On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> >> ><Paul@.discussions.microsoft.com> wrote:
> >> >
> >> >>Hi thanks for the response. Here are more details on what I am trying to do.
> >> >>There is a table (table1) that has hundreds of records that look like
> >> >>JTB-ABC-MDTC-06200-0001
> >> >>JCV-BCD-ABAM-06201-0001
> >> >>JTB-ABC-MDAC-06200-0002
> >> >>
> >> >>I need the stored procedure to take the value
> >> >>JCV-BCD-RBSV
> >> >>and build the rest of it based on the following conditions and then save it
> >> >>to a table.
> >> >>Get the current julian date, today would be 06242, two hundred forty two day
> >> >>of year 06. The value we are building would then look like JVC-BCD-RBSV-06242
> >> >>and only the last section still to be built. This is done by doing the
> >> >>following.
> >> >>1. Compare the jul date for the day the stored procedure will run (06242)
> >> >>and find all records in table 1 with the same date (call this subset a).
> >> >>2. Next out of subset a find all that match the first 6 letters (create
> >> >>subset b).
> >> >>3. Next out of subset b find the greatest value of the last 4 numbers (say
> >> >>it was 0002).
> >> >>4. Finally increment this value by 1 and use it to finish the newly created
> >> >>value, so we would have JVC-BCD-RBSV-06242-0003.
> >> >>This is easy to do in vb or C#.
> >>
>|||If the proc should NOT add an entry based on conditions that it can
test in the database, then by all means put those tests in the proc,
and just schedule it to run unconditionally. Coding to prevent bad
data in your database is what we all strive for. It also means you
can schedule it to simply execute and not worry about it.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 14:33:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>I tried out the sample and it worked. Anyhow just had a quick last question.
> Do you know if there is a way to conditionally run a job? I am thinking of
>scheduling the stored procedure but in some cases if a manual data entry has
>taken place (through an asp.net web application) I will not want to add a new
>record with the scheduled job. I guess this could be properly handled in the
>stored procedure(scheduled job), possibly perform some type of table check to
>see when the last entry took place to know weather or not to add a record.
>Thanks.|||ok sounds like a good idea!
--
Paul G
Software engineer.
"Roy Harvey" wrote:
> If the proc should NOT add an entry based on conditions that it can
> test in the database, then by all means put those tests in the proc,
> and just schedule it to run unconditionally. Coding to prevent bad
> data in your database is what we all strive for. It also means you
> can schedule it to simply execute and not worry about it.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 14:33:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I tried out the sample and it worked. Anyhow just had a quick last question.
> > Do you know if there is a way to conditionally run a job? I am thinking of
> >scheduling the stored procedure but in some cases if a manual data entry has
> >taken place (through an asp.net web application) I will not want to add a new
> >record with the scheduled job. I guess this could be properly handled in the
> >stored procedure(scheduled job), possibly perform some type of table check to
> >see when the last entry took place to know weather or not to add a record.
> >Thanks.
>

can this be done in SQL2000

Hi I need to create a stored procedure that can do the following but now sur
e
if it can be done with sql2000.
1. read in existing data from a table.
2. create the current Julian date.
3. make a comparison and add a sequence number onto this number if it is a
specific Julian date.
I think all of this could probably be done with 2005 since it allows using
C#,vb for TSQL.
Paul G
Software engineer.On Wed, 30 Aug 2006 08:18:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:

>Hi I need to create a stored procedure that can do the following but now su
re
>if it can be done with sql2000.
>1. read in existing data from a table.
Stored procedures are very good at retrieving data from database
tables.

>2. create the current Julian date.
SQL Server has sufficient tools for date manipulation that calculating
Julian date from the current date (getdate()) should be no problem. It
does, however, require knowing which definition of Julian date is
intended.

>3. make a comparison and add a sequence number onto this number if it is a
>specific Julian date.
Comparison of what to what? By "this number" do you mean the Julian
data calculated in item 2? What specific Julian date? One passed as
a parameter to the stored procedure? One retrieved from the table in
item 1?

>I think all of this could probably be done with 2005 since it allows using
>C#,vb for TSQL.
I am sure it can be done in 2005. It is unclear from the information
give that it will require C# or VB, but if it turns out to be
complicated they are available. Perhaps if you provided a bit more
detail someone will be able to suggest an appropriate approach.

>Paul G
>Software engineer.
Roy Harvey
Beacon Falls, CT|||Hi thanks for the response. Here are more details on what I am trying to do.
There is a table (table1) that has hundreds of records that look like
JTB-ABC-MDTC-06200-0001
JCV-BCD-ABAM-06201-0001
JTB-ABC-MDAC-06200-0002
I need the stored procedure to take the value
JCV-BCD-RBSV
and build the rest of it based on the following conditions and then save it
to a table.
Get the current julian date, today would be 06242, two hundred forty two day
of year 06. The value we are building would then look like JVC-BCD-RBSV-0624
2
and only the last section still to be built. This is done by doing the
following.
1. Compare the jul date for the day the stored procedure will run (06242)
and find all records in table 1 with the same date (call this subset a).
2. Next out of subset a find all that match the first 6 letters (create
subset b).
3. Next out of subset b find the greatest value of the last 4 numbers (say
it was 0002).
4. Finally increment this value by 1 and use it to finish the newly created
value, so we would have JVC-BCD-RBSV-06242-0003.
This is easy to do in vb or C#.
--
Paul G
Software engineer.
"Roy Harvey" wrote:

> On Wed, 30 Aug 2006 08:18:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Stored procedures are very good at retrieving data from database
> tables.
>
> SQL Server has sufficient tools for date manipulation that calculating
> Julian date from the current date (getdate()) should be no problem. It
> does, however, require knowing which definition of Julian date is
> intended.
>
> Comparison of what to what? By "this number" do you mean the Julian
> data calculated in item 2? What specific Julian date? One passed as
> a parameter to the stored procedure? One retrieved from the table in
> item 1?
>
> I am sure it can be done in 2005. It is unclear from the information
> give that it will require C# or VB, but if it turns out to be
> complicated they are available. Perhaps if you provided a bit more
> detail someone will be able to suggest an appropriate approach.
>
> Roy Harvey
> Beacon Falls, CT
>|||Of course all the parts of that complex column should be individual
table columns, but I will not belabor that point.
There is nothing about that which requires going to C# or VB. This
should give you some ideas. Note that I used a view, but each of the
pieces could have been substringed out when references. However since
all the real work is one in one SQL command, all those references
would make it a good bit more opaque. Another advantage to the view
is that it is possible to make it an indexed view, which could make a
major difference in performance.
CREATE TABLE Table1
(StrungOut char(23) NOT NULL)
INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
GO
CREATE VIEW Table1_V
AS
SELECT StrungOut,
First6 = SUBSTRING(StrungOut,1,6),
JDate = SUBSTRING(StrungOut,14,5),
JYear = SUBSTRING(StrungOut,14,2),
JDay = SUBSTRING(StrungOut,16,3),
Last4 = SUBSTRING(Strungout,20,4)
FROM Table1
GO
CREATE TABLE Table2
(StrungOut char(23) NOT NULL)
GO
CREATE PROC Demonstration
@.front char(12)
AS
INSERT Table2
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getd
ate())),2) +
right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
FROM Table1_V
WHERE JDate =
right(convert(char(4),datepart(year,getd
ate())),2) +
right(convert(char(4),datepart(dayofyear
,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
GO
EXEC Demonstration 'JCV-BCD-RBSV'
SELECT *
FROM Table2
StrungOut
--
JCV-BCD-RBSV-06242-0006
Also, in the spec you mentioned matching on the first six characters,
but I wondered if it might have actually been the first seven.
One final point worth making. It would be quite practical to modify
this so that rather than taking in a string as a parameter, it
produced a new row such as this for each Front6 in Table1 that matches
the current date.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 09:09:02 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:

>Hi thanks for the response. Here are more details on what I am trying to do
.
>There is a table (table1) that has hundreds of records that look like
>JTB-ABC-MDTC-06200-0001
>JCV-BCD-ABAM-06201-0001
>JTB-ABC-MDAC-06200-0002
>I need the stored procedure to take the value
>JCV-BCD-RBSV
>and build the rest of it based on the following conditions and then save it
>to a table.
>Get the current julian date, today would be 06242, two hundred forty two da
y
>of year 06. The value we are building would then look like JVC-BCD-RBSV-062
42
>and only the last section still to be built. This is done by doing the
>following.
>1. Compare the jul date for the day the stored procedure will run (06242)
>and find all records in table 1 with the same date (call this subset a).
>2. Next out of subset a find all that match the first 6 letters (create
>subset b).
>3. Next out of subset b find the greatest value of the last 4 numbers (say
>it was 0002).
>4. Finally increment this value by 1 and use it to finish the newly created
>value, so we would have JVC-BCD-RBSV-06242-0003.
>This is easy to do in vb or C#.|||An alternate coding of the WHERE clause:
WHERE JYear =
right(convert(char(4),datepart(year,getd
ate())),2)
AND JDay =
right(convert(char(4),datepart(dayofyear
,getdate())+1000),3)
AND First6 = SUBSTRING(@.front,1,6)
Roy
On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:
[vbcol=seagreen]
>Of course all the parts of that complex column should be individual
>table columns, but I will not belabor that point.
>There is nothing about that which requires going to C# or VB. This
>should give you some ideas. Note that I used a view, but each of the
>pieces could have been substringed out when references. However since
>all the real work is one in one SQL command, all those references
>would make it a good bit more opaque. Another advantage to the view
>is that it is possible to make it an indexed view, which could make a
>major difference in performance.
>CREATE TABLE Table1
>(StrungOut char(23) NOT NULL)
>INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
>INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
>INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
>INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
>GO
>CREATE VIEW Table1_V
>AS
>SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
>GO
>CREATE TABLE Table2
>(StrungOut char(23) NOT NULL)
>GO
>CREATE PROC Demonstration
>@.front char(12)
>AS
>INSERT Table2
>SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate =
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
>GO
>EXEC Demonstration 'JCV-BCD-RBSV'
>SELECT *
> FROM Table2
>StrungOut
>--
>JCV-BCD-RBSV-06242-0006
>Also, in the spec you mentioned matching on the first six characters,
>but I wondered if it might have actually been the first seven.
>One final point worth making. It would be quite practical to modify
>this so that rather than taking in a string as a parameter, it
>produced a new row such as this for each Front6 in Table1 that matches
>the current date.
>Roy Harvey
>Beacon Falls, CT
>On Wed, 30 Aug 2006 09:09:02 -0700, Paul
><Paul@.discussions.microsoft.com> wrote:
>|||Hi thanks for the response. Yes the table was already setup and there was
not time to restructure. Will take a look at what you have. Was not aware
that you can do a lot with SQL.
--
Paul G
Software engineer.
"Roy Harvey" wrote:

> Of course all the parts of that complex column should be individual
> table columns, but I will not belabor that point.
> There is nothing about that which requires going to C# or VB. This
> should give you some ideas. Note that I used a view, but each of the
> pieces could have been substringed out when references. However since
> all the real work is one in one SQL command, all those references
> would make it a good bit more opaque. Another advantage to the view
> is that it is possible to make it an indexed view, which could make a
> major difference in performance.
> CREATE TABLE Table1
> (StrungOut char(23) NOT NULL)
> INSERT Table1 values ('JTB-ABC-MDTC-06200-0001')
> INSERT Table1 values ('JCV-BCD-ABAM-06201-0001')
> INSERT Table1 values ('JTB-ABC-MDAC-06200-0002')
> INSERT Table1 values ('JCV-BCD-ABAM-06242-0005')
> GO
> CREATE VIEW Table1_V
> AS
> SELECT StrungOut,
> First6 = SUBSTRING(StrungOut,1,6),
> JDate = SUBSTRING(StrungOut,14,5),
> JYear = SUBSTRING(StrungOut,14,2),
> JDay = SUBSTRING(StrungOut,16,3),
> Last4 = SUBSTRING(Strungout,20,4)
> FROM Table1
> GO
> CREATE TABLE Table2
> (StrungOut char(23) NOT NULL)
> GO
> CREATE PROC Demonstration
> @.front char(12)
> AS
> INSERT Table2
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> FROM Table1_V
> WHERE JDate =
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> GO
> EXEC Demonstration 'JCV-BCD-RBSV'
> SELECT *
> FROM Table2
> StrungOut
> --
> JCV-BCD-RBSV-06242-0006
> Also, in the spec you mentioned matching on the first six characters,
> but I wondered if it might have actually been the first seven.
> One final point worth making. It would be quite practical to modify
> this so that rather than taking in a string as a parameter, it
> produced a new row such as this for each Front6 in Table1 that matches
> the current date.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 09:09:02 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
>|||I see were you get the current julian date, JYear and JDay but I did not see
if combine these to get the 06242 for example for today. thanks.
Paul G
Software engineer.
"Roy Harvey" wrote:

> An alternate coding of the WHERE clause:
> WHERE JYear =
> right(convert(char(4),datepart(year,getd
ate())),2)
> AND JDay =
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3)
> AND First6 = SUBSTRING(@.front,1,6)
> Roy
> On Wed, 30 Aug 2006 13:00:08 -0400, Roy Harvey <roy_harvey@.snet.net>
> wrote:
>
>|||JYear and JDay are the pieces of the Julian date in Table1, so they
are only used for the comparison.
In the assignment:
SELECT @.front + '-' +
right(convert(char(4),datepart(year,getd
ate())),2) +
right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
the Julian date part of the output string is the second and third
lines. BUT, I just thought of an alternate way to code the
assignment. We went to all that trouble to make sure Table1_V.JDate
matched the current day, so instead of using the current day we could
just use Table1_V.JDate.
SELECT @.front + '-' +
JDate +
'-' +
RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
However, I'm guessing that IF there is no matching row with the
current date you might actually want to create the new row with 0000
or 0001 as the final part of the string. In that case I would keep to
using the derivation from getdate() as it will be easier to code in
the row-not-found branch.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Aug 2006 10:32:01 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I see were you get the current julian date, JYear and JDay but I did not se
e
>if combine these to get the 06242 for example for today. thanks.
>Paul G
>Software engineer.
>
>"Roy Harvey" wrote:
>|||ok thanks for the information.
--
Paul G
Software engineer.
"Roy Harvey" wrote:

> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
>|||I tried out the sample and it worked. Anyhow just had a quick last question
.
Do you know if there is a way to conditionally run a job? I am thinking of
scheduling the stored procedure but in some cases if a manual data entry has
taken place (through an asp.net web application) I will not want to add a ne
w
record with the scheduled job. I guess this could be properly handled in th
e
stored procedure(scheduled job), possibly perform some type of table check t
o
see when the last entry took place to know weather or not to add a record.
Thanks.
--
Paul G
Software engineer.
"Roy Harvey" wrote:

> JYear and JDay are the pieces of the Julian date in Table1, so they
> are only used for the comparison.
> In the assignment:
> SELECT @.front + '-' +
> right(convert(char(4),datepart(year,getd
ate())),2) +
> right(convert(char(4),datepart(dayofyear
,getdate())+1000),3) +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> the Julian date part of the output string is the second and third
> lines. BUT, I just thought of an alternate way to code the
> assignment. We went to all that trouble to make sure Table1_V.JDate
> matched the current day, so instead of using the current day we could
> just use Table1_V.JDate.
> SELECT @.front + '-' +
> JDate +
> '-' +
> RIGHT(convert(char(5),(convert(int,MAX(L
ast4)) + 1) + 10000),4)
> However, I'm guessing that IF there is no matching row with the
> current date you might actually want to create the new row with 0000
> or 0001 as the final part of the string. In that case I would keep to
> using the derivation from getdate() as it will be easier to code in
> the row-not-found branch.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 30 Aug 2006 10:32:01 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
>