getting a store procedure's result

Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,

Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

Getting a pool timeout error

I have pages which are using a master page.

An example page on my site would be the homepage, it makes 3 connections to mssql.

1) Get the keywords of the page

2) Get a list of news articles

3) Get the content of the page.

When I visual web express to debug the site it's giving me a pool error message, neither can I get the site to load directly via IIS. It says theres been a pool timeout.

I've read on the internet about making sure connections are closed when you are finished and I've checked that all database connections are closed using

finally
{
conn.Close();
}

does anyone have any idea why I would be having this problem?

How can I see what connections the site is opening, or maybe theres a limit on my server?

I'm using my own test server running windows 2003 and IIS

Don't increase the connection timeout unless there is a REAL need (15 sec by default).

Here are good links will help you hopfully:

http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

http://www.15seconds.com/issue/040830.htm

http://kb.seekdotnet.com/ViewArticle.aspx?ID=35

Good luck.

|||

As far as I can see I have NO leaking connections.

And I'm sure this is relating to the bug in visual studio because this happens when I try to access the page directly via IIS.

Does anyone else have any ideas?

Is there some way I can view connections and their state when I'm debugging the site?

|||

Apply the latest service pack.

Yes, you can know the status by using one of the connection object proerity (con.status or something).

Good luck.

|||

Checkout this link:http://geekswithblogs.net/chrishan/archive/2007/07/18/114030.aspx

I hope it will help you.

Good luck.

|||

Thanks, I dont seem to be any further forward though.

One thing I have noticed is that in the Output window of Visual Studio I get the following error repeated constantly.

"A first chance exception of type 'System.Data.SqlClient.SqlException'' occurred in System.Data.Dll"

|||

Surely someone can help with this?

Like I say, I don't believe this problem is with Visual Studio, as it occurs when I run the site directly on the server.

Here is the procedure which seems to be causing the problem, when I dont include this function the site runs fine, however when I include it it wont run and I get the error in my above post in the output window.

1protected void setConfigKeywords()2 {3// Define data objects4 SqlConnection conn;5 SqlCommand comm;6 SqlDataReader reader;78// Read the connection string from web.config9string connectionString = ConfigurationManager.ConnectionStrings["AWT"].ConnectionString;1011// Initialise the connection12 conn =new SqlConnection(connectionString);1314// Create command15 comm =new SqlCommand("SELECT * FROM Config WHERE ConfigID = 1", conn);1617try18 {19// Open the connection20 //conn.Open();2122 // Execute the command23 reader = comm.ExecuteReader();2425while (reader.Read())26 {27string title = reader["ConfigMetaTitle"].ToString();28string keywords = reader["ConfigMetaKeywords"].ToString();29string description = reader["ConfigMetaDesc"].ToString();30 }3132//Page.Title = title;33 //HtmlHead head = (HtmlHead)Page.Header;34 //Cls_Meta.setHeaderInfo(head, description, keywords);3536 // Close the reader37 reader.Close();3839 }40catch (Exception ex)41 {42string errorPage = ConfigurationManager.AppSettings["errorPage"];43string errorMsg ="Problem getting general keywords from database : " + ex;44 Server.Transfer(errorPage +"?errormsg=" + errorMsg);45 }46finally47 {48// Close the connection49 conn.Close();50 }51 }

getting a date in the past

Hi,
Does anyone knows the select syntax for getting a date in the past but
close the current date.
For example: i have a table of addresses with an id, startdate, street,
etc. Now what i would like to do, is get the date that is close to the
current date. The outcome of it, is the current address of a person.
Is this possible with use of the columns id and startdate or just startdate?Please post DDL if you are refering to columns in your tables
http://www.aspfaq.com/5006
Select TOP 1 <columnlist>
>From SomeTable
Where id = <Someid>
Order by Startdate desc
HTH, Jens Suessmeyer.|||select DATEDIFF(dd, StartDate, getdate()), * from YourTable
order by DATEDIFF(dd, StartDate, getdate())
dd = Days. This can be substitued for hours, minutes, seconds etc. Have a
look at DATEDIFF function in SQL Books Online
HTH. Ryan
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:%23Spc$gZIGHA.1876@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does anyone knows the select syntax for getting a date in the past but
> close the current date.
> For example: i have a table of addresses with an id, startdate, street,
> etc. Now what i would like to do, is get the date that is close to the
> current date. The outcome of it, is the current address of a person.
> Is this possible with use of the columns id and startdate or just
> startdate?

getting a out of the database

I know this is possible but I am having a foggy brain moment, I need tobe able to retrieve an ' out of the database for example the nameO' Grady or O' Neil programaticaly. So would someone please throwme a bone and point me in the right direction?
You shouldn't have any trouble. It's the same as any other character.
SELECT someFieldContainingAnApostrophe FROM someTable
Where exactly are you having trouble?
|||for example I have a file saved with the title O'Connor M M 08-08-05.html the results I get with the program would be O.
I may have to do some further investigating but at this point the only results that fail are those similar to above

|||You will have to do more investigating. SQL Server in itself is not truncating your data.
|||I don't know about truncating the data but quoted identifiers are ANSI SQL and when you have the option turned on SQL Server follows the ANSI SQL 1992 guidelines. The following text is from the BOL (books online). Hope this helps.



Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON when they connect. DB-Library does not set QUOTED_IDENTIFIER ON by default. Regardless of the interface used, individual applications or users may change the setting at any time. SQL Server provides a number of ways to specify this option. For example, in SQL Server Enterprise Manager and SQL Query Analyzer, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER, the quoted identifier option of sp_dboption, or the user options option of sp_configure.

When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and the single quotation mark (') in SQL statements:

Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; this practice is not recommended.

Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:

SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'

|||Thank you all for you help I guess I am going to have to do someadditional testing of my code, and investigate furthure funny how alittle ' can caause such a big pain :-D I will monititor the forums ifanyone has some experience with this particular problem.

Thanks
|||Try the link below for possible solution I think the trick is to use double single qoutes like 'O''Neil' instead of just O'Neil. Hope this helps.
http://www.aspfaq.com/params.htm|||

rkwalters wrote:

I will monititor the forums ifanyone has some experience with this particular problem.

It's not that we don't have the experience and capability to help you,it's that you haven't supplied enough information for us to be able tohelp, nor have you provided any code. So we are guessing blindly.
Do you know exactly where the problem is happening?
Are you SURE the expected data is making it into your database?
Are you SURE the data is not being extractly correctly?

|||If you look at the data directly in your DB what do you see? I have a feeling you are trying to put the field (O'grady) into something else. Can you show your entire SQL statement you are using to pull and display the field contents?
Nick|||Nick the data is indeed in the DB, below I have posted some datafrom a query from SQL Server directly, the problem must lay inside theC# code I have written to populate the actual string. So I haveincluded the code as well as the stored proc's that I use. Hopethat gives you enough information.
The ouput of the sample data is as follows when the code is ran.
O'
Coleman Garry Carl 08-08-05.html
Sample of the data in the rl_resume table:
resume_id cd_key stored link
810 9644 O'Connor Petrina M08-08-05.html
788 9622 Coleman Garry Carl 08-08-05.html
C# code to get the data back for populating a string:
private string getResumes(int cdKey)
{
string cdResume="";
try
{

SqlConnection tConn = newSqlConnection(dbConnection);

SqlCommand SqlCmd = tConn.CreateCommand();

SqlCmd.CommandType =CommandType.StoredProcedure;
SqlCmd.CommandText = "ml_GetResumeLink";
SqlCmd.Parameters.Add("@.cd_key", cdKey);
tConn.Open();
SqlDataReader tSqlDr= SqlCmd.ExecuteReader();
while(tSqlDr.Read())
{
cdResume=tSqlDr.GetSqlString(0).ToString();
}
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
return cdResume;
}
}
stored procedure to retrive the data:
CREATE PROCEDURE [dbo].[ml_GetResumeLink]
(
@.cd_key BIGINT
)
AS
SELECT
cd_link
FROM
rl_resumes
WHERE
cd_key=@.cd_key
GO
Stored procedure to poputlate the data:
CREATE PROCEDURE [dbo].[rl_InsertCustomerDetails]
(
@.cd_am_key BIGINT,
@.cd_upload_date DATETIME,
@.cd_fname VARCHAR(50),
@.cd_lname VARCHAR(50),
@.cd_address1 VARCHAR(100),
@.cd_address2 VARCHAR(100),
@.cd_city VARCHAR(50),
@.cd_state VARCHAR(50),
@.cd_phone1 VARCHAR(50),
@.cd_phone2 VARCHAR(50),
@.cd_fax VARCHAR(50),
@.cd_mobile VARCHAR(50),
@.cd_email VARCHAR(50),
@.cd_notes VARCHAR(250),
@.cd_marketupdates BIT,
@.cd_ignore_flag BIT,
@.cd_zip VARCHAR(10),
@.cd_link VARCHAR(150),
@.my_Ident BIGINT

)
AS
INSERT INTO cd_customer_data
(
cd_am_key,
cd_upload_date,
cd_fname,
cd_lname,
cd_address1,
cd_address2,
cd_city,
cd_state,
cd_phone1,
cd_phone2,
cd_fax,
cd_mobile,
cd_email,
cd_notes,
cd_marketupdates,
cd_ignore_flag,
cd_zip
)
VALUES
(
@.cd_am_key,
@.cd_upload_date,
@.cd_fname,
@.cd_lname,
@.cd_address1,
@.cd_address2,
@.cd_city,
@.cd_state,
@.cd_phone1,
@.cd_phone2,
@.cd_fax,
@.cd_mobile,
@.cd_email,
@.cd_notes,
@.cd_marketupdates,
@.cd_ignore_flag,
@.cd_zip
)
SET @.my_Ident = SCOPE_IDENTITY()

INSERT INTO rl_resumes
(
cd_key,
cd_link
)
VALUES
(
@.my_Ident,
@.cd_link
)
GO
|||Ok in query Analyzer, if you run:
EXEC ml_GetResumeLink 9644
What do you see? If you see only O', then its a problem in SQL, if not, its a problem in your .NET code. Just trying to limit the issue.
I see you have a line: cdResume=tSqlDr.GetSqlString(0).ToString();
What are you doing with cdResume? have you stepped through the code to make sure you only get O'?
Nick|||This is what I get: O'Connor Petrina M 08-08-05.html
So I am sure that it has to be the code. I am adding the cd_resume tothe string http://myurl.com/webarea/ the result isthat a link is created and mailed out. All links work with theexception of those that have an O' in them. So I guess mynext step is to find out if there is a way within .net to actuallyretrieve and populate a string with that type of data.
cdResume=tSqlDr.GetSqlString(0).ToString(); maybe the wrongsyntax to use here, maybe a differnt sql reader function is theappropriate method.
Any thoughts on that?


|||

In your HTML, do you happen to have
"<a href='http://myurl.com/webArea" & cdResume & "'>"
by any chance? Recognize that above is using single quotes around the href instead of double qoutes.
Nick

|||the cdResume value is passed to another method and assigned to theappResume variable. below is the code that adds the appResume tothe string.
myString.Append("<a href='http://myurl.com/content/" + appResume + "'>");

Russ
|||OK thats the problem. Think about how that will look when you put in O'connor (or whatever it was)
<a href='http://myurl.com/content/o'connor.html'>
If you notice, there are now 3 single quotes, so the HTML thinks that the second single quote was the end of the URL. Try:
myString.Append("<a href="http://links.10026.com/?link="http://myurl.com/content/" + appResume + """>");
This should work for you.
Nicksql

getresourcecontents

Could someone give me an example of displaying reporting services resources via a treeview using the getresourcecontents method?

Sorted this now

Dim rs As New ReportingService
Dim myByteArray() As Byte
myByteArray = rs.GetResourceContents(strResourcePath, "application/vnd.ms-excel")
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment; filename=Myxls.xls")
Response.BinaryWrite(myByteArray)

geting the UNIQUENAME fom a string

Does anyone have an idea how to get the UNIQUENAME of a member from ther dimesion date given a determined value.

For example in Adventure Works

Val: 2004

UNIQUENAME: [Date].[Calendar].[Calendar Year].&[2004]

Thanks!!

Here is an example showing how to retrieve the unique member name or the key value associated with the current member:

WITH

MEMBER MEASURES.[UniqueName] AS

[Date].Calendar.CurrentMember.UniqueName

MEMBER MEASURES.[KeyValue] AS

[Date].Calendar.CurrentMember.Properties("Key")

SELECT

{[Date].[Calendar].[Calendar Year].&[2004]} ON COLUMNS,

{MEASURES.[UniqueName],MEASURES.[KeyValue]} ON ROWS

FROM [Adventure Works]

HTH,

- Steve

Getdate() with no time associated

Is there a command that will let me set getdate() a in a smalldatetime field so that the there is no time associated with it?

For example, I have a table that I want to load the date a user does an action. If I use getdate() I'll get a value such as 5/25/2006 08:26:56.340, whereas I would just like a value 5/25/2006.

I can work it out by doing the following: select (datename(month,getdate())+'-'+datename(day,getdate())+'-'
+datename(year,getdate()))

However it seems to me that there should be a simpler way.well, i dunno if it's simpler, but this is a lot more efficient --

dateadd(d,datediff(d,0,getdate()),0)|||Towards the bottom of this article is an explanation on the why and how :)

EDIT - how about I post the article link eh?
http://www.sql-server-performance.com/fk_datetime.asp|||That does seem more efficient (I knew there had to be a better approach). And thanks for the link to the article.|||fabulous link, pootle, thanks

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

get the list of records for last registered emails

hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanksTry:
select
count (*)
, email
, max (regdatetime) regdatetime
group by
email
having
count (*) > 1
order by
regdatetime desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanks|||Tom it works perfect ...
thanks for your help
"Tom Moreau" wrote:

> Try:
> select
> count (*)
> , email
> , max (regdatetime) regdatetime
> group by
> email
> having
> count (*) > 1
> order by
> regdatetime desc
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
> hello,
> i have a table for example mytable with 2 fields
> email (varchar50) regdate(datetime)
> i want to have a list of emails which are more times registered - sort by
> last time when registered
>
> example of entries in the table
> u1@.dom1.com 26.03.2006 15:12:02
> u2@.dom1.com 24.03.2006 15:12:02
> u3@.dom1.com 24.03.2006 14:12:02
> u1@.dom1.com 23.03.2006 13:12:02
> u2@.dom1.com 22.03.2006 12:12:02
> u1@.dom1.com 21.03.2006 11:12:02
> u2@.dom1.com 20.03.2006 12:12:02
> u2@.dom1.com 19.03.2006 12:12:02
>
> i want to get something like
> 3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
> time
> 4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
> u3 - is not listed because it is only one time registered
>
> the information is sort desc by last registration time
> Yes i know what you think about the "tabledesign..." but my customer has
> such a table - and he asked me for that information:The information what
> will result is then inserted in a new table...
> thanks
>

Get some data out of the Northwind db

I'm trying to connect my sql server 2000. Wich components do i need to get, for example, the content in the orders db in the Northwind db.All you need is the classes located in the System.Data.SqlClient namespace. Here is an example on MSDN that gets a list of categories from the Categories table in Northwind:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassctortopic.asp

Get recordset result in variables

Hello folks.
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:

> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>

Get only single row results per id?

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.

For example i have an Articles table, and a PicturesForArticles table.

The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).

And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)

Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.

So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.

Is there any way to do this, to only return on row of results for each aid?

Thanks

That's very similar to something that I discussed in one of my articles on SingingEels :http://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx

Basically, you can use the "SubSelect" method I did under the "Joining The Tables Together" subheading.

Let me know if you need more help with this, if not, then please mark this post as the answer.

Thanks,

|||

Did you mean the first query after the subheading?

If so then how can i select more than one field from the derived table, do i need another derived table?

|||

At the end of the article I show how to get more than one field from a 'vertical' table, but grouped by a certain criteria (in my case "CustomerID")

SELECT
Customers.*,
LatestPayments.*
FROM
dbo.CustomersLEFTOUTERJOIN
(SELECT CustomerID,MAX(ID)AS LastPaymentID,MAX(PaymentDate)AS LastPaymentDate
FROM dbo.PaymentHistoryGROUPBY CustomerID) LatestPayments
ON Customers.ID = LatestPayments.CustomerID

That's the code fromhttp://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx that shows how to do that... if you are having troubles modifying it to your needs, then please past your table definitions and I'll change it to work for you.

Thanks,

|||

Ok thanks