noLoveLusT wrote:
hi everyone i am very very new to the sql server (2 days actually and
) so far i learned creating SPs etc but couldnt workout how to get
return value from my prodecure
my sp as follows
________________________
CREATE PROCEDURE [dbo].[page_all_artists]
@PageIndex INT,
@PageSize INT,
@Total INT OUTPUT
AS
BEGIN
The first line here should be:
SET NOCOUNT ON
to prevent the spurious "x rows effected" messages from being returned to
the client as closed recordsets
WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY ArtistID asc)
AS Row, Artist, SongCount
FROM artists_table)
SELECT Row, Artist, SongCount
FROM Entries
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
-----------------THIS ONE TO GET TOTAL RECORD COUNT AND I HAVE NO
IDE HOW IT SHOULD WORK---------------------
Select @total=Count(ArtistID) from (Select ArtistID From Entries e
group by e.ArtistID) b;
I've never used CTEs ... is Entries still available at this point? Does this
procedure do what you want in Query Analyzer? Test it like this:
DECLARE @total int
EXEC page_all_artists '20060101',25, @total output
select @total as Total
<snip>
You need to use an explicit Command object, appending Parameter objects to
its Parameters collection in order to retrieve the value of the output
parameter. Coding these Parameter objects is tedious and error-prone so I
created a utility page to do it for me. You can get it from:
http://common.mvps.org/barrowsb/Clas..._generator.zip
Using your procedure's declaration, this is the output of the code
generator:
Dim cmd, param
Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "page_all_artists"
set .ActiveConnection=cnSQL
set param = .createparameter("@RETURN_VALUE", adInteger, _
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@PageIndex", adInteger, _
adParamInput, 0, [put value here])
.parameters.append param
set param = .createparameter("@PageSize", adInteger, _
adParamInput, 0, [put value here])
.parameters.append param
set param = .createparameter("@Total", adInteger, _
adParamInputOutput, 0, [put value here])
.parameters.append param
.execute ,,adexecutenorecords
end with
This assumes you have the ADO constants defined either for the page or the
application - see
http://www.aspfaq.com/show.asp?id=2112
You would modify it as follows:
Dim cmd, param
Set cmd=server.CreateObject("ADODB.Command")
With cmd
.CommandType=adcmdstoredproc
.CommandText = "page_all_artists"
set .ActiveConnection=objConn
set param = .createparameter("@RETURN_VALUE", adInteger, _
adParamReturnValue, 0)
.parameters.append param
set param = .createparameter("@PageIndex", adInteger, _
adParamInput, 0, startFrom )
.parameters.append param
set param = .createparameter("@PageSize", adInteger, _
adParamInput, 0, perPage)
.parameters.append param
set param = .createparameter("@Total", adInteger, _
adParamOutput)
.parameters.append param
set objRS = .execute
'the procedure returns records so don't specify no-records
end with
The first step is to process and close the recordset so the output parameter
value will be retrieved. I typically do this by using a GetRows array:
dim arData
if not objRS.eof then arData = objRS.GetRows
objRS.close: set objRS = nothing
Then get the output parameter value - since that was the last parameter
defined, param still refers to it so:
dim total: total = param.value
If it wasn't the last parameter, then get it explicitly by:
dim total: total = cmd.parameters("@Total").value
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
