Only Run Stored Procedure If Update Records Exist?
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
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
here is my original Sql Code I add a parameters @CustId in line 13 when CustID not Null, I want to add this to the Where clause line 146 and 156 if CustId not null then where clause will add a reference like CustId = @CustIDif CustId is Null then not reference @custID how to Add a if ~else clause to my code? i tried all day.. but it doesn't work.. 1 SET QUOTED_IDENTIFIER OFF 2 GO 3 SET ANSI_NULLS OFF 4 GO 5 6 7 8 ALTER PROCEDURE [dbo].[usp_OutDataDownQuery] 9 @DTBEG DATETIME, 10 @DTEND DATETIME, 11 @Remark INT, 12 @BankId VARCHAR (128) 13 @CustID CHAR 14 15 16 as 17 18 19 20 SELECT 21 * 22 FROM 23 ( 24 SELECT 25 ZT_Master.PriKey, 26 ZT_Master.BankId, 27 ZT_Master.TDateTime, 28 ZT_Master.PNo, 29 ZT_Master.Remark, 30 ZT_Master.CustId, 31 ZT_Master.ProcStatus, 32 ZT_Customer.[Name], 33 --ZT_Customer.AccountBAK AS Account, 34 --ZT_Customer.SCAccountBAK AS SCAccount 35 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SD') AS Account, 36 (SELECT TOP 1 SUBSTRING(PCLNO, 3, 12) FROM ZT_Detail WHERE ZT_Master.PriKey = ZT_Detail.MasterKey AND ZT_Detail.TXTYPE = 'SC') AS SCAccount 37 FROM 38 ZT_Master LEFT JOIN ZT_Customer ON ZT_Master.CustId=ZT_Customer.Id 39 ) a 40 ,------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 41 (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 42 CtcbBanSDTotal=ISNULL(本行代收ç†æ•¸,0), 43 CtcbBanSDTotalAMT=ISNULL(本行代收金é¡?,0), 44 OtherBanSDTotal=ISNULL(他行代收ç†æ•¸,0), 45 OtherBanSDTotalAMT=ISNULL(他行代收金é¡?,0), 46 CtcbBanSCTotal=ISNULL(本行代付ç†æ•¸,0), 47 CtcbBanSCTotalAMT=ISNULL(本行代付金é¡?,0), 48 OtherBanSCTotal=ISNULL(他行代付ç†æ•¸,0), 49 OtherBanSCTotalAMT=ISNULL(他行代付金é¡?,0), 50 GoodSDTotal=ISNULL(代收æˆ?功ç†æ•¸,0), 51 GoodSDTotalAMT=ISNULL(代收æˆ?功金é¡?,0), 52 GoodSCTotal=ISNULL(代付æˆ?功ç†æ•¸,0), 53 GoodSCTotalAMT=ISNULL(代付æˆ?功金é¡?,0), 54 BadSDTotal=ISNULL(代收失敗ç†æ•¸,0), 55 BadSDTotalAMT=ISNULL(代收失敗金é¡?,0), 56 BadSCTotal=ISNULL(代付失敗ç†æ•¸,0), 57 BadSCTotalAMT=ISNULL(代付失敗金é¡?,0) 58 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 59 本行代收ç†æ•¸, 60 本行代收金é¡?, 61 他行代收ç†æ•¸, 62 他行代收金é¡?, 63 本行代付ç†æ•¸, 64 本行代付金é¡?, 65 他行代付ç†æ•¸, 66 他行代付金é¡?, 67 代收æˆ?功ç†æ•¸, 68 代收æˆ?功金é¡?, 69 代付æˆ?功ç†æ•¸, 70 代付æˆ?功金é¡? 71 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 72 本行代收ç†æ•¸, 73 本行代收金é¡?, 74 他行代收ç†æ•¸, 75 他行代收金é¡?, 76 本行代付ç†æ•¸, 77 本行代付金é¡?, 78 他行代付ç†æ•¸, 79 他行代付金é¡? 80 -------------------------------------------------------------------代收æˆ?功ç†æ•¸èˆ‡ä»£æ”¶æˆ?功金é¡?-------------------------------------------------------------- 81 FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 82 本行代收ç†æ•¸, 83 本行代收金é¡?, 84 他行代收ç†æ•¸, 85 他行代收金é¡? 86 FROM (SELECT MasterKey, 87 COUNT(PriKey) AS 本行代收ç†æ•¸, 88 SUM(AMT) AS 本行代收金é¡? 89 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a 90 FULL JOIN (SELECT MasterKey, 91 COUNT(PriKey) AS 他行代收ç†æ•¸, 92 SUM(AMT) AS 他行代收金é¡? 93 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b 94 ON a.MasterKey=b.MasterKey) a 95 -------------------------------------------------------------代付æˆ?功ç†æ•¸èˆ‡ä»£ä»˜æˆ?功金é¡?--------------------------------------------------------------- 96 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 97 本行代付ç†æ•¸, 98 本行代付金é¡?, 99 他行代付ç†æ•¸, 100 他行代付金é¡? 101 FROM (SELECT MasterKey, 102 COUNT(PriKey) AS 本行代付ç†æ•¸, 103 SUM(AMT) AS 本行代付金é¡? 104 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) = '822' GROUP BY MasterKey) a 105 FULL JOIN (SELECT MasterKey, 106 COUNT(PriKey) AS 他行代付ç†æ•¸, 107 SUM(AMT) AS 他行代付金é¡? 108 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND SUBSTRING(RBANK, 1, 3) <> '822' GROUP BY MasterKey) b 109 ON a.MasterKey=b.MasterKey) b 110 ON a.MasterKey=b.MasterKey) a 111 -----------------------------------------------------------------æˆ?功ç†æ•¸èˆ‡æˆ?功金é¡?--------------------------------------------------------------------------- 112 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 113 代收æˆ?功ç†æ•¸, 114 代收æˆ?功金é¡?, 115 代付æˆ?功ç†æ•¸, 116 代付æˆ?功金é¡? 117 FROM (SELECT MasterKey, 118 COUNT(PriKey) AS 代收æˆ?功ç†æ•¸, 119 SUM(AMT) AS 代收æˆ?功金é¡? 120 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) a 121 FULL JOIN (SELECT MasterKey, 122 COUNT(PriKey) AS 代付æˆ?功ç†æ•¸, 123 SUM(AMT) AS 代付æˆ?功金é¡? 124 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE='00' OR RCODE='') GROUP BY MasterKey) b 125 ON a.MasterKey=b.MasterKey) b 126 ON a.MasterKey=b.MasterKey) a 127 ---------------------------------------------------------------------失敗ç†æ•¸èˆ‡å¤±æ•—金é¡?--------------------------------------------------------------------------------- 128 FULL JOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey), 129 代收失敗ç†æ•¸, 130 代收失敗金é¡?, 131 代付失敗ç†æ•¸, 132 代付失敗金é¡? 133 FROM (SELECT MasterKey, 134 COUNT(PriKey) AS 代收失敗ç†æ•¸, 135 SUM(AMT) AS 代收失敗金é¡? 136 FROM ZT_Detail WHERE TXTYPE='SD' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) a 137 FULL JOIN (SELECT MasterKey, 138 COUNT(PriKey) AS 代付失敗ç†æ•¸, 139 SUM(AMT) AS 代付失敗金é¡? 140 FROM ZT_Detail WHERE TXTYPE='SC' AND MasterKey IN (SELECT PriKey FROM ZT_Master WHERE TDATETIME BETWEEN @DTBEG AND @DTEND) AND (RCODE<>'00' AND RCODE<>'') GROUP BY MasterKey) b 141 ON a.MasterKey=b.MasterKey) b 142 ON a.MasterKey=b.MasterKey) b 143 144 145 146 WHERE a.PriKey=b.MasterKey AND 147 --TDateTime BETWEEN @DTBEG AND @DTEND AND 148 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND 149 150 (a.Remark=@Remark OR @Remark=2) AND 151 (a.BankId=@BankId OR @BankId='') 152 ORDER BY TDateTime,CustId,PNo 153 154 155 156 WHERE a.PriKey=b.MasterKey AND 157 --TDateTime BETWEEN @DTBEG AND @DTEND AND 158 TDateTime BETWEEN '2007/9/5' AND '2007/9/5' AND 159 160 (a.Remark=@Remark OR @Remark=2) AND 161 (a.BankId=@BankId OR @BankId='') 162 ORDER BY TDateTime,CustId,PNo 163 164 165 GO 166 SET QUOTED_IDENTIFIER OFF 167 GO 168 SET ANSI_NULLS ON 169 GO 170 171
I'm having some very frustrating 'syncing' problems with my report (currently in dev in visual studio) and what actually exists in the database. To start, I had my report definition open, and realized I needed a new sproc. So I went over to the db and created a new sproc, lets call it "Test1". After making a new dataset in ssrs, I can't seem to get SSRS to realize the stored proc actually exists. I continually get the error:
"The stored procedure 'Test1' doesn't exist. (System.Data)
I've shut down visual studio, re-opened, etc. Same thing. It looks like visual studio has some cached version of sys.procedures that only refreshes when the bloody thing feels like it. Any ideas?? I've been doing this for awhile, and I've minded the connection string, correct sproc syntax, etc. It runs fine on SQL Management studio.
Another, similar problem - When adding in a new column to a sproc, the dataset doesn't seem to pick up on it, no matter how many times the "refresh fields" button is hit.
hi need help how to send an email from database mail on row update from stored PROCEDURE multi update but i need to send a personal email evry employee get an email on row update like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
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...
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.
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.
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
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.
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.
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.
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.
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?
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
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.
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
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))'
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 =
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!
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
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
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
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.
OK, This is an Update that I have working, But what do I do, if the customer does not exist already it doesn't add the customer? How should I remedy this? if the customer does exist works great.
UPDATE AC SET CustId = Left (CustomerId,10), CustName = Left (CustomerName,25), Addr1 = Left (Address1,25), Addr2 = Left (Address2,25), City = Left (ca.City,15), Region = Left (State,2), PostalCode = Left (Zip,5) FROM RIO.dbo.tblArCust AC INNER JOIN (SELECT CustomerCode, MAX(LastUpdatedDate) MaxDate FROM COFFEE.dbo.vueCustomerAddress GROUP BY CustomerCode) V ON V.CustomerCode = AC.CustId INNER JOIN COFFEE.dbo.vueCustomerAddress CA ON CA.CustomerCode=V.CustomerCode AND MaxDate=LastUpdatedDate WHERE CA.addresstypeid = 1
