Stored Procedure Producing Duplicate Records
Feb 11, 2004
Hi,
I have written the following stored procedure:
alter proc GetProducts
@prodcatint=null
as
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
Does anybody have any suggestions.
Thank you in advance,
poc1010
View 4 Replies
ADVERTISEMENT
Feb 1, 2005
I have a web app that calculates tax filing status and then stores data about the person.
Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()
And that is it.
View 6 Replies
View Related
Aug 29, 2005
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen. So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info. Any idea y? They both should be bringing back information from the same table not one from my live DB and one from my TEST DB. Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows: CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement: SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid
View 2 Replies
View Related
Jun 21, 2001
hello friends
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0
i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only
*** similarly when it checks the 100th record it compares it for 99 times
*** similarly when it checks the 10000th record it compares it for 9999 times
so here lies the problem....
when it checks the 100000th record it gets dammm slow...
what i have get till now is that i have checked.....>>>>>
5000 records in 4 mins....
25000 records in 22 mins....
and
100000 records in 20 hours....(means initially its faster but it gradually slows down)
************************************************** ************************
here i m giving the code for the procedure......
************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON
Declare @IvgId as numeric(15)
Declare @Address as nvarchar(250)
Declare @AddressClean as nvarchar(250)
Declare @MaxLen as INT
Declare @Add as nvarchar(250)
Declare @Ic as int
Declare @FoundIvgId as numeric(15)
Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0
Begin
/*here i m doing string cleaning by removing special characcters */
Select @MaxLen = len(LTRIM(RTRIM(@Address)))
Select @Address = LOWER(@Address)
Select @Ic = 1
Select @AddressClean = ' '
While @Ic <= @MaxLen
/* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */
Begin
Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
If ascii(@Add) > 90 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
Select @Ic = @Ic + 1
End
/* now we have removed special characters , for failure checking i m using this 'Y' */
Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y'
Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address
End
PRINT 'Cleaning Done.............................'
Close CurData
Deallocate CurData
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0
Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20))
Begin
Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)
End
ELSE
Begin
Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0)
Select @NewIvgId = @NewIvgId + 1
End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
End
Close CurData
Deallocate CurData
SET NOCOUNT OFF
Print 'Done................................'
************************************************** **************************
now the procedure is over....now i m writing the SQL script of DATA & RESULT table
************************************************** ************************
CREATE TABLE [dbo].[DATA] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Title] [varchar] (10) NULL ,
[FirstName] [varchar] (50) NULL ,
[MiddleName] [varchar] (10) NULL ,
[LastName] [varchar] (30) NULL ,
[Add1] [varchar] (150) NULL ,
[Add2] [varchar] (50) NULL ,
[Add3] [varchar] (50) NULL ,
[City] [varchar] (30) NULL ,
[State] [varchar] (30) NULL ,
[Country] [varchar] (20) NULL ,
[Pincode] [varchar] (10) NULL ,
[OffPhone] [varchar] (20) NULL ,
[OffFax] [varchar] (20) NULL ,
[ResPhone] [varchar] (20) NULL ,
[ResFax] [varchar] (20) NULL ,
[EMail] [varchar] (50) NULL ,
[Source] [varchar] (20) NULL ,
[MODEL] [varchar] (20) NULL ,
[PNCD] [varchar] (6) NULL ,
[DupKey] [decimal](18, 0) NULL ,
[Duplicate] [int] NULL ,
[HouseHoldID] [varchar] (50) NULL ,
[YearSlab] [varchar] (10) NULL ,
[CleanStatus] [int] NULL ,
[AddStatus] [int] NULL ,
[BatchNo] [varchar] (20) NULL ,
[ModelStatus] [int] NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[SapStatus] [int] NULL ,
[ErrCase] [int] NULL ,
[cmpCity] [varchar] (50) NULL ,
[Product] [varchar] (1) NULL ,
[cmpPinCode] [varchar] (6) NULL ,
[Address] [nvarchar] (250) NULL ,
[AddressClean] [nvarchar] (250) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/
CREATE TABLE [dbo].[DATA_TEST] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Address] [nvarchar] (50) NULL ,
[AddressClean] [nvarchar] (50) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
so now i have given the whole description of my problem....i m eagerly waiting for reply......
if anybody can help....i will be very thankful.....
bye for now
Bhupinder singh
View 1 Replies
View Related
Oct 11, 2004
I am going crazy. I have a typical date field in tbl_Feedback(smallDateTime) that is storing just the date portion. I have a stored proc that I want to dynamically retrieve records within a certain date range (typical!). I am using ASP/ADO, etc. When I run the following Stored Proc in SQL Analyzer, it works (if I replace the variable "@sDate" with '10/01/2004' --There ARE records within this date range.) Further more, If I run a simple Query on the Feedback table and do the same, it produces results, but when I call the stored Proc from the web page, I get 0 results when attempting to PASS date values dynamically. I tried to pass them as actual date types, still 0. I'm now attempting to pass it as a 10 length string ("10/01/2004") and converting it to a date, still 0. What am I MISSING???
-- Procedure
CREATE PROCEDURE sp_009_SiteLead
@sDatenVarChar(10)
AS
--DECLARE @CurDate datetime
DECLARE @StartMonth int
DECLARE @StartYear int
DECLARE @StartDay int
DECLARE @StartDate varchar(10)
SET @StartMonth = DATEPART( month, Convert ( datetime, @sDate ))
SET @StartYear = DATEPART( year, Convert ( datetime, @sDate ))
SET @StartDay = DATEPART( day, Convert ( datetime, @sDate ))
SET @startDate = CONVERT( varchar(2), @StartMonth ) + "/" + CONVERT( varchar(2), @StartDay ) + "/" + CONVERT( varchar(4), @StartYear )
Print @startDate
SELECT *
FROMtbl_Feedback
WHERE tbl_Feedback.DateIn >= @startDate
GO
--- ADO Web page
' Stored Procedure Name: sp_009_SiteLead
' Site Lead Default Query
set cmd=server.CreateObject("ADODB.command")
set psDate = cmd.CreateParameter("sDate", adDBTimeStamp, adParamInput)
set cmd.ActiveConnection=DBConnection
cmd.CommandText="sp_009_SiteLead"
cmd.Parameters.Append peDate
cmd.Parameters.Append psdate
psDate.Value = "10/01/2004"
Set rsObj = CreateObject("ADODB.Recordset")
rsObj.ActiveConnection = DBConnection
rsObj.CursorLocation = adUseClient
rsObj.PageSize= 20
rsObj.Open cmd
Again, If I replace the @startDate variable in the stored proc with a date surrounded in single quotes, I get the expected results, but it just does not understand my date if I pass it.
Can anyone help?
Thanks,
Chad
View 5 Replies
View Related
Feb 4, 2008
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String
'Get the users IP address
strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress
Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try
End Sub
View 7 Replies
View Related
Apr 17, 2008
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance
Pmillio Jones
Pmillio Jones
Pmillio Jones
Robert Walsh
Here is my stored procedure;
ALTER PROCEDURE [dbo].[stream_UserFind]
-- Add the parameters for the stored procedure here
@userName varchar(100),
@subCategoryID INT,@regionID INT
AS
SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON
Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR
SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
View 11 Replies
View Related
Oct 22, 2007
hi every body
can any onehelp me in making a stored procedure to delete a duplicate rows in tables
thanks in advance
View 9 Replies
View Related
Jun 2, 2008
How can I use the data returned from a stored procedure within another stored procedure?
For example, I trying to something along these lines:
select * from tbl_Test
union all
select * from (exec sp_Test)
View 2 Replies
View Related
Mar 23, 2006
I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...
here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"
===
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans
cmd.CommandText = "usp_InserOrder"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.Add("@MaterialID", Data.SqlDbType.Int)
cmd.Parameters.Add("@OpenItem", Data.SqlDbType.Bit)
cmd.Parameters("@MaterialID").Value = 3
cmd.ExecuteNonQuery()
trans.Commit()
=====
I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.
Please help, I am a newb and lost...as you can tell from my code...
View 4 Replies
View Related
Nov 14, 2000
What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?
Thanks ahead of time!
G.
View 3 Replies
View Related
Jul 14, 2004
I am trying to count records in my stored procedure. Can someone please help me.
these are the two procedures I am using
Alter Procedure usp_rptQualityReport As
SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))
ORDER BY
tblFailureReasons.FailureReason
Alter Procedure usp_rptQualityReport2 As
exec usp_rtpQualityReport
SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,
(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel
FROM usp_rptQualityReport;
View 3 Replies
View Related
Sep 20, 2014
Creating a stored procedure to insert records into multiple rows.
Let's say we have 3 colums in procedure to insert, col3 has a max limit of 1000, if col3 data exceeds limit it has insert into next row and vice versa.
View 1 Replies
View Related
Oct 11, 2007
Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.
View 6 Replies
View Related
Mar 27, 2008
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
TaskId PoId Podate Approved
1 2 2008-07-07 No
3 4 2007-05-05 No
5 5 2005-08-06 Yes
2 6 2006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and i have the column TaskId as the common column
Pls provide me with full stored procedure code.
C.R.P RAJAN
View 1 Replies
View Related
Dec 1, 2006
Hi All,
I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.
EXEC [dbo].[ETLloadGROUPS]
@countRun =?,
@startTime =?,
@endTime = ?
This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.
When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.
Am I missing something here ? Please Advice
Thanks in Advance
View 9 Replies
View Related
Mar 27, 2008
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values-------------------------------------------TaskId PoId Podate Approved
1 2 2008-07-07 No 3 4 2007-05-05 No 5 5 2005-08-06 Yes 2 6 2006-07-07 Yes
Table B contains the following columns and values-------------------------------------------------TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C. In both the tables A and B i have the TaskId as the common column
Pls provide me with full stored procedure code.
View 2 Replies
View Related
Apr 18, 2006
I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made:
CREATE PROCEDURE dbo.GetUserStats @UserID BIGINT AS
DECLARE @TempTable TABLE
(
UserID BIGINT,
UserName VARCHAR(60),
TotalAmt FLOAT
)
INSERT INTO @TempTable (UserID, UserName, TotalAmt)
SELECT u.RecID, u.LastName + ', ' + u.FirstName,
(SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @UserID)
FROM Users u
WHERE u.RecID = @UserID
SELECT * FROM @TempTable
GO
So if I execute this amount entering a single ID, it returns a single row for that user:
UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00
What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users:
UserID UserName TotalAmt
--------------------------
1 Doe, John 100.00
2 Smith, Bob 123.45
3 Blow, Joe 150.55
Is there a way to re-use a stored procedure within another, based on the results of a query?
View 7 Replies
View Related
Oct 6, 2015
I am trying to put together a Stored Procedure that runs other SP's based on a records found condition. Sometimes my update table either by deleting duplicates or for other reasons is empty. Running a SP to update records on a table is not needed.
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT1')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT1_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'CT2')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_CT2_Trace_Data
END
IF EXISTS (SELECT TOP 1 * FROM Work_Tables.dbo.Trace_Return_Updates WHERE TR_RecType = 'FULF')
BEGIN
EXEC IMB_Trace_Data.dbo.Update_FULF_Trace_Data
END
View 1 Replies
View Related
Feb 26, 2006
Hi, I would like to delete some records in a table older than a specified date using a stored procedure. Can anyone help?
Thank you,
Cynthia
View 5 Replies
View Related
Jul 20, 2005
I have a stored procedure named mySP that looks basically like this:Select Field1, Field2From tblMyTableWhere Field 3 = 'xyz'What I do is to populate an Access form:DoCmd.Openform "frmMyFormName"Forms!myFormName.RecordSource = "mySP"What I want to do in VBA is to open frmContinuous(a datasheet form) ifmySP returns more than one record or open frmDetail if mySP returnsonly one record.I'm stumped as to how to accomplish this, without running mySP twice:once to count it and once to use it as a recordsource.Thanks,lq
View 1 Replies
View Related
Oct 24, 2006
I am running a SP using this code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly]
(
@postcode nvarchar(50)
)
AS
SET NOCOUNT ON;
SELECT
Cust_Address.Cust_Address_ID,
Cust_Address.Cust_Address_1,
Cust_Address.Cust_Address_2,
Cust_Address.Cust_Address_Post_Code,
Cust_Address.Cust_Post_Town,
Post_Town.Post_Town_ID,
Post_Town.Post_Town_Name,
Post_Town.Post_Town_Priority
FROM Cust_Address
INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID
WHERE Cust_Address.Cust_Post_Town LIKE @postcode
The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all.
Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine.
Cheers Matt
View 1 Replies
View Related
Oct 9, 2007
I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.
I use the logic in other sprocs too.
Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....
INSERT INTO Insurance (Name)
Exec sp_AddNamesList
Thanks,
Mike
View 5 Replies
View Related
Jul 28, 2007
I am using SQL Server 2005 std edition SP2 on a Windows 2003 server. I have created a simple stored procedure that deletes all records from two tables:
BEGIN
SET NOCOUNT ON
DELETE FROM dbo.table1
DELETE FROM dbo.table2
END
Executing the procedure generates the message "The stored procedure executed successfully but did not return records." which produces an error condition when run from an Access 2007 VB module using the DoCMD function:
On Error GoTo ErrorExit
DoCmd.SetWarnings False
DoCmd.OpenStoredProcedure "dbo.myStoredProcedure"
When the above VB code is run (it's part of an Access 2007 adp project connected to the SQL Server database) it takes the error exit and returns the "... did not return records." message. How can I avoid this??
Thanks,
Paul
View 10 Replies
View Related
Aug 17, 2007
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
View 2 Replies
View Related
Dec 9, 2005
Hi, and thanks in advance.
I have VWD 2005 Express and SQL 2005 Express running.
I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting...
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded.
Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle.
Any help would be appreciated!
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString %>"
ProviderName="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString.ProviderName %>"
SelectCommand="spDisplayServiceOrders" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" EnableCaching="True" cacheduration="300" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="RadioButtonList1" ConvertEmptyStringToNull="False"
DefaultValue="2005-11-1" Name="SDate_Entered" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ALTER PROCEDURE [dbo].[spDisplayServiceOrders]
(
@SDate_Entered SmallDateTime,
@SClosed nvarchar(50)= NULL
)
AS
If @SClosed IS NULL
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
Order by SDate_Entered DESC
END
ELSE
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
and SClosed = @SClosed
Order by SDate_Entered DESC
END
View 2 Replies
View Related
Jan 6, 2006
Hi everyone,
I have created a stored procedure in sql server with parameters for my c# application. Wanted to know is there anyway to set the default value for @searchpostcode to select all the records?
Right now it brings the records based on the postcode specified .(I have dropdownlist in my c# application that passes the parameters for postcode)
My stored procedure:
CREATE PROCEDURE sp_accepting_practice (@searchpostcode as nvarchar(100)) AS
SELECT dbo.tbdentists.Title, dbo.tbdentists.FirstName, dbo.tbdentists.Surname, dbo.tbpractices.PracticeName, dbo.tbpractices.PracticeAddress1, dbo.tbpractices.PracticeAddress2, dbo.tbpractices.Town, dbo.tbpractices.Postcode, dbo.tbpractices.Phone, dbo.tbdentistspractices.ListNo, dbo.tbtreatment.treatmentNatureFROM dbo.tbdentists INNER JOIN dbo.tbdentistspractices ON dbo.tbdentists.DentistId = dbo.tbdentistspractices.DentistId INNER JOIN dbo.tbpractices ON dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId AND dbo.tbdentistspractices.PracticeId = dbo.tbpractices.PracticeId INNER JOIN dbo.tbtreatment ON dbo.tbdentistspractices.TreatmentId = dbo.tbtreatment.treatmentIdWHERE dbo.tbpractices.Postcode LIKE '%' + @searchpostcode + '%'ORDER BY dbo.tbpractices.PracticeId
EXECUTE sp_accepting_practice G4GO
I greatly appreciate your help. Thanks in Advance
Regards
Shini
View 9 Replies
View Related
Jul 20, 2005
I have a table tblCustomers in a one-to-many relationship with tabletblProducts.What I want to do is to create a stored procudure that returns a listof each customer in tblCustomers but also creates a field showing astring (separated by commas)of each matching record in tblProducts.So the return would look like:CustID Customer ProductList1 Smith Apples, Oranges, Pears2 Jones Pencils, Pens, Paperetc...Instead of:CustID Customer Product1 Smith Apples1 Smith Oranges1 Smith Pears2 Jones Pencils2 Jones Pens2 Jones PaperWhich is what you get with this:SELECT tblCusomers.CustID, tblCusomers.Customer,tblProducts.ProductFROMtblCusomers INNER JOINtblProducts ONtblCustomers.CustID = tblProducts.CustIDI'd appreciate any help!lq
View 6 Replies
View Related
Oct 6, 2007
i have a stored procedure with one coming id parameter
Code BlockALTER PROCEDURE [dbo].[sp_1]
@session_id int
...
and a view that holds these @session_id s to be sent to the stored procedure.
how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.
View 1 Replies
View Related
Oct 21, 2015
I am looking out for sample  stored procedures returning multiple records
Example: GetOrderDetailsByOrderId
The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.
View 4 Replies
View Related
Dec 5, 2007
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo
ProjectNo
Category
Type
Count
Budget
BudgetTotal
100143
Travel
Travel, Meals, No Report IRS
2
0
300.27
100146
Travel
Travel Costs, Training (all)
1
0
300.27
100164
Supplies & Materials
Supplies, Educational
1
0
300.27
100167
Equipment
Eq NonCapital Desktop Comp
1
0
300.27
100170
Faculty Salaries
FB, Faculty
11
0
300.27
100170
Faculty Salaries
Salary, Faculty, T&R FT
11
0
300.27
100170
Wages
Wages, Student
2
0
300.27
100171
Faculty Salaries
FB, Faculty
19
0
300.27
100171
Faculty Salaries
Salary, Faculty, T&R FT
19
0
300.27
100176
Scholarships & Fellowships
Fell, Assist, Out, Grad
1
0
300.27
100177
Scholarships & Fellowships
Fell, Assist, In, Grad
1
0
300.27
View 5 Replies
View Related
Mar 19, 2008
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?
Thanks, Gary
View 9 Replies
View Related
Mar 25, 2008
Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
Thanx in Advance
View 4 Replies
View Related
