Outputting Data From Stored Procedure

[Jarrod Morrison]

Hi All

Im generally a vb programmer and am used to referencing multiple records
returned from a query performed on an sql database and im trying to move
some functions of my software into sql stored procedures. So far ive been
able to move the functions relatively easily but im unsure about how to
output multiple values from an sql stored procedure. By this i mean for
example one of the stored procedures may take your username and return the
contents of a single field in a record of one of the tables, but i would
like to be able to return for arguement sake the contents of a single field
from two records if possible. Under VB im used to referencing the recordset
with a (1) after it to reference the corresponding record from the query. I
was wondering if there is a way to do something similar to this with stored
procedures if possible ?

Thanks for any help

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.c om.au) writes:

Im generally a vb programmer and am used to referencing multiple records
returned from a query performed on an sql database and im trying to move
some functions of my software into sql stored procedures. So far ive
been able to move the functions relatively easily but im unsure about
how to output multiple values from an sql stored procedure. By this i
mean for example one of the stored procedures may take your username and
return the contents of a single field in a record of one of the tables,
but i would like to be able to return for arguement sake the contents of
a single field from two records if possible. Under VB im used to
referencing the recordset with a (1) after it to reference the
corresponding record from the query. I was wondering if there is a way
to do something similar to this with stored procedures if possible ?

Unfortunately, without an example, I find it difficult to get a clear
understanding of what you are doing.

However, a general word of caution: SQL is a very different language
than Basic with different mindsets. Translating VB into SQL line by
line, may not always be the best approach.

Since I don't know what you are doing, this link may be completely
irrelevant. But maybe it addresses something of what you are trying to
do: http://www.algonet.se/~sommar/share_data.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jarrod Morrison]

Hi Erland

Thankyou for the link, it sort of helped me a little bit, but I thought I
would give you a direct example of what I am doing to see if you can give me
any further pointers on what I can do. In my stored procedure I will execute
a query and store the results in a temporary table in the procedure. An
Example of what will be in the table is as follows:

PrinterPath Name Priority

\\Server1\Print er1 Printer 1 1
\\Server2\Print er2 Printer 2 2

What I want to do is to output only the first columns data from the
procedure to my vb client program (IE: \\Server1\Print er1 and
\\Server2\Print er2). Obviously this is a basic example and the amount of
data and records will change all the time. To begin with I only want to
output the first column, but if possible down the track I would also like to
do multiple columns if it is possible. At the moment in my other stored
procedures I am just defining a variable at the beginning of the procedure
as output and assigning a value to it before the procedure ends. I am hoping
there is an easy way to output multiple values from the stored procedure in
a similar fashion.

Thanks for all your help

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** *************@1 27.0.0.1...

Unfortunately, without an example, I find it difficult to get a clear
understanding of what you are doing.

However, a general word of caution: SQL is a very different language
than Basic with different mindsets. Translating VB into SQL line by
line, may not always be the best approach.

Since I don't know what you are doing, this link may be completely
irrelevant. But maybe it addresses something of what you are trying to
do: http://www.algonet.se/~sommar/share_data.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.c om.au) writes:

Example of what will be in the table is as follows:

PrinterPath Name Priority

\\Server1\Print er1 Printer 1 1
\\Server2\Print er2 Printer 2 2

What I want to do is to output only the first columns data from the
procedure to my vb client program (IE: \\Server1\Print er1 and
\\Server2\Print er2). Obviously this is a basic example and the amount of
data and records will change all the time. To begin with I only want to
output the first column, but if possible down the track I would also
like to do multiple columns if it is possible.

SELECT PrinterPath FROM tbl WHERE ...

SELECT PrinterPath, Name FROM tbl WHERE ...

Then in VB you receive the values in a recordset.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jarrod Morrison]

Hi Erland

Do you mean to do the select statement straight from the vb program ? Or
just plain do the select statement in the stored procedure on the sql server
and vb program will receieve it as a recordset ? I dont really understand
what you meant there as i was trying to do all the work in the sql stored
procedure if possible and just return the results

Thanks
"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Jarrod Morrison (ja*****@ihug.c om.au) writes:

Example of what will be in the table is as follows:

PrinterPath Name Priority

\\Server1\Print er1 Printer 1 1
\\Server2\Print er2 Printer 2 2

What I want to do is to output only the first columns data from the
procedure to my vb client program (IE: \\Server1\Print er1 and
\\Server2\Print er2). Obviously this is a basic example and the amount of
data and records will change all the time. To begin with I only want to
output the first column, but if possible down the track I would also
like to do multiple columns if it is possible.

SELECT PrinterPath FROM tbl WHERE ...

SELECT PrinterPath, Name FROM tbl WHERE ...

Then in VB you receive the values in a recordset.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Jarrod Morrison (ja*****@ihug.c om.au) writes:

Do you mean to do the select statement straight from the vb program ? Or
just plain do the select statement in the stored procedure on the sql
server and vb program will receieve it as a recordset ? I dont really
understand what you meant there as i was trying to do all the work in
the sql stored procedure if possible and just return the results

I was thinking of something like:

CREATE PROCEDURE your_sp AS
SELECT PrinterPath, Name FROM tbl WHERE...

I'm sorry if my answers are not fully clear, but I have a feeling that
there something hiding in your questions that I have not been able to
grasp.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Samuel Hon]

Hi

This is my interpretation

By this i
mean for example one of the stored procedures may take your username and
return the contents of a single field in a record of one of the tables,
1 - You have a fixed number of output parameters from the stored
procedure

Dim objConn as ADODB.Connectio n
Dim objCmd As ADODB.Command
Dim objRst As ADODB.Recordset
Dim objField As ADODB.Field

'Open your connection first

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Father ",
adVarChar, adParamOutput, 50)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Mother ",
adVarChar, adParamOutput, 50)

'Execute
objCmd.Execute

'Get your parameters back out
strFather = objCmd.Paramete rs("Father").Va lue
strMother = objCmd.Paramete rs("Mother").Va lue

2 - You need to retrieve a number of records from one field.
but i would like to be able to return for arguement sake the contents of
a single field from two records if possible. Under VB im used to
referencing the recordset with a (1) after it to reference the
corresponding record from the query. I was wondering if there is a way
to do something similar to this with stored procedures if possible ?

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)

'Execute
Set objRst = objCmd.Execute

'Get the field. For large recordsets, this is the best approach
'See http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=265
'You can use (1) here if you'd like
'Set objField = objRst.Fields(1 )
Set objField = objRst.Fields(" YourField")

'Get your values from the recordset
Do Until objRst.EOF
Debug.Print objField.Value
objRst.MoveNext
Loop

objRst.Close
Set objRst = Nothing
I hope this is what you're looking for

[Jarrod Morrison]

Hi Samuel

Thanks for the post, thats exactly what i wanted. But i have one further
question about the second part of the post, when i execute the select
statement inside the stored procedure it is actually part of a loop and thus
each select statement is being treated seperately by VB, so if i use query
analyzer i see for example 3 seperate returns which is ok because it is what
is meant to be returned, however vb is only reading the first one of these
returns and then closing the recordset. Im sure there is an easy way around
this but im unsure of what to do. Any help is greatly appreciated

Thanks

"Samuel Hon" <no*****@samuel hon.co.uk> wrote in message
news:c8******** *************** ***@posting.goo gle.com...

Hi

This is my interpretation

By this i
mean for example one of the stored procedures may take your username and
return the contents of a single field in a record of one of the tables,

1 - You have a fixed number of output parameters from the stored
procedure

Dim objConn as ADODB.Connectio n
Dim objCmd As ADODB.Command
Dim objRst As ADODB.Recordset
Dim objField As ADODB.Field

'Open your connection first

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Father ",
adVarChar, adParamOutput, 50)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Mother ",
adVarChar, adParamOutput, 50)

'Execute
objCmd.Execute

'Get your parameters back out
strFather = objCmd.Paramete rs("Father").Va lue
strMother = objCmd.Paramete rs("Mother").Va lue

2 - You need to retrieve a number of records from one field.

but i would like to be able to return for arguement sake the contents of
a single field from two records if possible. Under VB im used to
referencing the recordset with a (1) after it to reference the
corresponding record from the query. I was wondering if there is a way
to do something similar to this with stored procedures if possible ?

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)

'Execute
Set objRst = objCmd.Execute

'Get the field. For large recordsets, this is the best approach
'See http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=265
'You can use (1) here if you'd like
'Set objField = objRst.Fields(1 )
Set objField = objRst.Fields(" YourField")

'Get your values from the recordset
Do Until objRst.EOF
Debug.Print objField.Value
objRst.MoveNext
Loop

objRst.Close
Set objRst = Nothing
I hope this is what you're looking for

[Jarrod Morrison]

Hi Erland

Thanks for all your help with the stored procedure questions, Samuel replied
to the posts as well and gave me a good vb example to go with and now it
seems like it is all working well. Thanks again

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Jarrod Morrison (ja*****@ihug.c om.au) writes:

Do you mean to do the select statement straight from the vb program ? Or
just plain do the select statement in the stored procedure on the sql
server and vb program will receieve it as a recordset ? I dont really
understand what you meant there as i was trying to do all the work in
the sql stored procedure if possible and just return the results

I was thinking of something like:

CREATE PROCEDURE your_sp AS
SELECT PrinterPath, Name FROM tbl WHERE...

I'm sorry if my answers are not fully clear, but I have a feeling that
there something hiding in your questions that I have not been able to
grasp.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ellen K.]

Not sure I understand your question exactly, but it sounds like maybe
you need the ADO .NextRecordset method, which does just what it sounds
like, brings back the next recordset from your stored procedure.

On Sun, 14 Dec 2003 16:01:21 +1100, "Jarrod Morrison"
<ja*****@ihug.c om.au> wrote:

Hi Samuel

Thanks for the post, thats exactly what i wanted. But i have one further
question about the second part of the post, when i execute the select
statement inside the stored procedure it is actually part of a loop and thus
each select statement is being treated seperately by VB, so if i use query
analyzer i see for example 3 seperate returns which is ok because it is what
is meant to be returned, however vb is only reading the first one of these
returns and then closing the recordset. Im sure there is an easy way around
this but im unsure of what to do. Any help is greatly appreciated

Thanks

"Samuel Hon" <no*****@samuel hon.co.uk> wrote in message
news:c8******* *************** ****@posting.go ogle.com...

Hi

This is my interpretation

> By this i
> mean for example one of the stored procedures may take your username and
> return the contents of a single field in a record of one of the tables,

1 - You have a fixed number of output parameters from the stored
procedure

Dim objConn as ADODB.Connectio n
Dim objCmd As ADODB.Command
Dim objRst As ADODB.Recordset
Dim objField As ADODB.Field

'Open your connection first

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Father ",
adVarChar, adParamOutput, 50)
objCmd.Paramete rs.Append objCmd.CreatePa rameter("Mother ",
adVarChar, adParamOutput, 50)

'Execute
objCmd.Execute

'Get your parameters back out
strFather = objCmd.Paramete rs("Father").Va lue
strMother = objCmd.Paramete rs("Mother").Va lue

2 - You need to retrieve a number of records from one field.

> but i would like to be able to return for arguement sake the contents of
> a single field from two records if possible. Under VB im used to
> referencing the recordset with a (1) after it to reference the
> corresponding record from the query. I was wondering if there is a way
> to do something similar to this with stored procedures if possible ?

'Open command
Set objCmd = New ADODB.Command
objCmd.CommandT ype = adCmdStoredProc
objCmd.CommandT ext = "spGetParen ts"
Set objCmd.ActiveCo nnection = objConn

'Add your input and output parameters
objCmd.Paramete rs.Append objCmd.CreatePa rameter("UserNa me",
adVarChar, adParamInput, 8, strUserName)

'Execute
Set objRst = objCmd.Execute

'Get the field. For large recordsets, this is the best approach
'See http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=265
'You can use (1) here if you'd like
'Set objField = objRst.Fields(1 )
Set objField = objRst.Fields(" YourField")

'Get your values from the recordset
Do Until objRst.EOF
Debug.Print objField.Value
objRst.MoveNext
Loop

objRst.Close
Set objRst = Nothing
I hope this is what you're looking for