SQL 2012 :: Select Top 100 - A Record Is Not Returning From Database
Aug 24, 2015
I have a strange situation with an select. I've noticed that when I select top 100, a record is not returning from the database, but when doing top 101 the record appears on position 41.
The query is like this:
select top 100 GroupId, count(HouseId)
from House h
group by h.GroupId
order by max([DateCreated]) desc
From all discussions about top 100 vs top 101 I've noticed that everybody is saying that top 101 is using another algorithm and we can have a speed problem, but my problem is not about this. With top 100 I'm missing a record that should appear at index 41.
View 9 Replies
ADVERTISEMENT
Oct 7, 2014
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.
View 6 Replies
View Related
Jun 16, 2014
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
View 9 Replies
View Related
Sep 1, 2015
Process to create snapshot failed (twice per day snapshot of the database is being created for some people to work on). Morning snapshot worked fine but the afternoon one failed. The snapshot is being created from Mirror database. I used this code:
CREATE DATABASE [DB_snapshot] ON
(name = N'DB',filename = N'D:SnapshotDB.SQLSnapshot' )
,(name = N'indexes',filename = N'D:SnapshotDB_indexes.SQLSnapshot' )
AS SNAPSHOT OF DB
And the error message i get
Msg 1823, Level 16, State 6, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (202011:19306:2), for transaction ID (0:0), on page (1:1823948), allocation unit 281474979397632, database 'DB_snapshot' (database ID 6). Page: LSN = (201954:220201:1), allocation unit = 281474979397632, type = 1. Log: OpCode = 4, context 18, PrevPageLSN: (202010:23679:1). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 1, Line 1
During redoing of a logged operation in database 'DB_snapshot', an error occurred at log record ID (202011:19306:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
What might be causing this error? Mirroring seems to be working OK. status is "Synchronized"
View 1 Replies
View Related
Feb 11, 2004
Hi ,
Is there a way in SQL Server stored procedure to return multiple records/ more than one records....?
Thanks in Advance...
-Mohit.
View 1 Replies
View Related
Nov 8, 2007
when this query is run it returns the max value for each of the activity types eg. phone calls, emails etc.
what i want to achieve is for it to return only one record. whichever is more recent. but it only has to be either a phone call or an email.
SELECT regardingobjectidname, MAX(actualend) AS Last_Contacted_On, activitytypecodename, owneridname
FROM FilteredActivityPointer AS A
WHERE (statecodename = 'completed') AND (activitytypecodename IN (@activitytypes))
GROUP BY regardingobjectidname, activitytypecodename, owneridname
Melvin Felicien
IT Manager
DCG Properties Limited
View 8 Replies
View Related
Mar 7, 2008
ALTER PROCEDURE dbo.GetMostRatedRecipe
(
@CurrentDate datetime
)
AS
SET NOCOUNT ON
SELECT *
FROM Recipe
WHERE TotalRating =
(SELECT MAX(TotalRating)FROM Recipe) AND published = 1 AND ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate
This doesn't work good when the recipe having max total rating is not published & expired. I guess I need to first filter the recipes which are published & unexpired and then select the recipe having max total rating. But I don't know how to do that. Could anyone of you please help me doing this ?
View 1 Replies
View Related
Oct 19, 2006
I am having problems with a SQL query.
Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:
id, studentid, date, canceled.
I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid
I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.
what would be grate is if there where a way of returning a the coresponding id like in:
select studentid, max(date), related(id) as ids
from tablea
where canceled=0
group by studentid
then I could do:
Select *
from tablea inner join (select studentid, max(date), related(id) as ids
from tablea a
where canceled=0
group by studentid
) b on (a.id=b.ids)
View 5 Replies
View Related
Jul 29, 2006
There are loads of postings on the net about this problem but none I have found explain the cause.
Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.
Has anyone come across the solution for this issue?
Thanks
View 6 Replies
View Related
Oct 28, 2005
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
View 3 Replies
View Related
Sep 29, 2006
I need to do something sort of like the DESCRIBE function in MySQL. I need to return the table structure, AND the first row from each column sort of as an example of the data in each column.
then i would just need to run this query on each of my tables...
any ideas?
View 1 Replies
View Related
Feb 12, 2007
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee
View 3 Replies
View Related
Sep 13, 2006
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!
Thanks in advance, Larry
ALTER FUNCTION dbo.TestFoodDisLikes
(
@ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @RDLike varchar(50)
DECLARE @RDLikeList varchar(250)
BEGIN
SELECT @RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLike
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM RDLike_cursor
INTO @RDLike
SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike
END
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @RDLikeList
END
View 5 Replies
View Related
Aug 10, 2014
I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.
The desired output is:
Yellow Blue
11/23/201311/19/2013
11/19/210310/01/2012
10/01/210210/08/2010
10/08/201012/14/2007
The actual output is:
Yellow Blue
11/23/201311/19/2013
11/19/210311/19/2013
10/01/210211/19/2013
10/08/201011/19/2013
11/23/201310/01/2102
11/19/210310/01/2102
10/01/210210/01/2102
10/08/201010/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.
Here is the SQL:
SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
[Code] .....
View 9 Replies
View Related
Jul 20, 2005
Hi,In the process of localizing the 'regions' table, we added three newtables. The localized data will be stored in the TokenKeys andTokenValues tables. It would be easier if we did away with theTokeyKeys/TokenValues tables and just added a localeid in the regionstable, but this is the desired schema by the client. Here's theschema:Table: regionsid nameabbreviation1 United StatesUSTable: localesid locale1 en_US2 fr_CATable: TokenKeysid key1 db.regions.name2 db.regions.abbreviationTable: TokenValuesid keyid valuelocaleid1 1 Etas Unis22 2 EU2The old sql was simply this:select name, abbreviation from regionswhich returns:United States, USBut the new sql needs to link in the localized data from the tokeykeysand tokenvalues tables using the localeid... Im trying to figure outwhat the sql statement would look like to return this:Etats Unis, EU (This is supposed to be the French version)My confusion is we are trying to return multiple column values fromthe same column (TokenValues.value) and make them act as separatecolumns in the same record, like it was with the original.Thanks
View 1 Replies
View Related
Oct 5, 2006
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = @@IDENTITY
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
any help greatly appreciated,
Cheers,
Craig
View 3 Replies
View Related
Jul 20, 2005
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
View 9 Replies
View Related
Sep 28, 2007
We have an archive table which keeps each instance of a sales order that was archived under a "Verion No" field. Each time the sales order is archived it is entered into the archive tables (Sales Header Archive, Sales Line Archive). What I am trying to do is write a query to return all sales orders but only the most recent archived version.
For example this table layout is similar to what I am working with. Version No, Order No and Customer No. are the keys between the Header and Line tables, Customer Name column in the output is from only the Sales Header Archive table
SALES LINE ARCHIVE TABLE
Version No - Order No. - Customer No -----> (other columns)
1 s-5 1000
2 s-5 1000
1 s-6 2000
1 s-7 3000
2 s-7 3000
3 s-7 3000
1 s-8 4000
1 s-9 2000
2 s-9 2000
Here is what I need to output to show:
RESULTS OF JOINED TABLES
Version No - Order No - Customer No - Customer Name ---> (other columns)
2 s-5 1000 Something, Inc.
1 s-6 2000 Acme
3 s-7 3000 Company, LLC
1 s-8 4000 Blah & Associates
2 s-9 2000 Acme
It should return the last Version No of each Sales order.
Does that make sense? It is something probably easy... But, I've spent two days using multiples and multiples of different ways, that just aren't working: I'm about to dropkick my server cabinet...
View 4 Replies
View Related
May 12, 2014
,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.
Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356
Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?
View 8 Replies
View Related
Nov 26, 2014
I am trying to clean up security. When I check tables in a specific database I see a list of users with select access. There are 1000+ tables in the database. I know I can do 'revoke select on table_name to user_name' ....
View 3 Replies
View Related
Mar 12, 2008
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae
Here is the complete code:
SqlCeConnection _conn = null;
_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");
_conn.Open();
// DOES NOT WORK *** This statement does not return the record (it exist)
string _sql = "SELECT * FROM Product where RegDescr='0123456' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";
// works correctly *** as long as multiple records are returned
string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";
// works correctly
string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";
SqlCeCommand _cmd = _conn.CreateCommand();
SqlCeDataReader _rdr;
_cmd.CommandText = _sql;
_cmd.CommandType = CommandType.Text;
// Same results using ExecuteResultSet or ExecuteReader
//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
_rdr = _cmd.ExecuteReader();
listBox1.Items.Add("In the while loop");
while (_rdr.Read())
{
listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));
}
listBox1.Items.Add("Done");
View 1 Replies
View Related
Dec 5, 2013
I have a table tbl_rules. This table will define rules for each role. I have not yet defined the fields for the rules. But the table definition is as below:
Create table tbl_Rules
(
ID int identity(1,1) not null,
Role_ID int not null,
primary key (ID),
constraint fk_RoleName foreign key (Role_ID)
references tbl_Role(ID)
)
The table tbl_role has two columns as below:
ID RoleName
1 Manager
2 Analyst
3 Admin
So far so good. I created the tbl_rules.
But what i want to do is when I do select * from tbl_Rules, i want to show as below:
ID Role_ID
1 Manager
2 Admin
Instead it shows:
ID Role_ID
1 1
2 3
Is there a way to do this? The goal is to return the select * from tbl_Rules results to a gridview to enable adds and changes. I could do this by doing queries for each column, but I was hoping to make it easier. Not sure if this is even possible.
View 1 Replies
View Related
Jul 6, 2007
Hi,
My Select query returing the one column:
Options
1
2
3
Form this Options column I have to check the Checkbox1, checkbox2, checkbox3 out of total 6 checkboxes in asp.net form. I am getting the Options column from database.
How to store that resultset.
Thanks in advance.
View 2 Replies
View Related
Jun 26, 2014
I am trying to run queries on a table (table has zero rows). Inspite of giving 0 rows returned the query keeps on running and I have to cancel it. I tried inserting a dummy row into the table but even the insert operation is taking too long.Every query which I hit on the table just keeps on running without giving any result.
But this is not the case with other tables in the database.They are all running fine giving proper results. But this one table is behaving funny.
View 3 Replies
View Related
Aug 27, 2014
This query was run against a database on 2008 SP3 and 2012 SP2
Here is an example of what I ran
Declare @A_number varchar(5)
Select Top 1 Column_Is_an_int = @A_number
From Our_Main_Table
Where A_Database_Name = 'A database with many records in this table and multiple records in the Column_is_an_int some are more than 5 characters'
Select @A_number
Exec My_Stored_Proc @A_Nmuber
The top result for that database had an integer that was 8 characters including the - sign.
When the query ran I would normally expect it to throw the binary data cannot be truncated error. However in this case it returned a * in the variable which then tried to pass it in to the SP which of course threw a fit.
Once I changed the varchar(5) to varchar(50) it worked perfectly.
View 5 Replies
View Related
Nov 13, 2007
Hi friends,
I am facing problem with SQLRowCount() function which is returning -1 when I tried to fetch records greater than 99 from my data file. if the data file contains total of 99 records then the above function is returning its second parameter value nRowCount as 99 but if dat file contains 100 records then it is returning nRowCount as -1
Please tell me whether it is a driver specic error or some thing else. I am using a ODBC driver for a database called MYOB.
Below is the piece of code that I used for fetching records.
if (SQLExecDirect(m_StmtHandle, (PUCHAR)sExecuteStatement.c_str(), SQL_NTS) != SQL_SUCCESS) return false;
if (SQLNumResultCols(m_StmtHandle,(SQLSMALLINT *) &nColumnCount) != SQL_SUCCESS) return false;
if (SQLRowCount(m_StmtHandle,&nRowCount) != SQL_SUCCESS) return false;
The string 'sExecuteStatement' in SQLExecDirect() function above is carrying a Select query that fetch 100 records.
Its strange that the function fails to return exact row count for 100 records, but it is working fine with 99 records.
I ma not using any kind of array with size of 99 or 100 in my code.
What I want to know is whether I can use SQLRowCount() with SELECT statement, If not what is the alternative.
Pleasee help me with a piece of code if you can.
Ishwar B.G
View 1 Replies
View Related
Nov 13, 2007
Hi friends,
I am facing problem with SQLRowCount() function which is returning -1 when I tried to fetch records greater than 99 from my data file. if the data file contains total of 99 records then the above function is returning its second parameter value nRowCount as 99 but if dat file contains 100 records then it is returning nRowCount as -1
Please tell me whether it is a driver specic error or some thing else. I am using a ODBC driver for a database called MYOB.
Below is the piece of code that I used for fetching records.
if (SQLExecDirect(m_StmtHandle, (PUCHAR)sExecuteStatement.c_str(), SQL_NTS) != SQL_SUCCESS) return false;
if (SQLNumResultCols(m_StmtHandle,(SQLSMALLINT *) &nColumnCount) != SQL_SUCCESS) return false;
if (SQLRowCount(m_StmtHandle,&nRowCount) != SQL_SUCCESS) return false;
The string 'sExecuteStatement' in SQLExecDirect() function above is carrying a Select query.
Its an unpredictable error because the function fails to return exact row count for 100 records, but it is working fine with 99 records.
I am not using any kind of array with size of 99 or 100 in my code.
What I want to know is:
1. Can I use SQLRowCount() with SELECT statement, If not what is the alternative.
2. Is this a driver specific error?.
3. Can I use SQL_DIAG_ROW_COUNT instead of SQLRowCount? If Yes, How to use it?.
Your help will be greatly appriciated if you give me a piece of code for this issue.
View 1 Replies
View Related
Mar 23, 2014
I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:
// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}
When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.
View 0 Replies
View Related
Jun 9, 2014
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF
display_term: END OF FILE
column_id: 2
document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
View 2 Replies
View Related
Nov 6, 2014
I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.
Consider this script:
declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)
select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e
[Code] ....
The code works -- that is, the duplicate row is deleted. However the output clause returns:
abcdern
123451
So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?
View 9 Replies
View Related
Jun 16, 2015
I have this code:
Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)
and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:
select dbo.fnGet_Rec_Count('ACA_RSF') as cnt
here is the code:
alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
-- Declare the return variable here
DECLARE @count bigint
-- Add the T-SQL statements to compute the return value here
select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')
-- Return the result of the function
RETURN @count
END
GO
I get zero regardless of where @count is declared as in or bigint.
View 9 Replies
View Related
Aug 8, 2006
Hi there ;
This Problem is goin to make me crazy!
I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 .
I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() .
i use 2 selects as follows :
1)select * from X where Date1='8/9/2006 11:17:41 AM'
2)select * from X where Date2='8/9/2006 11:17:41 AM'
#1 is OK, but the second one returns a row with all columns set to NULL .
(X is the name of the table)
Thanks in advance
View 6 Replies
View Related
Jun 14, 2008
All- Please assist: In this SELECT statement:
1 SELECT person_id, (last + ', ' + first + ' on ' + CASE f.address_1 WHEN NULL THEN 'none' ELSE f.address_1 END) as last_first_address
2 FROM person
3
4 LEFT JOIN family f
5 ON person.family_id = f.family_id
I'm finding that the compound column last_first_address returns <null> when f.address_1 is null, even though it seems like it should return the string "none" per my CASE statement. If f.address_1 is not NULL, then it the statement works as expected. Please advise as to how I can fix the statement so that "none" is returned as the final string in last_fist_address if f.address_1 is null.
Note: I tried removing the parens. No effect on result.
Thanks!
-Kurt
View 2 Replies
View Related
