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
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
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..
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 dinner join tblVWR ton d.vwr = t.vwr Any suggestions? Thanks, Zath
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 -------------------------------------------------------------------------------
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')
------------------------------------------------------------------------------- -- 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
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
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).
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.
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.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
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)
'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.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
IF (@i_WildCardFlag=0)BEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END PRINT @SQLString SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)' EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric. REQUIREMENT: The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks
IF @@ERROR = 0 BEGIN -- Success. Commit the transaction. Commit Tran END ELSE Rollback Tran
return GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the "SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
When I am trying to call a function I made from a stored procedure of my creation as well I am getting:
Running [dbo].[DeleteSetByTime].
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.TTLValue", or the name is ambiguous.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[DeleteSetByTime].
This is my function:
ALTER FUNCTION dbo.TTLValue
(
)
RETURNS TABLE
AS
RETURN SELECT Settings.TTL FROM Settings WHERE Enabled='true'
This is my stored procedure:
ALTER PROCEDURE dbo.DeleteSetByTime
AS
BEGIN
SET NOCOUNT ON
DECLARE @TTL int
SET @TTL = dbo.TTLValue()
DELETE FROM SetValues WHERE CreatedTime > dateadd(minute, @TTL, CreatedTime)
END
CreatedTime is a datetime column and TTL is an integer column.
I tried calling it by dbo.TTLValue(), dbo.MyDatabase.TTLValue(), [dbo].[MyDatabase].[TTLValue]() and TTLValue(). The last returned an error when saving it "'TTLValue' is not a recognized built-in function name". Can anybody tell me how to call this function from my stored procedure? Also, if anybody knows of a good book or site with tutorials on how to become a pro in T-SQL I will appreciate it.
Hello, I am hoping there is a solution within SQL that work for this instead of making round trips from the front end. I have a stored procedure that is called from the front-end(USP_DistinctBalancePoolByCompanyCurrency) that accepts two parameters and returns one column of data possibly several rows. I have a second stored procedure(USP_BalanceDateByAccountPool) that accepts the previous stored procedures return values. What I would like to do is call the first stored procedure from the front end and return the results from the second stored procedure. Since it's likely I will have more than row of data, can I loop the second passing each value returned from the first? The Stored Procs are: CREATE PROCEDURE USP_DistinctBalancePoolByCompanyCurrency @CID int, @CY char(3) AS SELECT Distinct S.BalancePoolName FROM SiteRef S INNER JOIN Account A ON A.PoolId=S.ID Inner JOIN AccountBalance AB ON A.Id = AB.AccountId Inner JOIN AccountPool AP On AP.Id=A.PoolId Where A.CompanyId=@CID And AB.Currency=@CY
CREATE PROCEDURE USP_BalanceDateByAccountPool @PoolName varchar(50) AS Declare @DT datetime Select @DT= (Select MAX(AccountBalance.DateX) From Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName) SELECT SiteRef.BalancePoolName, AccountBalance.DateX, AccountBalance.Balance FROM Company Company INNER JOIN Account Account ON Company.Id = Account.CompanyId INNER JOIN SiteRef SiteRef ON Account.PoolId = SiteRef.ID Inner JOIN AccountBalance AccountBalance ON Account.Id = AccountBalance.AccountId WHERE SiteRef.BalancePoolName = @PoolName And AccountBalance.DateX = @DT Order By AccountBalance.DateX DESC
Any assistance would be greatly appreciated. Thank you, Dave
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory] @CatId <span class="kwd">int</span> = 0 AS BEGIN SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc End
