How Do I Return A Multiple Resultset In MSSQL2000?
Apr 19, 2006
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
What is the problem with the code?
How do i display and return a resultset to my coldfusion client application?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.nTransaction
(@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR
-- FORWARD_ONLY STATIC
SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
FROM entryheaders as Eh cross join entrys as E
WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
ORDER BY Eh.EntryID DESC
SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @ntransCursor into @nValue
END
CLOSE @ntransCursor
DEALLOCATE @ntransCursor;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Okay heres the problem I'm facing. I have GoDaddy as my hosting company. They only support MSSQL2000 but I just recently bought MSSQL2005 Developer Edition. And I was wondering what is the main difference between them two. I didn't find anything on Google. And the only thing is I can't find a better host. Cause I have 50GB of space and 500GB of bandwidth. And I don't want to lose that for just MSSQL2005 support cause my host doesn't/ Have that. So im stuck like chuck. Is there really any difference?
Hello all, I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob @MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode FROM dbo.TJob INNER JOIN dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN WHERE (dbo.TJob.IJobN = @MyJobNo) GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
I need to return only the top row of a ResultSet that is generated from my Query but can't seem to find anything in SQL Server 3.0 Mobile that allows me to do that. I know in SQL Server (Desktop) I could use the TOP keyword. The query I have is as follows, and just need the top row. Now the returned resultset will, in time, be massive, and there is just no way I can afford to return this amount of data in my application only to take to the top/first row. The query I have is as follows...
SELECT * FROM tbl_NSP_AnswerSet WHERE (DateCompleted IS NOT NULL) ORDER BY DateCompleted DESC
(I want the last record that was inserted into the database)
I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code: while (@I <= @count) begin BEGIN TRY -- delete all previous rows inserted in @customerRow for previous counts @I delete from @customerRow -- this is inserting the current row that we want to save in database insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code: begin catch IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorLine INT; SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.
I am getting multiple rows returned because the form that am working with has 4 possible different signtures on it I need to be able to get it so that I can return on row with the a column for the signature and date for each signature
SELECT a.op__docid, a.order_no, a.deptname, a.order_date, a.person_completing, a.date_req, a.dept_head, a.dept_head_ext, a.deliver_to, a.vendor_info, a.purchase_reason, a.qty1, a.qty2, a.qty3, a.qty4, a.qty5, a.item1, a.item2, a.item3, a.item4, a.item5, a.unit_cost1, a.unit_cost2, a.unit_cost3, a.unit_cost4, a.unit_cost5, a.ext_cost1, a.ext_cost2, a.ext_cost3, a.ext_cost4, a.ext_cost5, a.shipping, a.total, b.op__tiername, b.op__sectionid, b.op__usersigname, b.op__when, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 386) AS Requestor, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 385) AS DeptHead, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 384) AS Administration, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 444) AS Receiver
FROMfd__purchase_order a JOINt4w_signatures b ON a.op__docid = b.op__parentid
Hi there,I have tables with such structuretransaction_YYMM(idx,date,company_id,value)where YYMM stands for 2digits year and monthI want to define query (maybe view, procedure):select * from [???] where date>='2007-01-01' and date<='2007-04-30'which will grab data fromtransaction_0701transaction_0702transaction_0703transaction_0704and return all as onebest regardsRafal
Here's what I'm trying to do:I have a table, which I don't have administration over, with 4 interestingfields:RecordCount - an auto incrementing primary keyCardNumber - integerCompanyID - integerAccessPriv - an string that varies constantlyThere could be identical CardNumber's, but they must have differentCompanyId's. A data set might look like this:RecordCount | CardNumber | CompanyID | AccessPriv1 | 1 | 82 | all2 | 2 | 82 | level 13 | 2 | 84 | all4 | 1 | 82 | noneThe table is transactional, so old records will not be flushed even thoughnew records contain the most current data (records 1 and 4 in this case). I'm wondering if there's a way to do a SQL SELECT query that, as it goesfrom the beginning to the end of the table, overwrites previous recordswhen later CardNumber's and CompanyID's match the previous records. So inthis case, the query would only return rows 2-4 because record 4'sCardNumber and CompanyID match record 1's.I know this is possible with application logic, but is it possible with anSQL query?Thanks.
Hello, Any help would be greatly appreciated. I have a single row that looks like this. Cust, Add, Item, Value 1 ST 258 6 I want to return six rows based on the value and the value could be any number. All of the row information will stay the same except the Value that will count off of the original value. Cust, Add, Item, Value 1 ST 258 1 1 ST 258 2 1 ST 258 3 1 ST 258 4 1 ST 258 5 1 ST 258 6
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like
Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK'
now can i make a function which returns 'NL','UK','IN','GN','JK'
I have read a lot in favor and recommendation of returning multiple resultsets. But now I have to implement it with a scenario. I have a Parent Table named "Books" and a Child one named "Volumes". A book can have multiple volumes. Now I want to display the list of Books with their Volumes pagewise. How can I implement that procdure.
Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:
This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?
Hi, We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int), As Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Return End
This Works Fine :- And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters() Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int) As Begin Declare @cnt Int Set @cnt = 1 While @cnt <= 2 If @cnt =1 Begin Insert @ki Select Names,count(distinct Chremail) As From Customer Where Chremail Is Not Null Set @cnt = @cnt + 1 End If @cnt =2 Begin Insert @k2 Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B On A.intcustomerid = B.intcustomerid Where Chremail <> ' ' And Chremail Is Not Null And Intpreferenceid In (6,7,2,3,12,10) Set @cnt2 = @cnt2 + 1 End End Return End
Can We Return Two Tables Or Is It Not Possible ? Pls Chk Into This And Tell Me.
I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
I am creating a report from a stored procedure that returns multiples record sets. For an example, my stored procedure is as follows
CREATE PROCEDURE MYPROCEDURE AS
BEGIN SELECT * FROM TABLE1 SELECT * FROM TABLE2 SELECT * FROM TABLE3 END
Now lets say I want to create a report that will display the result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How can I do this? Is there any way to bind the second record set to the report dataset with out changing the stored procedure? I will appreciate any kind of suggestions on this Thanks Moim
I would like to know if it's possible to return a single record by joining the tables below. [Persons] PersonID [int] | PageViewed [int] =============== ================= 1 10 2 5 3 2 4 12
[PersonNames] - PersonID JOINS Persons.PersonID PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int] =============== ============== ======================= =================== 1 1 Samantha Brown 5 1 2 Samantha Green 10 2 3 Richard T 10 3 4 Riko T 0 4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes AltNameID [int] | AltNames [nvarchar] ================ ============================= 1 Sam, Samantha, Sammie, Sammy 2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID] NameID [int] | AltNameID [int] ============= ================ 1 1 4 1 3 2 This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch ========= ============ ================= ================= 1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy 2 5 Richard T Richard T 3 2 Riko T Riko T, Riko, Rico 4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field. Any help would be greatly appreciated. Thanks, Pete.
I want to send 1 email with all clientname records which the cursor gets for me. My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help. I ano table to understand here about the while if right. thanks. +++++++++++++++++++++++++++++++++++++++++ CREATE PROCEDURE test1 AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC for
select client_name from clients ------------------------- -- now prepare and send out the e-mails declare @ToEmail varchar(255) declare @FromEmail varchar(255) declare @Subject varchar(255) declare @Body varchar(2000) declare @UserID numeric(38) declare @UserName varchar(255) declare @SMTPServer varchar(100) set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor fetch next from myCursor into @clientName
--loop now: while (@@fetch_status=0)
begin -- while(@@fetch_status=0) -- check if valid "To" e-mail address was found if ((@clientName is null) or (ltrim(@clientName) = ''))
begin --should not come here anytime ideally set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = 'was emailed to wrong person' set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = '-testing' set @Body =
'this will send ClientName:'+ @clientName end --end else
-- send the e-mail --exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer --fetch next from myCursor into @clientName
fetch next from myCursor into @clientName
end --while(@@fetch_status=0) exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer close myCursor deallocate myCursor
