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
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)
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.
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
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.
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?
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"
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!
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; }
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 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.
-- 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.
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
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?
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.
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
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.
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?
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
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.
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
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.
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.
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:
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:
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
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?
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.
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?
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.
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
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.