select top 1 * from f_Function1('XXX',2099,99) ORDER BY ef DESC
I have about 400 XXX values. I execute function individually for them but normally i get results in individual result sets.
select top 1 * from f_Function1('XXX1',2099,99) ORDER BY ef DESC
select top 1 * from f_Function1('XXX2',2099,99) ORDER BY ef DESC
select top 1 * from f_Function1('XXX3',2099,99) ORDER BY ef DESC
I want all 400 results in one result set. What should i do?
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
Hello,In the project I'm working on, I need to add up all rows data for onecolumn. So, I have this code:$getprodcount = mysql_query("SELECT SUM(qty) FROM purchase");$numproducts=$getprodcount;Later on, I have this code: <?php print $numproducts; ?>What is being printed is Resource id #5...not the numeric value of what issupposed to be a sum. What is wrong? I am assuming taht resource id #5 is apointer of some sorts to the number I am looking for, but how do you getthe actual sum number?Thanks in advance!--Message posted via http://www.sqlmonster.com
Hello Sir I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form?
I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)
The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?
I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.
This is my function:
ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max)
IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END
FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END
RETURN @RTN
END
Each time I try " RETURN EXEC(@RTN) " or something similar I get an error.
I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks, Tom Anderson Software Engineer Custom Business Solutions
Periodically throughout the day a report is manually pulled from a SQL Server database. Is their a way w/o me adding a field to the database to have the result set return the "new" results? For example, lets say this is our DDL
Create Table OneTwoThree ( id int ,date11 datetime ,firefly varchar(10) )
Prior I could use this syntax, but that was only with needing to generate 1 result set.
Select id, convert(varchar(10), date11, 101) As [Date], firefly from onetwothree where CONVERT(varchar(10), date11, 101) < CONVERT(varchar(10), GetDate(), 101)
Looking at my datetime values, let's say the 1st was generated at 18:45, obviously the 1st two records in the table would be returned. And let's say a 2nd time I need to generate I want to exclude the 1st two entries as they have already been verified. How can I do such w/o adding a field to the table?
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function. This is how I retrieve the longitude: <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>" SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"> </asp:SqlDataSource> I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this: var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); However, lat & long do not contain the retrieved result but rather a useless System.something string. How do I assign the retrieved results to these variables and port them over to Javascript as required? Many thanks!
I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good). If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable? Possibly, is the expression after an AS something that I can manipulate into a double variable? My end goal is to insert the result of the calculation into a database. What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps: <asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource"> <Columns> <asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" /> <asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" /> <asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" /> <asp:BoundField DataField="density" HeaderText="density" SortExpression="density" />
</Columns> </asp:GridView>
<asp:SqlDataSource ID="lbsgalDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT a.Formulation, SUM (a.lbs) AS lbs, SUM ((a.lbs)/(b.density)) AS gal, ( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density FROM Formulations a INNER JOIN Materials b ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation">
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...
I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
Yesterday Peso was gracious enough to help me with creating function/views/sp's
I took those examples and extended what had from excel into function in SQL
however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function?
Here are excerpts two functions I have:
We'll call this function UserUsage() ------------------------------------ RETURN( SELECT ut.LastName, ut.FirstName, CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)], Max(hu.time_stamp) AS [Last Log Date], pct.Title, cat.topic_name FROM ZSRIVENDEL.dbo.UserTable ut, ZSRIVENDEL.dbo.history_usage hu, ZSRIVENDEL.dbo.pc_CourseTitles pct, ZSRIVENDEL.dbo.cam_topics cat WHERE ut.student_id = hu.student_id AND hu.course_id = pct.CourseID AND hu.topic_id = cat.topic_id AND ((ut.ClientID=@ClientID) AND (pct.ClientID=@ClientID) AND (ut.GroupID=3400) AND (hu.time_stamp>= @StartDate And hu.time_stamp< @EndDate) AND (hu.session_time<21600000)) GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name )
and will call this function UserSummary(): ----------------------------------------- RETURN ( SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] FROM ZSRIVENDEL.dbo.UserTable AS ut INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID WHERE ut.ClientID = @ClientID AND ut.GroupID = 3400 AND hu.Time_Stamp >= @StartDate AND hu.Time_Stamp < @EndDate AND hu.Session_Time < 21600000 GROUP BY ut.LastName, ut.FirstName )
As you can see the first part of the both query are simlar. In particular the:
SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)]
and also the variables @StartDate and @EndDate.
In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please?
