I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
select distinct d.id, d.vwr, d.reqType from tblVWR AS d
inner join tblVWR t
on d.vwr = t.vwr
Any suggestions?
Thanks,
Zath
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
The structure of the periods table is as follows:
[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL
The stored procedure is currently:
USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
View 14 Replies View RelatedHi again,
I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber,
dateTimeConnect,
dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1
LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE
(t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND
(t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND
(t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND
(t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND
(@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND
(t1.duration >= @theDuration) AND
((t1.datetimeConnect) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND
((t1.dateTimeDisconnect) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect))
The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value.
What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number).
If this is not clear, please let me know.
Thanks,
Bullpit
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select distinct''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'
EXEC (@sql)
set @RowId = @RowId + 1
END
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.
Hi All,
I'm struggling with this one.
We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table.
We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.
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.
Hello,
I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.
For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:
IF @QUERY_ID = 1
BEGIN
SELECT [whatever]
FROM [tbl1]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
ELSE
IF @QUERY_ID = 2
BEGIN
SELECT [whatever]
FROM [tbl2]
WHERE [conditions]
GROUP BY [something]
ORDER BY [somethingelse]
END
END
I hope that makes sense. Thanks in advance.
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
Will you please look at my stored procedure below? I originally had table "Servee" as Parent table and "Orders" talbe as child table, using relationships in VWD express. I am querying sql server 2005 express. When I run the page, I got an error saying a conflict relationship Orders_FK-Servee. So I deleted the table relationship and ran the page, but the procdure didn't do anything, but no error.
What I'm trying to do, is Insert a new record if the current (meaning displayed on the page) ServeeID is not present in the Orders table. If the current ServeeID is present, UPDATE the current record. Thanks in advance.
ALTER PROCEDURE dbo.newjob
@ServeeName varchar(500),@Identity int OUT
AS
IF NOT EXISTS(SELECT * FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID)
BEGININSERT Servee (ServeeName)
VALUES (@ServeeName)
END
ELSE
BEGIN
UPDATE Servee SET Servee.ServeeName=Servee.ServeeName FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID
ENDSET @Identity = SCOPE_IDENTITY()
Return
hi gurushave a employee table which consist of the normal employee relatedfields which the fields empid and supervisor mainly related to thisquestion.have to create a single (the question is whether it is possible) or astored procedure to get the result as follow. the input parameter isempid.have to retrieve the subordinates records of the employee id passed asparameter and the drill down.for example from the below table. if empid 12 is been passed as theparameter value then it should retrieve the records with followingempid - 1, 26. the employee with empid 1 has subordinates under himwhich should also be pulled that is 16, 62 and so on. the output forthe above example will be a result with records in the following order- 12, 1,16,62, 26 and so on.hope i have explained the problem clearlywould appreciate if some one can guide in creating an efficient selfjoin or a stored procedureEmpId FirstName LastName Supervisor----- --------- -------- ----------1 Carl Hogans 1212 Fred Smith NULL16 Sue Bankers 126 Frank Green 1255 Karen Feeders NULL56 James Black 5557 Kirk Simmons 5658 Cliff Page 5659 Jimmy Plant 5660 Jack Cale 5961 Robert Santana NULL62 Jack Russell 1where clause or parameter passed is 'EmpId'thanx in advancebala
View 4 Replies View RelatedI'm very new to SQL and have been assigned to modify an existing Store Procedure. The existing is pasted below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE RPT_ACTIVITY_LEDGER
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS
SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4'
FROM ACTIVITIES
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*************
The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.
I was just curious if there was anyway possible to accomplish something simliar to:
SELECT * FROM [dbo].[PRODUCT]
JOIN [dbo].[GetRegionProductPrice](@RegionClassID,[dbo].[PRODUCT].[ID]) Pricing ON Pricing.[ProductID] = [Product].[ID]
WHERE [CategoryID] = @ProductNavigationID
Basically what i am trying to do is Select all my products and obtain a product price for reach, which uses a complex stored procedure that obtains a price for a single product. I understand that i could create a cursor to go through it, but i was just curious if there was a better method... Thanks for your time..
-Devin
Hi everybody
View 3 Replies View Related The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?
I am getting the below error
Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53
Ambiguous column name 'REINS_TYPE_CD'.
below is the query i changed.
ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION]
@RECON_TRNSCTN_ID int=NULL,
@RECON_ITEM_ID int=NULL,
@DIVISION_ID int=NULL,
@REINS_TYPE_CD int=NULL,
@TRANSACTION_NO char(10)=NULL,
@TRANSACTION_NAME varchar(100)=NULL,
@REF_NUMBER varchar(20)=NULL,
@POLICY_CRS_REF_NO varchar(20)=NULL,
@GL_DTFrom datetime=NULL,
@GL_DTTo datetime=NULL,
@TRANSACTION_CD int=NULL,
@AMOUNT money=NULL,
@FILE_STATUS_CD int=NULL,
@TRNSCTN_OWR_CD int=NULL,
@ISSUE_CD int=NULL,
@SUPP_ISSUE_CD int=NULL,
@IRC_CLASSIFICATION_CD int=NULL,
@UNDER_90_DAYS money=NULL,
@OVER_90_DAYS money=NULL,
@AGING_DAYS_CNT int=NULL,
@VOCHER_NO varchar(50)=NULL,
@LOADED_DTFrom datetime=NULL,
@LOADED_DTTo datetime=NULL,
@SPUsageMode TINYINT = 0 -- This should be the last parameter
AS
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- All Stored Procedure code MUST be placed between Section 1 and 2 of
-- SP Usage Audit code
-------------------------------------------------------------------------------
-- SP Usaged Section 1 - Declare
-------------------------------------------------------------------------------
DECLARE @SPStartTime DATETIME
SELECT @SPStartTime = GETDATE()
DECLARE @SPEndTime DATETIME
DECLARE @AuditCount INT
-------------------------------------------------------------------------------
SELECT @GL_DTFROM = ISNULL(@GL_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @GL_DTTO = ISNULL(@GL_DTTO, CONVERT(DATETIME,'12/31/9999'))
SELECT @LOADED_DTFROM = ISNULL(@LOADED_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @LOADED_DTTO = ISNULL(@LOADED_DTTO, CONVERT(DATETIME,'12/31/9999'))
Begin
SELECT
RECON_TRNSCTN_ID,
RECON_ITEM_ID,
DIVISION_ID,
REINS_TYPE_CD,
TRANSACTION_NO,
TRANSACTION_NAME,
REF_NUMBER,
POLICY_CRS_REF_NO,
GL_DT,
TRANSACTION_CD,
AMOUNT,
FILE_STATUS_CD,
TRNSCTN_OWR_CD,
ISSUE_CD,
SUPP_ISSUE_CD,
IRC_CLASSIFICATION_CD,
UNDER_90_DAYS,
OVER_90_DAYS,
AGING_DAYS_CNT,
VOCHER_NO,
LOADED_DT,
REI.REINS_TYPE_DS
FROM AIGNET.dbo.RECON_TRANSACTION AS RE
INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD
WHERE
(@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID)
AND
(@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID)
AND
(@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID)
AND
(@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD)
AND
(@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO)
AND
(@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME)
AND
(@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER)
AND
(@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO)
AND
((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo))
AND
(@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD)
AND
(@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT)
AND
(@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD)
AND
(@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD)
AND
(@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD)
AND
(@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD)
AND
(@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD)
AND
(@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS)
AND
(@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS)
AND
(@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT)
AND
(@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO)
AND
((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))
End
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- SP Usage Section 2 - INSERT Audit Info
-------------------------------------------------------------------------------
SELECT @AuditCount = @SPUsageMode +
(SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK)
WHERE Parameter = 'SPUsageByPass')
IF @AuditCount < 1
Begin
SELECT @SPEndTime = GETDATE()
INSERT DBPerfMon.dbo.SPUsage (
DatabaseName
, Duration
, ObjectID
, ObjectName
, UserName
)
SELECT
DB_NAME()
, DATEDIFF(ms, @SPStartTime, @SPEndTime)
, OBJECT_ID(OBJECT_NAME(@@PROCID))
, OBJECT_NAME(@@PROCID)
, dbo.fncGetLastUpdatedBy ()
End
-------------------------------------------------------------------------------
-- Absolutely NO Stored Procedure code written beyond this point
-------------------------------------------------------------------------------
Hi all,can somebody help to write this stored procedure Table1 Table2LogID MigIDUserMove LogIDUserNew Domain User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks
View 1 Replies View RelatedHi,
it is possible to execute a stored procedures inside of a inner join of another stored procedure?
I have the problem, that I have a stored procedure which will return a resultset. This resultset should be used in a join of another stored procedure. It is possible or not?
I tried something like that, but it doesn't work for me:
CREATE PROCEDURE usp_Person
AS
SELECT
A.LastName,
A.FirstName,
B.Street
FROM
Table1 A
INNER JOIN
EXEC usp_Address B
ON
A.Id = B.Id
GO
I am thankful for a hint or a better way.
I am a newbie to SQL and I am trying to create a stored procedure to use with Crystal Reports.
I don't understand why I am having trouble joining tables to the Member table. These table joins have worked in the past.
Create Proc rpmb_FamilyPortraitDirectoryP1Test
(
@ChurchId int,
@StartFamilyName varchar(50),
@EndFamilyName varchar(50),
@MemberTypeId varchar(max) = Null,
[Code] ....
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I have a view which joins 2 tables together in a left outer join.
Now, the left outer join table (lets call this SalesData) may or may not have records. As months and years go by, records will be inserted or updated.
I am trying to find a query which will do the following:
Bring back all records where the branch matches an input value and also:
- bring back NULL records for the YR and MO column
- bring back values which match the input params for YR and MO column.
this works as expected:
quote:
SELECT wmtecp.*
FROM v_stores wmtecp
RIGHT OUTER JOIN v_Stores roj ON wmtecp.ID = roj.ID
WHERE wmtecp.TM = @p1 AND 1=1 AND roj.YR IS NULL AND roj.MO IS NULL OR wmtecp.MO = 11 AND wmtecp.YR = 2014
This is right outer joining the view to itself and prefer it this way.
Here is the thing: I want to bring back only distinct records (distinct by store ID I guess).
We are bringing back result set which contains NULL and NON null values but for both result sets the same storeID is returned, I do not want this but to only bring back the same record once either WITH values or WITHOUT values.
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id
ticket_to
ticket_from
message
message_id
ticket_id
message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message
FROM tickets
INNER JOIN message ON tickets.ticket_id = message.ticket_id
GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
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
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
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?
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...
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?
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
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???
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!
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 RelatedHelloI 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 RelatedI 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.