I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @lastscandate would ever be
NULL. If it is null it should be equal to @maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @lastscandate. This should then active the next if
statement and set the @lastscandate equal to the @maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.
Thanks for your help.
DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))
OPEN c1
FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycle
WHILE @@fetch_status = 0
BEGIN
--Get MailDate from Manifest - if NULL then use GetDate
set @maildate = NULL
SELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycle
if @maildate is NULL
set @maildate = getdate()
--Get Last Scan Date from Transactions - if NULL then use MailDate
set @lastscandate = NULL
select @lastscandate=max(actiondate) from transactions where
acctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303
if @lastscandate is NULL
set @lastscandate = @maildate
BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @maildate,
lastscandate=@lastscandate
WHERE id = @mid
I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable] ON [dbo].[t_account]-- A DB level trigger FOR UPDATE --Event we want to capture AS IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
SET @UseCTD = CASE WHEN @BranchTranType IS NULL AND @BranchDivision IS NULL AND @BranchRegion IS NULL AND @BranchNbr IS NULL AND @BranchSatId IS NULL THEN 1 ELSE 0 END
SELECT DISTINCT L.ProviderId, L.OriginalProviderId, S.SourceCode, SG.SourceGroupCode, SGDescription=SG.Description, RG.ReportGroupId , ReportGroupDesc=RG.Description , L.VendorId, VEN.VendorName, B2.BranchTranType, B2.BranchDivision, B2.BranchRegion, B2.BranchName, L.BranchNbr, L.BranchSatId , Branch=CONVERT(varchar, L.BranchNbr) + L.BranchSatId, LoNbr = ISNULL(L.LONbr, 9999), LoName = ISNULL(LO.LOName, ' - NOT ASSIGNED -') , LoEmploymentStatus = LO.EmploymentStatus, LeadCount = 1, Completed = CASE WHEN L.RecCompleted = 'C' THEN 1 ELSE 0 END, InCompleted = CASE WHEN L.RecCompleted = 'I' THEN 1 ELSE 0 END , NoContact = CASE WHEN LD.Disposition = 16 THEN 1 ELSE 0 END, Contacted = CASE WHEN LD.Disposition <> 16 THEN 1 ELSE 0 END, Pending = CASE WHEN LD.Disposition IS NULL THEN 1 ELSE 0 END, NoSale = CASE WHEN LD.Disposition <> 1 THEN 1 ELSE 0 END, Apps1003 = CASE WHEN LD.Disposition = 1 THEN 1 ELSE 0 END , AppraisalsOrdered = CASE WHEN ISNULL(T3._@8034, 0) = 0 THEN 0 ELSE 1 END, ClosedApps= CASE WHEN (@UseCTD = 1 AND SMT2._H0770 IS NOT NULL) OR SMT._H0770 IS NOT NULL THEN 1 ELSE 0 END, LoanVolume = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(T1._@2026, 0) ELSE ISNULL(T1._@2026, 0) END, OrigLoanBalance = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2._H0360, 0) ELSE ISNULL(SMT._H0360, 0) END, Revenue = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2.calc_TotalIncomeDollars, 0) ELSE ISNULL(SMT.calc_TotalIncomeDollars, 0) END, MarketingCost = CASE WHEN L.VendorId IN (59, 60) THEN 0 ELSE ISNULL(S.Cost, 0) END, CAM.LeadTypeId, SMT.calc_TotalIncomeDollars, SP.SubjectAddrState, SP.SubjectSCF, S.LeadFormLabel FROM MLS..tbl_MLS_Leads L (NOLOCK) INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId) INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId) INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId) INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr) INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId) LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr) LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN) LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr) LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr) LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM) LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM) LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId) LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId) LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID) LEFT JOIN ( SELECT LONbr, LOName = FullName, EmploymentStatus, ReHireDate, HireDate, TermDate
FROM MLS..vw_MLS_UsersWithLONbrs (NOLOCK) ) LO ON (LO.LONbr = L.LONbr) LEFT JOIN MLS..tbl_MLS_Campaigns CAM (NOLOCK) ON (CAM.CampaignId = SG.CampaignId) LEFT JOIN MLS..tbl_MLS_Vendors VEN (NOLOCK) ON (VEN.VendorId = L.VendorId)
WHERE L.DateReceived BETWEEN @BeginDate AND @EndDate + ' 23:59:59' AND (CASE WHEN @ReportGroupId IS NULL THEN 1 WHEN @ReportGroupId = RG.ReportGroupId THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchTranType IS NULL THEN 1 WHEN @BranchTranType = B2.BranchTranType THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchDivision IS NULL THEN 1 WHEN @BranchDivision = B2.BranchDivision THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchRegion IS NULL THEN 1 WHEN @BranchRegion = B2.BranchRegion THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchNbr IS NULL THEN 1 WHEN @BranchNbr = L.BranchNbr THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchSatId IS NULL THEN 1 WHEN @BranchSatId = L.BranchSatId THEN 1 ELSE 0 END) = 1 AND L.ProviderId = L.OriginalProviderId AND L.VendorId NOT IN (59, 60, 131) -- Exclude Turndown or Ghost leads AND L.Deleted = 0
I need to add the field DateReceived from the following view:
dbo.vw_MLS_Leads
How should I code this?
As always thanks for the great answers and suggestions in advance.
I'm using this query to update the top 1 row: Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2 set rowcount 0
The key field on the table is not an identity field.
Is there a way to get that KEY field's data (for the row that was updated)?
I tried this just to see what would happen and it returns null: Set rowcount 1 Update MyTable set Status = 1, sdi=999 where Status=2 select @@IDENTITY set rowcount 0
Hello,I have a stored procedure: -- Get an individual league match by IDALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]( @LeagueMatchID int)ASSET NOCOUNT ONSELECT * FROM mb_LeagueMatch WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchIDThe mb_LeagueMatch table has a column named IsActive that is a bit datatype.The value for all rows is set to true (in database explorer in visual studio 2005).When I execute the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.Has anyone seen this before?Thanks,Howard
I need to update a field with the code below, is it possible to do this with a table join? The Query Analyzer is giving me an error at "Join".
An example: (ManufSerNo is a field in table ASSETS, Status is a field in table HISTORY)
GO
CREATE PROCEDURE sp_ChngeAssetStatus
@ManufSerNochar(10), @Statusvarchar(1)
AS
UPDATE HISTORY SET Status = @Status JOIN ASSETS ON HISTORY.AssetID = ASSETS.AssetID WHERE ManufSerNo = @ManufSerNo --ELSE --RAISERROR ('Sorry, but the Asset ID specified does not exist. Record not updated.', 10 , 1)
I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.
I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:
CREATE PROCEDURE sp_UpdateProductsTable @prodName varchar(50), @prodManufacturer varchar(50), @prodShortDescrip text, @prodLongDescrip text, @prodCatId int, @prodPrice varchar(6), @prodSortOrder int, @prodActive int, @prodId int AS if( @prodId <> 0 ) begin update Products set Name = @prodName, Manufacturer = @prodManufacturer, ShortDescription = @prodShortDescrip, LongDescription = @prodLongDescrip, CategoryID = @prodCatId, Price = @prodPrice, SortOrder = @prodSortOrder, Active = @prodActive where ID = @prodId;
select ID as prodId from Products where ID = @prodId
end if( @prodId = 0 ) begin insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID, SortOrder, Active ) values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip, @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
SELECT SCOPE_IDENTITY() AS prodId end
GO
I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.
Hello, I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below: Input field: Product Description So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned. SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill" I guess there'll need to be a nested Select and loop to parse the space separated input field. Any pointers would be appreciated. Thank you Lee
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A [ = Plan B + = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how to get a pointer to the data and how to read from it.
But i want to copy the whole data.
Does anybody know how to do that?
Thanks in advance
A.Gempp
------------------------------- I'm using SQL Server 2000
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code: SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate FROM dbo.Applicants WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr) ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
I need to strip out all alpha chars and spaces in a given field and return only the numbers.
I've tried =CInt(Fields!Info.Value) and get an unexplained error. If the data was formatted consitantly I could simply do a RTrim or Right, but the number strings are not the same, some have spaces as in phone numbers (1 800 555 1212) or don't have a leading 1. Most instances are correct for my purpose (8005551212).
Any help would be appreciated.
UPDATE: Using the Replace function =Replace(Fields!Info.Value, " ","") gets me almost there. Now I should be able to use a Right, 10 function to return my desired value. Is it possible to combine these two funtions together?
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this? ASPX.CS file protected void Page_Load(object sender, EventArgs e) { SqlConnection con = null;
con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; da.Fill(ds);
Is there such a date/time?I see the Created date on the list of stored procs, but really want aDate Last Updated. After changing code for 3 hours, I tend to forgetwhich procs I've worked on, and which need to be move to production.any simple way to keep track of the last procs played with?thanks in advance...Join Bytes!
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 wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
I was wondering, is it really true that in Sql Server 2005, there is no updated date -property for stored procedures? There is a created date -property, but it's not useful, because stored procedure's get updated often. This is a huge disadvantage for me, and I was really waiting for ms to fix this problem after sql server 2000, but no. Does anyone figured out any solution to this problem?
I have just starting creating some stored procedures for our system and have a question related to management of these.
When using input parameters using the following syntax:
CREATE PROCEDURE sp_someInputProcedure @Username as varchar(16) @Password as varchar(12) @Name as varchar(50) @Address as varchar(60) @Zip as int @City as varchar(30) ... etc.
This is all well and good, but what if I make a change in the datamodel - for instance changing a datatype or the length of a varchar - do I need to remember to manually update all stored procedures that uses these columns/variables?
Seems like a bit of a hazzle. Is there an easier way to do this?