Proc Returning All Records

Nov 17, 2006

Following Proc is returning all records.I have passed any value to parameters but no luck.


CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;

if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO



zx

View 2 Replies


ADVERTISEMENT

Stored Proc Returning Dataset && Number Of Records Not Working

Jul 7, 2004

Hi

I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.

This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go

This is asp.net code

Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)

conn.Open()

dr = cmd.ExecuteReader

nRecords = convert.int32(cmd.parameters(@return).value)

conn.close


Thanks
Lbob

View 1 Replies View Related

Returning Recordset From Stored Proc

Mar 7, 2008

I need to return a rowset from a stored procedure.  The returned rows will have ten columns and need to be bound to a gridview.  Here is a code snippet.
'Create a DataAdapter, and then provide the name of the stored procedure.MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@condition_cl", _
SqlDbType.VarChar, 100))
'Assign the search value to the parameter.MyDataAdapter.SelectCommand.Parameters("@condition_cl").Value = sqlwhere & orderby
'ASSIGN THE OUTPUT PARAMETERS. HERE IS WHERE I NEED HELP  (?????????????????)
 
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") 'Fill the DataSet with the rows returned.
'Set the data source for the DataGrid as the DataSet that holds the rows.GridView1.DataSource = DS.Tables("TMPTABLE_QUERY").DefaultView
GridView1.DataBind()
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close()
 
In my little research, I have seen examples of how to return a single value, but not multiple rows.  I essentially have two problems.  I'm not sure how my output parameters are to be defined and added.  Do I need a separate 'Parameters.Add' statement for each column field value returned or can I do a single 'Parameters.Add' statement to define the whole row as an output parameter?  Also, upon returning from the call to the SP, will I need a looping mechanism to populate the recordset for each individual record returned, or will the 'MyDataAdapter.Fill(DS, "TMPTABLE_QUERY") suffice, as included in my code above?
Thanks in advance.

View 3 Replies View Related

Stored Proc Returning 0 Size

Mar 3, 2004

i have a stored proc


CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as

begin

declare @noteid as int
select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes'
if @noteid > 0
begin
select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid

end
else
begin
set @cusnotes='none'
end
print @cusnotes
end
GO


the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page

'@cusnotes' of type: String, the property Size has an invalid size: 0

i tried to run the stored proc frm the QA

declare @note as varchar(1000)
exec @note=pop_notes 3430,'y'
print @note



it returned

none
0 <-- this seems to be causing the error


what could be the error/reason...

thanks in advance

View 3 Replies View Related

Returning @@IDENTITY FROM Stored Proc

Nov 15, 2005

I am trying to get the identity of an inserted record using this SP:

<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
    @AuctionID Int OUTPUT,
    @StartDate char(255),
    @StartTime char(255),
    @Location char(255),
    @Title char(255),
    @Description char(255),
    @Images char(255)
)
AS
INSERT INTO Auctions
(
    StartDate,
    StartTime,
    Location,
    Title,
    Description,
    Images
)
VALUES
(
    @StartDate,
    @StartTime,
    @Location,
    @Title,
    @Description,
    @Images
)

SELECT     AuctionID AS ID
FROM         Auctions
WHERE     (AuctionID = @@IDENTITY)
</code>

Then, I am using this class for a file called dataaccess.cs to return the ID:

<code>
public string SaveImageName(string ImageName, string AuctionID)
        {
            SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

           
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
           
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;

            SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
            paramImagePath.Value = ImageName;
            this.newSqlCommand.Parameters.Add(paramImagePath);

            SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
            paramAuctionID.Value = AuctionID;
            this.newSqlCommand.Parameters.Add(paramAuctionID);

            SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
            paramImageID.Direction = ParameterDirection.Output;
            this.newSqlCommand.Parameters.Add(paramImageID);

            //Return SqlDataReader Struct
            this.newSqlCommand.Connection.Open();
            this.newSqlCommand.ExecuteNonQuery();
            this.newSqlCommand.Connection.Close();

            int returnID = (int)paramImageID.Value;

            return returnID.ToString();
        }
</code>

The above code returns null.

Can anyone tell me what I am doing wrong?

THanks, Justin.

View 2 Replies View Related

Returning Errors From Stored Proc

Feb 5, 2007

Hi,

Does sql server return an error code when a stored procedure fails, or an operation inside a stored procedure fails. How does one trap it?

We have an application that executes a number of stored procedures. I would like to verify that the procedures executed successfully, and the operations contained within the stored procedures (i.e. insert into a table) did not result in an error. If there was an error with the stored procedure of the operations inside, I would like to trap it, and return it to the calling program (non sql server).

I have seen the @@error global variable, but it only gives the return code of the last operation, which means I would have to check after every select, insert, delete, etc. I am hoping to avoid this.

Any thoughts?

Thanks in advance

View 2 Replies View Related

Returning A Value From Stored Proc With Query

Jul 18, 2014

In some of our business object reports we have to create variables to decode values to what we want. I am trying to replicate this in SQL Server and remember doing this in SQL server 2000 years ago back can't remember the exact way to do it. I remember running a query and calling stored proc within query which would return the value I wanted but not sure if I can still do this in SQL server 2008 and by that I mean doing it within query or have to do it another way.

Basically what I want is to have a procedure with all the variables replicated within that procedure so that when I run a query I can just call the appropriate bit of code by passing a specific name like

select job.dept, dbo.decodevariable('ShowJobDesc' job.jobtitle), job.salary
from job

so 'ShowJobDesc' and the job.jobtitle would be used to decode each job title to return job description.Just a bit unsure and can't remember if I am doing this the right way, is this possible?

View 2 Replies View Related

SQL Stored Proc Not Returning Any Data In The Web Page

Feb 26, 2007

Hi
I have coded the simple login page in vb .net  which calls the stored proc to verify whether the user login details exists in the database.  The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I  execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
 
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
 
 
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
 
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
 
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
 
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
 
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
 
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
 
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
 
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
 
End Try
 
Else
ErrorLbl.Text = "Please enter you username and password"
 
 
 
End If
 

View 5 Replies View Related

Returning Mutliple Strings From Stored Proc

May 14, 2007

Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!

Muchos gracias!

View 12 Replies View Related

Call To Stored Proc Returning Null Datatable

Jun 6, 2007

I have a stored proc which should be returning a datatable.  When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code.  I'm not getting any errors during runtime.  Any help at all would be a BIG help!
private void PopulateControls()    {        DataTable table = CartAccess.getCart();    }
public static DataTable getCart() {        DbCommand comm = GenericDataAccess.CreateCommand();        comm.CommandText = "sp_cartGetCart";
        DbParameter param = comm.CreateParameter();        param.ParameterName = "@CartID";        param.Value = cartID;        param.DbType = DbType.String;        param.Size = 36;        comm.Parameters.Add(param);
        DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm));        return table; }
public static DataTable ExecuteSelectCommand(DbCommand command)    {        // The DataTable to be returned         DataTable table;        // Execute the command making sure the connection gets closed in the end        try        {            // Open the data connection             command.Connection.Open();            // Execute the command and save the results in a DataTable            DbDataReader reader = command.ExecuteReader();            table = new DataTable();            table.Load(reader);            // Close the reader             reader.Close();        }        catch (Exception ex)        {            Utilities.SendErrorLogEmail(ex);            throw ex;        }        finally        {            // Close the connection            command.Connection.Close();        }        return table;    }

View 1 Replies View Related

SELECT Returning Multiple Values In A Stored Proc

Jul 20, 2005

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

View 6 Replies View Related

Openrowset In Stored Proc Returning 'S:' Is Not A Valid Path

Nov 2, 2007

I am having a difficult time figuring this one out. I have a stored procedure that as part of an ASP.Net app connects to a csv file through a mapped network drive and imports the data into a table in SQL Server. Here it is.

------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[usp_ImportComp]

-- Add the parameters for the stored procedure here
@CSV as varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Begin Try
Begin Tran
Declare @sql as nvarchar(max)
Set @SQL = 'Insert into dlrComp ( SN,
CM,
PM,
MC,
Comp_Total,
XU,
GM,
From_Date,
To_Date)
Select [Serial Number],
CM,
PM,
MC,
[Comp Total],
XU,
GM,
[From Date],
[To Date]
FROM OPENROWSET(' + char(39) + 'MICROSOFT.JET.OLEDB.4.0' + char(39) + ',' + char(39) + 'Text;Database=S:' + char(39) + ',' + char(39) + 'SELECT * FROM ' + @csv + char(39) + ')'
--print @sql
Exec sp_executesql @stmt=@sql
Commit Tran
End Try
Begin Catch
Rollback Tran
--Do some logging and stuff here
End Catch
END

------------------------------------------------------------------------------------

If I am connected to SQL through SQL Management Studio while logged in on the server that is running SQL as DomainUser1 and execute

exec usp_ImportComp @CSV='Comp.csv'

It completes successfully

However if I open SSMS (while logged into Windows on my PC as DomainUser2) using runas to run it as DomainUser1 while logged into my PC and connect to SQL Server using WIndows Auth and run the same I get the following error message.

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'S:' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

If I add
With Execute as 'DomainUser1' and modify the stored procedure I get the same error message above.

If I log onto the Server that is running SQL as DomainUser2 I can successfully run

exec usp_ImportComp @CSV='Comp.csv'

Both User1 and User2 have the same permissions to the Share and csv as does the Domain user under whose context SQL Server is running.

What am I doing wrong?

View 4 Replies View Related

Returning Middle Records

Dec 7, 2006

I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
 "SELECT TOP 20 to 40 * FROM Northwind"
 Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ

View 2 Replies View Related

Sqldatareader Not Returning Records

Feb 13, 2007

My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?    

View 4 Replies View Related

Returning The Last Row From A Set Of Duplicate Records

Feb 27, 2002

Any information as to how to handle this?

Thanks.

View 1 Replies View Related

Returning A Specified Range Of Records

Jul 14, 2005

Hi,

I have a SQL question which I suspect is very easy to answer but can't seem to find one for.

I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.

So, the SP would for example accept the parameters

@Upperbound = 80
@Lowerbound = 61

and would return a recordset of 20 records being the 61st thru 80th record from the queried table.

Thanks for the help
Guy

PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.

View 4 Replies View Related

Help With Returning A Certain # Of Records From A View.

Jul 20, 2005

I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike

View 3 Replies View Related

Returning Billable Records.

May 15, 2008

Hello -

I'm trying to write a select statement that will return only "billable" records.
A record is billable if:
- it is the only record for a Case (Case is an FKey ID column in my records table)
OR
- the last billable record for Case X has a DateTime > 24 hours before the record in question.

Getting records for the first condition is easy:

SELECT ID FROM Records r
LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case]
WHERE r2.[Case] IS NULL

It's the second part I'm having trouble with.

UNION
SELECT ID FROM Records
JOIN ?
....
WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??

I have to assume there can be any number of records for a case.

Thanks in advance!

View 10 Replies View Related

Returning 30% Random Records

Sep 27, 2007

HI,

If i have the following data




Code Block

Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);

Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');





How can i return 30% random requestid's from this table?

thanks.

View 9 Replies View Related

Returning ALL Records In A Query

Aug 18, 2006

I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.

Thanks

ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0,
CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS'
WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS'
ELSE 'NIGHTS'
END)
END

IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime

END
END

END




SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription,
dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift
FROM dbo.LineFaultDescription INNER JOIN
dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND
dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID
and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')

WHERE (dbo.LineFaultData.AssetID = {Asset_ID})
AND (dbo.LineFaultData.Shift = '{@Shift}')
ORDER BY dbo.LineFaultData.StartTime DESC

View 4 Replies View Related

SQL Command Returning Some Incorrect Records

Nov 13, 2006

I am fairly sure that I am just overlooking something, but the following command is returning some incorrect fields. 
SqlDataSource1.SelectCommand = "SELECT ITNBR, (SELECT ITDSC FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS ITDSC, SUM(MOHTQ) AS Balance, (SELECT VNDNR FROM AMFLIBT.ITEMASA WHERE AMFLIBT.ITEMASA.ITNBR = AMFLIBT.ITEMBL.ITNBR) AS VENDOR FROM AMFLIBT.ITEMBL WHERE VNDNR = @DDLVNDNR GROUP BY ITNBR, VENDOR"
SqlDataSource1.SelectParameters.Add("ddlvndnr", ddl1.SelectedValue)
I have more code to show, if this looks correct.  Just let me know.
Thanks in advance.

View 2 Replies View Related

SQL Syntax Returning Wrong Records

Nov 23, 2007

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

View 5 Replies View Related

Returning Limited Number Of Records!

Jul 8, 2004

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.

View 2 Replies View Related

Returning Range Of Records In MSSQL

Jan 25, 2005

Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:



SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC



Where this statement would return only records 2000 to 5000 of the returned results.

View 2 Replies View Related

T-SQL (SS2K8) :: Returning Multiple TOP Records?

Jul 10, 2014

Here is my setup: I have the following tables -

tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account

I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.

People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.

So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.

But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?

Straight TOP by itself won't do it.
GROUP BY by itself won't do it.

View 9 Replies View Related

Returning Records Within Days Range

Oct 31, 2013

This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:

PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 01/12/2012 01/12/2013 arr_56464
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494

And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.

But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:

PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494

But can't seem to get this using a self join query like this:

select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID
from arrivals a INNER JOIN arrivals b
ON b.personID = a.personID
WHERE
a.arrivalid != b.arrivalid
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0

View 4 Replies View Related

Returning Records That Exclude Top 10% Of Values

Mar 6, 2008

I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?

2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159

View 3 Replies View Related

Returning Records Unordered/randomized

May 8, 2008



Hi All,

Let's say I have a SELECT that returns 1000 records and I want them unordered or randomized How would I do that?

So instead of:
OrderID
1
2
3
4
5...and so on

I want:
OrderID
67
300
100
1
910..totally random, but all 1000 records must still be returned.

Regards
Melt

View 10 Replies View Related

Question On Returning All Records When Using Parameters...

May 4, 2007

I'm trying to fully utilize parameters on a report.



I have 3 parameters, a start date, end date, and a cause.



The start and the end date are working as expected. What I would like is if a user fails to select a cause (queried from a lookup dataset) I would like to return all records, not filtering by cause. I'm using parameters handed to the dataset to do this and the report will not allow me not to select a value.



Should I be using Filter, Report Parameters, or some other means to solve this?



Any advice would be appreciated as I don't want to "KLUDGE" up the system with a bunch of reports each with varying levels of criteria.



Thanx!



TR

View 8 Replies View Related

Returning Number Of Records Found In Query

Jan 24, 2008

I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.
         sub findcustomers(sender as object,e as eventargs)            dim connection1 as sqlconnection=new sqlconnection(...)            dim q1 as string="select * from tblcustomers where store='65'"            dim command1 as sqlcommand=new sqlcommand(q1,connection1)            dim a as integer            command1.connection.open()            a=command1.executenonquery()            label1.text=a.tostring()            command1.connection.close()         end sub
What am I doing wrong?

View 8 Replies View Related

Returning Only Non Matching Left Join Records

Mar 3, 2015

How to return only non matching left join records. Currently I am doing a traffic management database to learn sql.

I am checking for all parishes with no associated drivers. Currently I only have 2 of such.

The regular left join

select parish.name, driver.fname from parish left join driver on driver.parish=parish.name

Returns the all the names of the parishes and the first name of the associated drive, followed by the matches, however the two parishes with no matches have null for the first name.

View 1 Replies View Related

Query Returning 1.7million Records Slow

Nov 23, 2005

Hi,I have a sql server database with 1.7 million records in a table, withabout 30 fieldsWhen I run select * from tablename it can take over 5 minutes.How can I get this time down or is it normal?ThanksJerry

View 4 Replies View Related

Bug? Report Builder Returning Only Unique Records!

Feb 8, 2007

Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.

Example:

TransID Type TransQuantity

1 CS 4
2 CS 4
3 CP 2
4 CN 3

Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:

Type TransQuantity


CS 4
CP 2
CN 3

Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.

Thanks,
Taurkon

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved