can we have temporary table in store procedure when using BCP ?

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'

it show result , however when i run it using BCP tools

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way

CREATE PROCEDURE [SP_TEST]

--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B

SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D

----------BEGINNING OF PROCESS------

SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2

)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

----------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO

when i run in query analyzer , using "exec SP_TEST" , it work

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.

do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.

I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.

Wait until Monday, and lets see if any other forum members recognize this error.|||Hi

Did a quick RTFM:

Copying Data To or From a Temporary Table

When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".

HTH

EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,

This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.

bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:

SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF

If you use the "queryout" operator with an exec statement, it issues the following command:

SET FMTONLY ON
exec ...
SET FMTONLY OFF

Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.

FYI, a solution with a table variable WILL work, e.g.

<code>
create proc dbo.bills_test
as
begin

declare @.output table (col1... , col2 ... , ...)

insert @.output
select ...

select col1, col2 ...
from @.output

end
<\code>

you can play with this in query analyser, to get a stored proc that will work with bcp...

HTH,

Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

will tempdb object will causing any performance issue , i really not sure ..

:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.

Oh, for the love of god....

In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders

SELECT * FROM @.myTabVar99
GO

EXEC mySproc99

exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'

DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql

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

Can the report table have varying number of columns ??

The requierment is tyo have a dynamic report, that has a table with number of
columns decided on the input from recordset.
The SQL procedure takes in parameters and returns recordset, that can have
varying number of columns. All we want to do is to link this recordset to a
table on the report.
The report table should show the columns as they are in the Recordset. ( and
the column name in the recordset shall appear as the column header in the
Reporting table.)
-- Alok Kumar GuptaThere's really no good way to do this in the current version, no.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Alok" <Alok@.discussions.microsoft.com> wrote in message
news:47BD7BFE-DCAF-4DC1-A360-98BF8F9FB586@.microsoft.com...
> The requierment is tyo have a dynamic report, that has a table with number
of
> columns decided on the input from recordset.
> The SQL procedure takes in parameters and returns recordset, that can have
> varying number of columns. All we want to do is to link this recordset to
a
> table on the report.
> The report table should show the columns as they are in the Recordset. (
and
> the column name in the recordset shall appear as the column header in the
> Reporting table.)
> -- Alok Kumar Gupta
>

can stored procedure run more than 1 sql statements

Hi all,
I have 2 independent stored procedures(I cannot join them because of
the group condition are different however they have the same cust id)
but one for reading the summary and other showing the details of the
summary. Now I want to run these two stored procedures in order to
get the both return results for generating a .Net crytral report. Can
I get the results form both stored procedures and how can I do that.
Please help me. Thanks a lot.
Hellosixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello

Hi,

Why not use views for this. You could achieve with that.

-Manoj|||..NET should be able to handle multiple result sets from a single sp.

You can "spell out" both SQL/T-SQL code blocks in the same sp or you
can create two seperate sp's and call sp B from sp A via an EXECUTE
statement.

Good Luck

sixtttttt@.msn.com (Hello) wrote in message news:<68f89abd.0309190940.2ae3062d@.posting.google.com>...
> Hi all,
> I have 2 independent stored procedures(I cannot join them because of
> the group condition are different however they have the same cust id)
> but one for reading the summary and other showing the details of the
> summary. Now I want to run these two stored procedures in order to
> get the both return results for generating a .Net crytral report. Can
> I get the results form both stored procedures and how can I do that.
> Please help me. Thanks a lot.
> Hello|||groups@.rlmoore.net (rm) wrote in message news:<4c61f49.0309191421.14d680a4@.posting.google.com>...
> .NET should be able to handle multiple result sets from a single sp.
> You can "spell out" both SQL/T-SQL code blocks in the same sp or you
> can create two seperate sp's and call sp B from sp A via an EXECUTE
> statement.
> Good Luck
I have try the 2nd method (spA exec spB) but it seem that nothing can
return at the .net crystal report even I do not use the subreport. is
there other methods?
Pls help me.
Thx
Hello

Can stored procedure be called recursively?

Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?

what happens when you run this:

---

create table test (i int)
go
insert into test values(0)
go
create proc TryTest
as
declare @.i int
update test set i = i + 1
select @.i = i from test
print @.i
exec TryTest
go
exec TryTest

|||? Yes, it can. 32 levels. You can also use @.@.NESTLEVEL to determine the current level: CREATE PROCEDURE recurseASBEGIN PRINT @.@.NESTLEVEL EXEC recurseENDGO EXEC recurseGO DROP PROC recurseGO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <nonno@.discussions.microsoft.com> wrote in message news:452f0e66-9594-4818-9d92-6de799ad0dd5@.discussions.microsoft.com...Is stored procedure in mssql 2000 support recursion? Is there any limitation on the number of recursion?|||

Does @.@.NESTLEVEL has a transactional scope?

What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction?

Like:
UserA calls the procudure for the first time: NestLevel = 1
UserA calls the procedure again: NestLevel = 2

What will be the value of NestLevel for user B? 1 or 3?

TIA,

Isabel

|||? 1. It's the nesting level for the current scope; it has nothing to do with some other connection's scope. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Isabel_ve@.discussions..microsoft.com> wrote in message news:dd99b941-0788-4916-a1f8-19fe6117f330@.discussions.microsoft.com... Does @.@.NESTLEVEL has a transactional scope? What happens with the value of @.@.NESTLEVEL when two users (from my application) will call the same procedure, but both users have a different transaction? Like:UserA calls the procudure for the first time: NestLevel = 1UserA calls the procedure again: NestLevel = 2 What will be the value of NestLevel for user B? 1 or 3? TIA, Isabel

Can Store Procedure Return Rows?

Can store procedure return rows?

If store procedure can return rows, how to use ASP.NET to receive it? thanks!

Yes, Stored Procedure (SP) can return rows, accept parameters for updating/inserting rows, and do other cool stuff related to manipulating your DB. Normally, SP's that return rows can replace the usual SELECT SQL statements in most of the ASP.NET data controls. The topic is a vast one, so you should google for "ASP.NET Stored Procedure" for various articles available on the net.|||Thank you for the reply!

Can sqldatasource automatically sort retrieved data from stored procedure ?

Hi,

This is my problem :

I have listBox1, listBox2 controls bound to sqldatasource1 and sqldatasource2.

I wrote one stored procedure to retrieve data from my sql database like :

select Id, Name,Address,Amount from KalakaDB

Now I want my listBox1 to display Name By name and my listBox2 to display Amount by decrease order

how can i connect my sqldatacontrol to the stored procedure ?

Thanks

Typically sorting is controlled by the data bound control (for example, the GridView). However, the sorting is actually performed by the data source. In this case if you don't have a way to tell the data bound control which sort express to use, you can do it directly on the data source by handling SqlDataSource's Selecting event. In that event, simply say something like:

e.Arguments.SortExpression = "Amount DESC";

Thanks,

Eilon

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to sto
p
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.
Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:

>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Can sqlagent.out be cycled

Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
what options do I have to clear the sqlagent.out file without having to stop
the SQL agent.Unfortunately, just restarting the service would do this -
nothing built in to cycle the Agent logs.
-Sue
On Wed, 17 Aug 2005 07:03:04 -0700, "Steve R" <Steve
R@.discussions.microsoft.com> wrote:
>Is there a way of cycling the sqlagent.out file in SQL2000 (similar to
>cycling the error logs via the sp_cycle_errorlog stored procedure). If not,
>what options do I have to clear the sqlagent.out file without having to stop
>the SQL agent.

Can SQL break an array into one string? [Stored Procedure]

Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like

SELECT * FROM [eai.Documents] WHERECategoryId=11 ORCategoryId=16 ORCategoryId=18.

My question is: Can I do the same in my stored procedure?
Here is it:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[eai.GetSubCategoriesById](@.Idint)ASdeclare @.pathvarchar(100);SELECT @.path=PathFROM [eai.FileCategories]WHERE Id = @.Id;SELECT Id, ParentCategoryId,Name, NumActiveAdsFROM [eai.FileCategories]WHERE PathLIKE @.Path +'%'ORDER BY Path

Thank you
Artashes

There is no Array in Stored procedure, but you do can it by use dynamic sql . pass stored procedure a string as 11,16,18

then

in store procedure do

exec N'select * from yourTable where id in ' + @.idlist

Hope this help

|||

DavidDu thank you for answer. I found another solution.

It is herehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=475225&SiteID=1

Artashes

Can someone please help with this problem, this is my first time at this forum

Need Help in getting started on a SP in SQL SERVER2000
I need help in writing a stored procedure on SQL Server 2000. Basically the stored procedure's primary task is to generate invoice records and insert the records in a invoice table. In order to generate the invoice records, I have an initial table which are basically Day Records. The task that I would like to complete is to select all records from that initial table and I guess put them into a temp table. Now that i have my temp table, I would like to loop thru the table record by record, and do inserts in the invoice table. I cant seem to figure out the syntax since I am somewhat weak in TSQL Programming. I would appreciate any assistance on this forum or to my email. Also If you need some pseudocode for the process or the DDL for the initial table and the invoice table, I can definitely post that in the forum possibly in the next thread. Please advise, I would need a full written syntax to get me started since i have some other processes that I would need to build using the template. Thanks again.Why do you want to loop through the table instead of running a set-based SQL query against the entire dataset? What you are talking about doing is setting up an SQL Cursor which will step through the data one record at a time, but this is an ineffecient method of handling database data, and is only used when there is no other alternative (very rare) or by VB programmers who don't know TSQL (unfortunately very common).
The task you describe is probably very simple for someone with some knowledge of SQL, but if you are trying to wing-it you are heading into trouble. You need to get some expertise, either in the form of a good SQL Programming turorial or a good SQL consultant.

blindman