Complex Stored Procedure On History Table
Jun 7, 2006
Hi All,
I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?
Thanks in advance : )
View 1 Replies
ADVERTISEMENT
Mar 19, 2001
I'm very new to the more advanced aspects of SQL server, such as Stored Procedures. I've come upon a problem that I've been unable to find in one of my reference books. I have data stored dynamically in different columns with dynamic labels. Meaning that for instance, a set of columns named TXT_1 through TXT_10 could contain anything from a description of an item to a part number, to the color, but in any order. There is a seperate table that contains the key as to what a particular column represents for a particular item. I need to pull all of that out, and I've done it before using VBA in my active server pages. However, now I'm working on a report in Crystal Reports that will display all of this data, and the best scenario I can think of is if I were able to assemble all of the data properly using a stored procedure before passing the data to Crystal. What I don't know how to do is use the result set from the key table which will return three fields, such as Item_no, Label, and then the name of the column in the other table from which to pull the data for example "TXT_1". This field is returned as a string value. How can I write a dynamic select statement that can select from any of the columns (TXT_1 - TXT_10) based on the string that I pull from the key table? Any help would be very much appreciated.
View 1 Replies
View Related
Apr 15, 2008
Dear All,
I have a complex stored proc, which I inherited from the previous developer, and I wish to fine tune it a bit, because its taking 1.36 mins to retreive 5383 rows, which I think is quite excessive.
The stored proc is as follows:-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getpageidsforedb]
@projectid int
AS
create table #ebdindex (
[indexid] [int] IDENTITY (1, 1) NOT NULL ,
[stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[trans] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[status] [int]
)
create table #residinpages (
[indexid] [int],
[stringid] [nvarchar](4000)COLLATE Latin1_General_CI_AS,
[pageid] [int]
)
create table #residinpages2 (
[indexid] [int],
[pageid] [int]
)
insert into #ebdindex
Select stringid, trans, status from edbs e where
e.lcid='en' and e.projectid=@projectid
insert into #residinpages
select distinct e.indexid, e.stringid, CASE
WHEN s.pageid IS NULL then 0
ELSE s.pageid
END as pageid
from #ebdindex e,
stringidsinpages s, pages p
where e.stringid *=s.stringid
order by e.indexid
declare @indexId nchar(128)
declare @pageId nchar(128)
declare @RowNum int
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
set @RowNum = 0
WHILE @RowNum < (select max(indexid) from #ebdindex)
BEGIN
set @RowNum = @RowNum + 1
insert into #residinpages2 values( @indexId, @pageid)
select top 1 @indexId=indexID, @pageid=pageid from #residinpages
where indexId > @indexId
END
select e.indexid,e.stringid, e.trans, r.pageid, e.status from #residinpages2 r, #ebdindex e
where e.indexid=r.indexid
drop table #residinpages2
drop table #residinpages
drop table #ebdindex
Is there another way to write this stored proc, to make it return the results faster? I am new in SQL and this is quite a complex one for me.
Your help is much appreciated
Thanks
Regards
Johann
View 7 Replies
View Related
Apr 14, 2008
I created a site that will link the buyer and the seller together (think of ebay) for example:
Bob goes to my site to post an item he wants to sell (lets say a cell phone)
Steve goes to my site looking for a cell phone and he purchased Bob€™s cell phone (transaction is done on Paypal€™s site).
I have to make sure only one person check the same item at one time. For example
Steve and Mike are looking for a cell phone; Steve and Mike like the same cell phone. If Steve clicked on buy now Mike should not be able to check out the same item/or make mike wait to see if the Steve going to processed with his payment
after a lot of research i should do the check on the data layer not in the UI. so i need help to created a stored procedure that will do the following (do you think this is a good idea)
Case 1
1- Check if there is an order for this item in table checkout lets say item number is 45
Checkout Table
Item Number Email isComplete DTS
2- if there is no order ,a row should be created in checkout table
insert into checkout values (@itemnumber,@email,false,getdate())
Item Number Email isComplete DTS
45 Steve@hotmail.com false 04/12/2008 1:13:45
the stored procedure should return €œtrue€? at this point
3- whenever the column isComplete updated to true by another storedprocedure the row should be deleted
Delete from checkout where Item Number =45
Case2
1- Check if there is an order for this item in table checkout
2- If yes I need to check if it is => 20 minutes if true also the column should be updated with the newest date then it should return true
If <20 return true;
here are my stored procedure parameters
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
@ProductID int,
@BuyerEmail varchar(200)
AS
BEGIN
declare @DTS as datetime
declare @IsComplete as bit
select @DTS = getdate();
END
GO
Thanks
View 4 Replies
View Related
Jun 5, 2015
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
View 4 Replies
View Related
Apr 6, 2008
hi!
i have two tables: Pictures (PictureID, UserName, UserID, DateAdded, Comments int) and UserVisits (VisitID, UserName, UserID, PictureID, NewComment bit)
if a new comment is added on a picture with PictureID = 4, i update NewComment from UserVisits with 1 (true) where PictureID = 4 to all rows,
and if a user visits a picture with PictureID = 5, NewComment will be set to 0 back where pictureID = 5 (something like asp.net, when a reply is added to a therad)
i need to make a stored procedure with UserName input parameter witch selects ALL pictures from Pictures table and selects NewComment (true or false) from UserVisits witch corresponds with that UserName
!!! Is possible that in UserVisits to not have yet any column where UserName corresponds with the user who visits if the user did not visited that picture yet (this stored procedure will be used on a Repeater witch lists all the pictures)
if i was not clear please tell me, sorry i am verry bad at T-SQL language
thanks in advance
View 2 Replies
View Related
Jul 19, 2000
I have the following design as my starting point.
4 sql tables.
I will be doing a variety of searches against these 4 tables (525 different searches to be exact).
Which search is performed is based on table requested and the search criteria fields passed. One of the four tables will always be searched and one of the other three as well.
I have a driver stored procedure that determines which of the 3 tables to read and calls separate subordinate stored procedures for the table selected. Then it calls a stored procedure to select from the table that is always read.
I am building temporary tables to hold the results from the two tables read in each pass. Then the driver will return the results to the client.
My question is this, is there a more elegant way to determine which one of my 525 queries need to be executed than a very ugly large if/then/else, checking all of the possible combinations of parameters passed in each subordinate stored procedure?
Is there some way to create dynamic sql select statements based on the parameters passed to the subordinate stored procedures that have actual values (not null)?
Any insight would be a big help.
Judy
View 3 Replies
View Related
Aug 24, 2007
Hello,
I have a procedure stored in my application which takes a very very long time to execute.
It deals with the relations of compositon of a product (a computer is made up of a screen, a keyboard and electronic components).
The subject is about component/ sub component relations (article or product breakdown) also called non hierachical relationships.
The products have a code composed of 22 characters (ARTICLE_CODE) but the links in the breakdown between articles are builted on the first 12 characters (ROOT_ARTICLE_CODE).
So in the relationship table we can find an article (V_ROOT_PART_NUMBER_CODE) associated with several articles (N_ARTICLE_FATHER_ID) because they share the 12 first characters
Example
N_ARTICLE_ID;N_FATHER_ID;N_DATASOURCE_ID;N_SOURCE_ID;V_ROOT_PART_NUMBER_CODE;V_NODE_LABEL;B_ACTIVE;V_FATHER_ROOT_PART_NUMBER_CODE;V_FATHER_NODE_LABEL;B_FATHER_ACTIVE
1005;20637;2;14;5M4K85638000;5M4K85638000 - TEST7;1;TK18J1066002;TK18J1066002 - TEST1;1
1005;20638;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67000;A2HG31T67000 - TEST2;1
1005;20639;2;14;5M4K85638000;5M4K85638000 - TEST7;1;A2HG31T67002;A2HG31T67002 - TEST3;1
1005;23946;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD231000;T5D8FD231000 - TEST4;1
1005;24034;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109862;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109876;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
1005;109877;2;14;5M4K85638000;5M4K85638000 - TEST7;1;T5D8FD277000;T5D8FD277000 - TEST5;1
9560;706;2;12;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9550;706;2;12;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1
14049;706;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1
14049;707;2;12;5F4RE5126200;5F4RE5126200 - TEST10;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9550;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST9;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9560;707;2;-6;5F4RE5135200;5F4RE5135200 - TEST8;1;5F4RE5145002;5F4RE5145002 - TEST6;1
9554;707;2;-6;5F4RE5132202;5F4RE5132202 - TEST11;1;5F4RE5145002;5F4RE5145002 - TEST6;1
705;707;2;-6;5F4RE5128202;5F4RE5128202 - TEST12;1;5F4RE5145002;5F4RE5145002 - TEST6;1
18405;707;2;-6;5F4RE5145220;5F4RE5145220 - TEST13;1;5F4RE5145002;5F4RE5145002 - TEST6;1
But sometimes links are missing between identifiers (I have already an existing link between two articles. I have just added an article (new N_ARTICLE_ID) common on the first 12 characters with one of the previous elements.
I must thus create a new link father/ child because of this common element.
I can create several links if the article is linked with seral articles at a component level (the same keyboard can be used in several models of computers)
I make the same procedure for the father (by adding the links with existing child for an article common at 12 characters).
The problem is that it is a very very long procedure.
I have 75000 articles and it is necessary at least 48H for me to manage them.
Could you help me to optimize the code of this stored procedure?
Thank you by advance
Code Snippet
CREATE TABLE [ARTICLE] (
[N_ARTICLE_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[V_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[V_PART_NUMBER_LABEL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[B_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_ARTICLE_CODE_UNIQUE] UNIQUE NONCLUSTERED
(
[V_CODE]
) ON [PRIMARY]
GO
CREATE TABLE [ARTICLE_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL ,
[N_ARTICLE_FATHER_ID] [bigint] NOT NULL ,
[N_DATASOURCE_ID] [bigint] NOT NULL ,
[N_SOURCE_ID] [bigint] NOT NULL ,
[V_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_ACTIVE] [bit] NULL ,
[V_FATHER_ROOT_CODE] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[V_FATHER_NODE_LABEL] [char] (65) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[B_FATHER_ACTIVE] [bit] NULL ,
CONSTRAINT [PK_ARTICLE_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID],
[N_ARTICLE_FATHER_ID],
[N_DATASOURCE_ID],
[N_SOURCE_ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE] FOREIGN KEY
(
[N_ARTICLE_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_ARTICLE1] FOREIGN KEY
(
[N_ARTICLE_FATHER_ID]
) REFERENCES [ARTICLE] (
[N_ARTICLE_ID]
),
CONSTRAINT [FK_ARTICLE_BREAKDOWN_SOURCE] FOREIGN KEY
(
[N_SOURCE_ID]
) REFERENCES [SOURCE] (
[N_SOURCE_ID]
)
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Code Snippet
CREATE PROCEDURE [dbo].[LOAD_ARTICLE_BREAKDOWN_PACKAGE]
@ARTICLE_ID AS BIGINT= NULL,@ARTICLE_FATHER_ID AS BIGINT= NULL, @DATASOURCE AS BIGINT= NULL, @SOURCE AS BIGINT= NULL
AS
BEGIN
SET NOCOUNT ON;
If @ARTICLE_ID IS NOT NULL AND @ARTICLE_FATHER_ID IS NOT NULL AND @DATASOURCE IS NOT NULL AND @SOURCE IS NOT NULL
BEGIN
/*Table pour mettre tous les pn fils
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END*/
CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)
ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]
/*Table pour mettre tous les pn père
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
CREATE TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] (
[N_ARTICLE_ID] [bigint] NOT NULL
)
ALTER TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] WITH NOCHECK ADD
CONSTRAINT [PK_ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN] PRIMARY KEY CLUSTERED
(
[N_ARTICLE_ID]
) ON [PRIMARY]
Declare @LABEL1 as CHAR(50)
Declare @SPARE1 as BIT
Declare @LABEL2 as CHAR(50)
Declare @SPARE2 as BIT
Declare @ARTICLEC as BIGINT
Declare @ARTICLEF as BIGINT
/*information sur le premier PN*/
SET @LABEL1 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)
SET @SPARE1 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_ID))))
/*information sur le second PN*/
SET @LABEL2 = (SELECT TOP 1 CASE WHEN PNLABEL1.V_PART_NUMBER_LABEL IS NULL THEN '' ELSE PNLABEL1.V_PART_NUMBER_LABEL END FROM ARTICLE PNLABEL1
WHERE PNLABEL1.N_ARTICLE_ID IN (SELECT PNLABEL2.N_ARTICLE_ID FROM ARTICLE PNLABEL2 WHERE LEFT(PNLABEL2.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_FATHER_ID)) order by PNLABEL1.V_PART_NUMBER_LABEL desc)
SET @SPARE2 = (SELECT TOP 1 PNSPARE.B_ACTIVE FROM ARTICLE PNSPARE
WHERE PNSPARE.N_ARTICLE_ID in (SELECT PNSPARE1.N_ARTICLE_ID FROM ARTICLE PNSPARE1 WHERE (LEFT(PNSPARE1.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_FATHER_ID))))
/*recherche les articles communs a 12 dans la première boucle pour le fils*/
/*recherche les articles communs a 12 dans la seconde boucle pour le père*/
DELETE FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@DATASOURCE) AND (N_SOURCE_ID=-6) AND (N_ARTICLE_ID = @ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @ARTICLE_FATHER_ID)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@DATASOURCE) AND (N_SOURCE_ID=@SOURCE) AND (N_ARTICLE_ID = @ARTICLE_ID) AND (N_ARTICLE_FATHER_ID = @ARTICLE_FATHER_ID)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @DATASOURCE AS DATASOURCE, @SOURCE AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @LABEL1, @SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @LABEL2 AS FATHER_LABEL, @SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @ARTICLE_FATHER_ID) AND (PN1.N_ARTICLE_ID = @ARTICLE_ID);
END
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_ID))
WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
BEGIN
SET @ARTICLEC = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN)
INSERT INTO #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN
SELECT PNLIST.N_ARTICLE_ID FROM ARTICLE PNLIST
WHERE (LEFT(PNLIST.V_CODE,12) in (SELECT left(V_CODE,12) FROM ARTICLE WHERE N_ARTICLE_ID=@ARTICLE_FATHER_ID))
WHILE EXISTS(SELECT * FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
BEGIN
SET @ARTICLEF = (SELECT TOP 1 N_ARTICLE_ID FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN)
IF (NOT EXISTS(SELECT * FROM ARTICLE_BREAKDOWN WHERE (N_DATASOURCE_ID=@DATASOURCE) AND (N_ARTICLE_ID = @ARTICLEC) AND (N_ARTICLE_FATHER_ID = @ARTICLEF)))
BEGIN
INSERT INTO ARTICLE_BREAKDOWN
(N_ARTICLE_ID, N_ARTICLE_FATHER_ID, N_DATASOURCE_ID, N_SOURCE_ID, V_ROOT_CODE, V_NODE_LABEL,
B_ACTIVE, V_FATHER_ROOT_CODE, V_FATHER_NODE_LABEL, B_FATHER_ACTIVE)
SELECT PN1.N_ARTICLE_ID, PN2.N_ARTICLE_ID AS FATHER_ID, @DATASOURCE AS DATASOURCE, -6 AS SOURCE, left(PN1.V_CODE,12),
left(PN1.V_CODE,12) + ' - ' + @LABEL1, @SPARE1, left(PN2.V_CODE,12) AS FATHER_CODE,
left(PN2.V_CODE,12) + ' - ' + @LABEL2 AS FATHER_LABEL, @SPARE2 AS FATHER_SPARE
FROM ARTICLE PN1 CROSS JOIN
ARTICLE PN2
WHERE (PN2.N_ARTICLE_ID = @ARTICLEF) AND (PN1.N_ARTICLE_ID = @ARTICLEC);
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN WHERE N_ARTICLE_ID = @ARTICLEF
END
DELETE FROM #ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN WHERE N_ARTICLE_ID = @ARTICLEC
END
END
/*if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_CHILD_BREAKDOWN]
END
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[#ODS_TEMP_ARTICLE_PACKAGE_ARTICLE_FATHER_BREAKDOWN]
END*/
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 2 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
Sep 12, 2000
Hi,
I want to know where the history of stored procedures stored, I mean yesterday I created one procedure and today I dropped that procedure and recreate it, I checked in sysobjects table name,crdate it shows only the creation date of the procedure. Where can I find the Modification date or something related to procedure history.
Thanks in Advance,
Seenu
View 4 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Mar 27, 2006
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
Jul 19, 2012
I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
   declare @tTable(col1 int)
   insert into @tTable(col1) values (1)
   select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
View 11 Replies
View Related
Apr 18, 2007
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
Jan 31, 2006
I am working with the following two tables:
Category(NewID,OldID)
Link(CategoryID,BusinessID)
All fields are of Integer Type.
I need to write a stored procedure in sql 2000 which works as follows:
Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)
Then for each rows fetched from last query, execute a update query in the Link table.
For Example,
Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..
UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID
Please help me with the code.
Thanks,
anisysnet
View 1 Replies
View Related
Sep 8, 2006
Hello
Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?
insert #MyTempTable
exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3
JOIN dbo.OtherTable...
I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.
I just can't figure it out
Many Thanks!
Worf
View 2 Replies
View Related
Jul 23, 2005
Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Dec 12, 2007
How do you write a store procedure to meet the following conditions:
PROXIMITY SERACH PROCEDURE
COMPANY table
PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int
COMPANY_KEYWORD table
PK, FK1 company_id int
PK keyword varchar(100)
The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,
A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.
proximity_search
@search_expression varchar(255)
,@x_locint
,@y_locint
,@max_distanceint
,@max_recordsint
Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT -Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.
Distance can be calculated using the following formula:
Distance = sqrt( square(x1-x2) + square(y1-y2) )
THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be
appropriate.
View 4 Replies
View Related
Dec 12, 2007
How do you write a store procedure to meet the following conditions:
PROXIMITY SERACH PROCEDURE
COMPANY table
PK company_id int
company_name varchar(100)
premise varchar(10)
street varchar(30)
town varchar(100)
postcode char(7)
x_loc int
y_loc int
COMPANY_KEYWORD table
PK, FK1 company_id int
PK keyword varchar(100)
The data above shows an extract of a company listings database. The company table holds the name and address of the company as well as a physical location in terms of an Easting and Northing co-ordinate. Each company has one or more keywords or phrases associated with it in the company_keyword table. The full database holds 5 million companies with an average of 4 keywords each,
A stored procedure is required to provide proximity searching of the database. The procedure should return a list of companies that satisfy the keyword search expression and fall within the defined maximum distance of the specified location. Results should be limited to show the closest companies up to a maximum of @max_records.
proximity_search
@search_expression varchar(255)
,@x_locint
,@y_locint
,@max_distanceint
,@max_recordsint
Example Search Expressions
@search_expression - Description
TESCO AND CASHPOINT -Return all companies that have all of the
keywords specified.
TESCO OR SAINSBURY’’S - Return all records that have one or more of
the keywords specified.
TESCO EXPRESS AND CASHPOINT
OR
SAINSBURY’S LOCAL - Return all companies that have either both of
the first two keywords or the third.
Distance can be calculated using the following formula:
Distance = sqrt( square(x1-x2) + square(y1-y2) )
THE QUESTION
1. Create a stored procedure to satisfy the requirement shown above
2.Comment on the database design and identify any indexes that may be
appropriate.
View 7 Replies
View Related
Mar 2, 2004
PAYROLL
PID|PNAME|STARTDATE|ENDDATE
1|BATCH1|2004-01-01|2004-01-04
2|BATCH2|2004-01-01|2004-01-04
TIMEENTRY
TID|PID|USERID|DATE|HOURS
1|1|49|2004-01-01|7
2|1|49|2004-01-02|8
3|1|49|2004-01-03|8
4|1|49|2004-01-04|6
.
.
.
11|1|50|2004-01-01|5
12|1|50|2004-01-02|2
13|1|50|2004-01-03|8
14|1|50|2004-01-04|8
21|2|49|2004-01-01|4
22|2|49|2004-01-02|2
23|2|49|2004-01-03|2
24|2|49|2004-01-04|8
.
.
.
31|2|50|2004-01-01|8
32|2|50|2004-01-02|8
33|2|50|2004-01-03|8
34|2|50|2004-01-04|8
- contains timeentry(HOURS) for different users(USERID) FOR different payroll batches(PID) for different dates(DATE)
the query/procedure should return data this way
Userid|PID|2004-01-01|2004-01-02|2004-01-03|2004-01-04'<== HEADER ROW
1|49|7|8|8|6
1|50|5|2|8|8
2|49|5|2|8|8
2|50|8|8|8|8
Im not sure how to start cause the dates in the headers are the start date and end date of the Payroll
Any suggestions or help is appreciated
thanks
View 1 Replies
View Related
Apr 25, 2006
Hi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
View 1 Replies
View Related
Jan 17, 2008
Can you help me in one more thing When ever I see a complex Stored procedure I feel scared.But I can see people using print command use to debug it and also some are using sql profiler to trace the database updates/changes.I am scared how can they do this so effortlessely.Actally I don't know these two technique can you please help me with example script.
Regards
sumit
View 4 Replies
View Related
Mar 17, 2008
Hello Frnds.....I want to know What are the complex Stored Procedures or Complex T-SQL Querries ?
Can Anybody give me the examples of Stored Procedures or Complex T-SQL Querries ?
How can they be written in SQL 2000or SQL 2005 ?
Waiting for reply.......
View 1 Replies
View Related
Jan 29, 2013
I've never used a stored procedure before - let alone created one. how to append records from table A to table B.
View 1 Replies
View Related
Jan 11, 2008
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.
Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.
So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.
I want to keep track of which users used a instance of a wheelchair.
No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this
UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns
The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.
If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.
Is this a good or a common practice? Why use it, or stay away from it?
Henri
~~~~
There's no place like 127.0.0.1
View 1 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Oct 11, 2007
Hi,
I am having a SP which returns two Rowsets:
create proc GetSalesDetails as
select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006'
select ProductName from Products where SalesDate > '10/10/2006'
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
Cheers,
Soumen
View 3 Replies
View Related
Mar 9, 2004
How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible?
Marius G.
View 2 Replies
View Related
Nov 15, 2007
Hi all,
this is more of a design issue for a History table.
Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.
Regards,
General Problem
View 1 Replies
View Related
Feb 8, 2007
I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.
The current solution is:
3 tables are designed for the functions mentioned above.
Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied
PS: Fields listed above are only related ones, not the complete structure.
Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.
When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.
When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.
Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.
Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?
View 5 Replies
View Related
Jun 28, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
