I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)
Code:
SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY
FROM CUSTOMER C
INNER JOIN USERS U ON C.ID = U.ID
INNER JOIN ORDERS O ON C.ID = O.ID
INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID
my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column. Is there a way to return the content of these two tables? Is there a way to return a table from the stored procedure?
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.
The code is as follows:
<Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)
Dim adspath As New StringBuilder() adspath.Append(LDAP://[.......]/ou=Members/cn=) adspath.Append(username)
If username.ToString().Length > 0 Then Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous) If Not (uobject Is Nothing) Then exists = True Else exists = False End If End If
End Sub
The same code works fine from an ASP.NET. If I deploy the code and execute it with
exec LDAP_UserExists 'username'
I receive the error
Error converting data type varchar to bit.
And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.
Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.
Can anyone suggest the correct method of performing this query?
Hi I have a table called DiaryDate2 and a query called DiaryDateOver8 and want to have a sql query to return the records from the table that are not in the query based on two linked fields username and Diarydate1 The SQL I have written however returns all records SelectCommand="SELECT DiaryDate2.Username, DiaryDate2.DiaryDate1 FROM DiaryDate2 WHERE(DiaryDate2.Username = ?)AND NOT EXISTS (SELECT 1 from DiaryDateover8 where DiaryDateover8.Username=DiaryDate2.Username AND DiaryDateover8.DiaryDate1=DiaryDate2.DiaryDate1)ORDER BY DiaryDate2.DiaryDate1"> Please could someone help Many thanks Colin
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100
(and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
I am creating a SSRS report using a SSAS cube as it data source. The user would like to select multiple values from a reporting parameter that is then used as a filter on the MDX statement. I am bale to have the report work successfully when only one value is selected but not when multiple values are selected; the report uses only the first value from the reporting parameter when it contains multiple values. How do I filter an MDX query using a SSRS report parameter with multiple values?
I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT COUNT(*) AS TodayCount FROM Counter WHERE DATEPART(YEAR, visitdate) = Year(GetDate()) AND DATEPART(MONTH, visitdate) = Month(GetDate()) AND DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?
I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!
Hello,im having a problem integrating an SQL statement with my program,it works fine when theres a result but throws an error when there isno result,is there anyway in SQL to make up a row if there isnt one that matchesthe query for example in this i could put indicator values in thereturned fields then a simple check to see if its a proper field of myindicator fieldI know ISNULL works with single values but that only works if there isa row returnedDoes anyone know of any SQL syntax for MS SQL that when no row isreturned it makes on up and parses it backThank you in advance for your help!!Ben
In SQL Server for a field of datatype float(8), if i insert a value 2.62 , it saves it as 2.6200000000000001 like so for other values also. But in frontend i can see the right values.
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma] from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
Anyone seen wrong values returned from a CLR update trigger when using the columncount property?
I have a 6-column table that I was experimenting on, and the isupdatedcolumn property was not returning true on the one column I was updating (I verfied the trigger was firing). So, I returned the columncount property in a pipe.send, and was surprised to see the value of 11. When I looped through each column's isupdatedcolumn property, the only column that returned true was column 9.
Then I remembered this table used to have more columns, so I conducted the following experiment:
Added a new column to the table (bringing the total number of columns to 7). Executed an update the caused the trigger to fire; columncount sent to the pipe returned 12. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 12. Added another column to the table. Executed an update the caused the trigger to fire; columncount sent to the pipe returned 13. Deleted the column. Executed another update that caused the trigger to fire; columncount returned 13.
See the pattern? I'm fairly perplexed. Anyone seen this or something similar? This table is in a database that was migrated from SQL 2000 to 2005 and is 9.0 compatibility mode.
I have a simple table on my webpage which shows data from my database. It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value. I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work. If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
I have a table that contains an identity column. This column is updated in a stored procedure that has a linked trigger that updates another 'backup' table. How can I ensure that the value of the new row added in the table with the identity is passed back to calling client. ?
The value of @@identity is incorrect as it reflects the value in the 'backup table' not the original table. and use select max() on the original table within the stored procedure could be violated by other users attempting the same operation
I have an accounting database which contains data from various years.The frontend is a VB.Net program. At the year end, the program createsnew voucher and transaction tables and creates new stored procedures forthem.I just append the 'new year' at the end and create themie, Vouchers2001, Vouchers2002, Vouchers2003Similarly Transactions2001, Transactions2002.The data for all the years is in the same database.Also, I maintain a table called 'Books' which contains the Years forwhich data is present in the Database. The Structure of the Books tableisBookID BookYear1 20012 20023 20034 2004My Problem is that i need to know the current balance of any ledger forany year. The method to calculate the balance for any year is to startfrom the Minimum year in the Books table and continue upto the requiredyear. The SQL is as follows.DECLARE @iLedgerID AS INT --will be passed as parameterDECLARE @iYear as INT --will be passed as parameterDECLARE @CurrentBalance as MONEYSET @iLedgerID =1DECLARE @MinBook as INTEGERDECLARE @String nVarchar(4000)SELECT @MinBook = Min(BookYear)FROM BooksWHILE @MinBook <= @iYearBEGINSET @String = ' DECLARE @TT Money ' + char(13) +' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOINdbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +'WHERE (LedgerID = @iLedgerID)'EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */SET @MinBook = @MinBook + 1ENDNow this is just a sample code. It may have a few glitches. My questionisa) Do I have to create a dynamic sql if the name of the database is notknown ahead of time. If No thenb) I need to add the balance of each year to the grand total. How do ireturn a value from a dynamic sql.TIA*** Sent via Developersdex http://www.developersdex.com ***
hi, i'm using the following code to generate the value of a column in a database, where the colums value is dependent upon the multiplication of two other cells in the same row. When I execute the statement, it will work fine for whole numbers, but it does not work correctly for decimal values. All columns are of type varchar(50). An exmple.................It just multiplied 200 by 2.50 and returned 600SqlCommand objCmd2 = new SqlCommand("UPDATE Portfolio SET Current_Worth=Current_Price*Number_Of_Shares WHERE Name_Of_Asset LIKE '%'+@Name_Of_Asset+'%'", objConn);objCmd2.Parameters.AddWithValue("@Name_Of_Asset", DropDownList1.SelectedValue);objConn.Open();objCmd2.ExecuteNonQuery();objConn.Close(); Any ideas?
Hi, How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned. i.e. SELECT * FROM GetMiddleValues (3);
If anyone has any ideas on how to accomplish this, that would be great.
I am just starting to learn about stored procs and how great they can make my world. I have been able to create a pro that does some data checking and inserts based on the results but what I have not been able to figure out is how would I pass a message from the proc to my Perl script that calls it? If anyone could point me in the right direction on what commands I should be looking into I would appreciate it.
-- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.
select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh where luis_id in ('I','E') and aseld.whse_id = 1 and ( (aseld.batch_id between goal_beg_batch and goal_end_batch and monitor_group = 'YK')
[code]....
is it possible to get the numbers from keys 1 & 2 on the same row in a new query?
Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.
