I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
-- Create the CDONTS NewMail object. EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified IF @Body IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Body', @Body IF @hr <> 0 GOTO ObjectError END
IF @Cc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc IF @hr <> 0 GOTO ObjectError END
IF @Bcc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc IF @hr <> 0 GOTO ObjectError END
IF @HTMLFormat <> 0 BEGIN EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0 IF @hr <> 0 GOTO ObjectError END
-- Loop through the ; delimited files to attach CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0 BEGIN SELECT @StrEnd = CASE charindex(';', @Attachments) WHEN 0 THEN len(@Attachments) ELSE charindex(';', @Attachments) - 1 END SELECT @Attachment = substring(@Attachments, 1, @StrEnd) SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send. DELETE #FileExists INSERT #FileExists EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1) BEGIN RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment) RETURN 1 END
I would appreciate any help with my following problem... lets say i have...
select A.firstname + '' + B.lastname as fullname, 'Their Home is ' + A.City + ' ' + (select top 1 C.State from States C where C.City = A.City) as Location from tableA A, TableB B Where A.id = b.id
This is not the actual statement but follows the same kinda logic... the problem that i get is that some of the rows in both my fullname column and in my location column show up as null... how would i fix it so for instance even if the state is missing it would still show: their home is LA or if just the last name is available it would show the lastname?
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).
I have two statements that look like this:
SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber
Each statement correctly aggregates the sums, but I need a record that shows me:
CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid
I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).
Select COUNT(DATEDIFF(d, DateintoSD, SDCompleted) - DATEDIFF(ww, DateintoSD, SDCompleted) * 2) AS 'Total Jobs Completed' From Project WHERE (SDCompleted > @SDCompleted) AND (SDCompleted < @SDCompleted2) AND (BusinessSector = 34) AND (req_type = 'DBB request ')
I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID in Employee table. Can any one help? Thanks
I have a table Table, there are two set of Number and store in Tel1 and Tel2
I want to get all the number of each reacod that start with '1' on each record, the number I need that may in Tel1 or Tel2, I want to get the result like 'Result' table how can I do it ?
Is it possible to combine fields and text in a select statement? In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this: SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe FROM GE_Data WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets. If this is possible, or there's a better way to do it, I'd love some more info. Thanks! -Mathminded
I have a simple sql select statement that looks like this. Select Column1+ ' ' + Column2 As SpecFROM Table both columns are varchar's and the output i get is something like this.Spec-----------------------AaBbCc What I would like to return as my results is this: Spec--------------------AaBbCc I hope this makes sense. I can I accomplish that?Thanks!
With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS ( SELECT o.id, o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P') SELECT id,proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name
I have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31 ( M31_SQL_ID INT ,JOB_NUMBER INT ,LINE_NUMBER INT ,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242 16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245 16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
I have 2 Columns FirstName and LastName but i need to show it in UI as User Name ,that means i need to combine both First Name and Last name and display both as 1 field namely UserName ,How to query tht ? What shld i use?
select notes, jobid, caller from contact where status in (6) and jobid = 173 I am getting this:
This job will be posted to Monster for 2 weeks. 173 906 Waiting for full budget approval 173 906 TUrns out we're uppin 173 906
What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888
And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
Here is the code. SELECT info1.categoria FROM ( SELECT DC2.categoria,count(DC2.ref) as quantidade FROM Disco_Categoria as DC2 GROUP BY DC2.categoria ) as info1 WHERE info1.quantidade > 2 AND info1.categoria != ALL ( SELECT DC2.categoria FROM Encomenda_Disco as ED2 INNER JOIN Disco_Categoria as DC2 ON ED2.ref=DC2.ref WHERE ED2.cod = 1 --ED.cod )
What I'm trying to do is to check if a category doesn't exist for a specific delivery (Encomenda). After reading about the predicated ALL, I decided that it was to be used, but it isn't returning the expected result.
Someone correct me if I'm wrong (since probably I am ;)) but what I'm asking in the code is to compare info1.categoria with all results of DC2.Categoria to check if there is any match, returning true if there isn't any match at all.
It is just not doing it. Can someone please point me at the problem so I can solve this?
