A Complex SELECT Stored Procedure, Pls Help

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


ADVERTISEMENT

Complex Stored Procedure...Is It Do-able?

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

Complex Stored Procedure

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

Complex Stored Procedure, Plz Help

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

Complex Stored Procedure Design - Help Me!!!

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

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

Problem Of Optimization In Complex Stored Procedure

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

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

How To Select Max And Min Value In Stored Procedure

May 25, 2008

Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly? 
DECLARE @Value1 DECIMAL(7,2)
DECLARE @Value2 DECIMAL(7,2)
SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date

View 9 Replies View Related

Stored Procedure - Select LIKE

May 4, 2006

I have the stored procedure snipet below and it does not return anything.
What am I doing wrong here?  I works ok if I need all the records for the dataset and don't use the WHERE and LIKE.
CREATE PROCEDURE dbo.search
@Field      VARCHAR (20),   @KeyWord      VARCHAR (200),  @errCode          INT                 OUTPUT
ASBEGIN
DECLARE @guid    uniqueidentifier DECLARE @subject      NVARCHAR SELECT  @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord
SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
 
Thanks,
Zath

View 9 Replies View Related

Using Stored Procedure In SELECT

Jan 12, 2001

In Informix you can use a stored procedure as part of a SELECT statement:

SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date)
FROM order_table

business_day_diff is a stored procedure that uses a custom calendar to compute
business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.

Does anyone know of a straightforward way to accomplish the same purpose?

View 2 Replies View Related

Can You Do A Select On A Stored Procedure?

Aug 12, 2007

Say I have the following stored procedure that takes in 1 parameter, and to call it I would do this: exec stored_procedure_name 'param'

And it returns a result with 3 fields: column1, column2, column3

Now I'm only interested in getting the results for column1, is there a way to call the stored procedure to only return column1?

I tried this: select column1 from (exec stored_procedure_name 'param')

It doesnt' work...

View 2 Replies View Related

Use Value From One Select Within A Stored Procedure

Dec 21, 2004

Hello!

How do you use the value of one select statement within another select statement inside the same stored proc?

Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!

I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.

Any suggestions?

View 7 Replies View Related

Using SELECT TOP In Stored Procedure

Oct 13, 2005

How could I use SELECT TOP in stored procedure
For ex I want convert this line to Stored procedure

"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"

I dont want use ROWCOUNT
because its not working good it will not show some data of some fields
for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:

this is my current code


CREATE PROCEDURE [dbo].[SP_Articles]
(
@Articles int
)
AS
SET ROWCOUNT @Articles
SELECT tblArticles.*
FROM tblArticles
WHERE published = 1 ORDER BY Adate DESC;
SET ROWCOUNT 0
GO

and I use this code to call that from ASP

strSQL = "EXECUTE SP_Articles @Articles = " & intArticles

rsArt.CursorType = 1
rsArt.Open strSQL, strCon

so just tell me how to use TOP in stored procedure
Thanks

View 9 Replies View Related

SELECT From A Stored Procedure

Jan 30, 2004

Hi everybody,
How can I select records from a SP?
For example:
My SP is the following:

CREATE PROCEDURE [dbo].[spExample]
AS
Declare @SELECT_Text AS varchar(500)
Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
exec(@SQL_Text)
GO

I want to select from it:

SELECT *
FROM dbo.spExample

It doesn't work. Then how can I use the result of the SP???

View 2 Replies View Related

Select From A Stored Procedure?

Mar 16, 2004

Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?

In other words, if I have a stored proc whose last command is:

selectt.[Region_Code],
t.[Country_Code],
sum([Total]) as [Total]
from#tmp t
group by[Region_Code],
[Country_Code]
order by[Region_Code],
[Country_Code]

Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!

View 5 Replies View Related

Stored Procedure With Many Select

Sep 19, 2013

I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.

View 4 Replies View Related

Select Into Stored Procedure

Jul 20, 2005

HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks

View 6 Replies View Related

Select Vs Stored Procedure

Jul 14, 2006

I have code simililar to the following in a stored procedure:

CREATE PROCEDURE GetGroupMembers
(
@GroupID INT
)

AS
SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID
FROM THonorsMembers LEFT OUTER JOIN
TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN
VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID
WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL
ORDER BY TMembers.MemberID

RETURN
GO


When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.

Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.

View 3 Replies View Related

Select From Stored Procedure

Jan 8, 2008



I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:

sp_MyProcedure 'Param1', 'Param2'

My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:

********************************************************************************************************
CREATE TABLE [dbo].[#tmpTable] ([RetValue] NVARCHAR(30))

INSERT INTO [#tmpTable]
EXEC sp_MyProcedure 'Param1', 'Param2'

SELECT * FROM AnotherTable

WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)

drop table [#tmpTable]
********************************************************************************************************

That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?

Thanks in advance,
JimmyR

View 7 Replies View Related

Select From Stored Procedure, Possible?

Apr 20, 2007

hi everyone,
in SQLEXPRESS if I execute

'use SqlTrackingDb exec dbo.GetName'

I can see all entries in GetName.
But I do not know if the following select statement is possible for a stored procedure in e.g. visual studio:

select * from dbo.GetName where Id = "001";

Any ideas?
Thanks in advance,
best regards bg

View 10 Replies View Related

Select + Insert Into + Stored Procedure. Please Help Me.

Oct 14, 2006

So I have simply procedure: Hmmm ... tak sobie myÅ›lÄ™ i ...

Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w
ten sposob co napisalem kilka postow wyzej (select ID as UID from
aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest
wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic    @user_id int,    @topic_katId int,    @topic_title nvarchar(256),    @post_content nvarchar(max)as    insert into [forum_topics] (topic_title, topic_userId,topic_katId)    values (@topic_title, @user_id, @topic_katId)       insert into [forum_posts] (topic_id, user_id,post_content)    values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id".  Can anyone help me ? I tried lots of ways but nothing works.

View 6 Replies View Related

Problem With Select Stored Procedure

Apr 4, 2007

i have this stored procedure: ALTER PROCEDURE dbo.SearchContact@searchCriteria nvarchar(128)AS    Select FstNam1,FstNam02 from Contacts where FstNam1 like '%'+@searchCriteria+'%'    RETURN  In my .aspx i have a SqlDataSource named SqlDataSource1 which have asocciated this stored procedure to select operation and parameter source of 'searchCriteria' is Control and ControlID  is "TextBox1". Also i have a gridview with source this sqlDataSource1 and a button . When i click this button i want to take value entered in textbox and send it to above stored procedure and show returned infos in my gridview. i put in Button1_Click(object sender, EventArgs e){ SqlDataSource1.SelectParameters["searchCriteria"].DefaultValue = Session["searchContact"].ToString();xxxxxx }  what i need to have to xxxxx to work fine ? I searched for a solution and i find a lot of posibilities, but all are fragmented.........pls give a solution ...thx for help.  P.S: Sorry for my english. 

View 15 Replies View Related

Select Stored Procedure Problem

Apr 23, 2007

Hi
 
I have a table with the columns
 
Id, Date, Downtime, FromDate, ToDate
 
How the data should look like in the table
33          2007-04-17 00:00:00.000 2  2007-04-17 15:55:52.000               2007-04-17 15:58:17.000
34           2007-04-17 00:00:00.000 68 2007-04-17 15:58:17.000             2007-04-17 17:06:27.000
35           2007-04-17 00:00:00.000 1   2007-04-17 17:06:27.000             2007-04-17 17:06:37.000
36          2007-04-18 00:00:00.000 2  2007-04-18 15:55:52.000               2007-04-18 15:58:17.000
37           2007-04-18 00:00:00.000 68 2007-04-18 15:58:17.000             2007-04-18 17:06:27.000
38           2007-04-19 00:00:00.000 1   2007-04-19 17:06:27.000             2007-04-19 17:06:37.000
 
I shall generate a report looking like this(Date column should be distinct in a way):
 
2007-04-17
2  2007-04-17 15:55:52.000 2007-04-17 15:58:17.000
68 2007-04-17 15:58:17.000            2007-04-17 17:06:27.000
0   2007-04-17 17:06:27.000            2007-04-17 17:06:37.000
2007-04-18
2  2007-04-18 15:55:52.000             2007-04-18 15:58:17.000
68 2007-04-18 15:58:17.000            2007-04-18 17:06:27.000
2007-04-19
1   2007-04-19 17:06:27.000            2007-04-19 17:06:37.000
 
This would been pretty easy in C#, but I need some help to solve this in sql ( the report takes an sql function) Does anyone know if this is possible in sql?
 
Thanks for help

View 3 Replies View Related

How To Return A Value From Stored Procedure When Select Nothing

Oct 8, 2007

Hi, nice to meet you all. I'm new developer and need help. Normally i use "sqldatasource1.select()", but need to fill argument, what is argument? Actually the main point i wan to return a value from stored procedure or got any methods can do this way? Hope you all can help me.... Thank

View 4 Replies View Related

Select From MySql DB From A MS SQL Stored Procedure

Jun 19, 2008

Hi everybody,
I'm just writing a stored procedure in my MS SQL 2005 server. I wanted to ask if there is any possibility to execute a select statement to a MySql database on another server? Does anybody have an idea?
Best regards,
ALEX

View 2 Replies View Related

Doing A SELECT X FROM Y WHERE Z IN (@param) In Stored Procedure?

Jan 21, 2005

Hello,

I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.

How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.

Any tips highly appreciated! If I'm not being clear I'll gladly post more details.

Thanks,

Noc

PS SQL 2000, ASP.NET 1.1, VS 2003.

View 2 Replies View Related

Stored Procedure - Sql Date Select

May 1, 2006

Having a little trouble getting this one to work.
I have a stored procedure that selects items from the table...
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews
I need to change it so it selects only the year needed.  For instance, only items from 2004.
SELECT fldDate, shortTitle, longTitle, Email,  POC, News, guidFROM tblNews WHERE fldDate = @myParameter
-Say myParameter = 2004 or whatever year is needed
The table field fldDate is a datatime field that has date and time stored and I can put any parameter into the stored procedure.
How can I be sure to only get a certain year?
Thanks,
Zath

View 2 Replies View Related

Calling A Stored Procedure Within A Select

Apr 30, 2001

Is it possible to call a stored procedure inside a select statement: I tried this , but is giving error "Incorrect syntax near the keyword 'execute'."

"
SELECT PC.ProductId
FROM ProductCategories PC
WHERE PC.CategoryID in ( execute get_category_hierarchies 1 )
"

where get_category_hierarchies is the stored procedure which expects an integer parameter.

View 2 Replies View Related

If Statement In Select Of Stored Procedure

Dec 19, 2000

Hi,

Can you use an IF statement in a Select statement within a stored procedure?

If so, how?

Mark

View 1 Replies View Related

Need To Run Stored Procedure In Select Statement

Aug 21, 2000

I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

View 4 Replies View Related

Stored Procedure Select Top &<@Variable&>

Feb 3, 2006

when passing a value to a stored procedure

How can you use that value within a Select Top <@Variable>
?

Below is the basic idea ..

CREATE Procedure SelectTop
(
@Number int
)

AS


SELECT TOP @Number *
FROM TableName


GO

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved