Parameter direction of a stored procedure

I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcRetDbl]

@.Threshold real, @.Result real OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SELECT @.Result = Channel1 FROM DataTable WHERE Channel2 < @.Threshold
END

But then when I look at the properties of the @.Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.

I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.

KamenYou are probably thinking too hard :)

You need to supply a parameter in to give you something to read when the value comes out. I can't remember what I use for sqlParameters - probably inputoutput. Try both - what have you got to lose?|||The only pure output from a stored procedure is the return code value and any result sets. Procedure parameters must be input, and can optionally be output too.

-PatP

Paging query

I have created a stored proc for paging on a datalist which uses a objectDataSource.

I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.

CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
@.itemCount INT OUTPUT

)
AS

SELECT @.itemCount= COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID

Declare @.startRowIndex INT;
Declare @.finishRowIndex INT;
set @.startRowIndex = ((@.pageIndex -1) * @.numRows) + 1;
set @.finishRowIndex = @.pageIndex * @.numRows

DECLARE @.tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),
ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)

INSERT INTO @.tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)
SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage,
CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weight
FROM CMRC_Products INNER JOIN
CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryID
WHERE (CMRC_Products.CategoryID = @.categoryID) AND (CMRC_Products.isActive = 1)

SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight
FROM @.tCat
WHERE TID >= @.startRowIndex AND TID <= @.finishRowIndex
GO

spawned:

My output param works fine if I comment out all the other select statements. But returns null with them.

Strange! Other select statements should not effect the output param as non of them refereneces the output param. How did you get the output param's value after executing the sp? Will the output param work fine if you directly execute the stored procedure in SQL via Query Analyzer (or Management Studio) without commenting out some statements?

|||

You are correct, I tested in Query Analyser and the Output was returned OK.

The issue was that the method was returning a DataReader along with a output param. The two don't get along! You have to close the DataReader before the output param is visible. This is 'by design' accourding to MS.http://support.microsoft.com/?id=308051 (See resolution section). Moving to the end of the recordset didn't seem to work but close it does.

This was a real pain as I had to change the method to Read the data into a list<Product> then close the Reader, then set reference to my output param.

Anyway all done now and works a treat. Thanks for you advice.

|||

Then how about returning the count as a result set instead of putting it in the output parameter? I mean you can write your stored procedure this way:

CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
)
AS

SELECT COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID

//do other things

go

And then in the code you can get the count from the first result set using SqlDataReader.

|||

Ok, I thought only DataSets alowed that (ie Tables[0], Table[1] etc, or does the Reader just return it at the end of the recordset.

I'll have a play around with it. Thanks for the tip.

Cheers,Shaun.

|||

I am having exa ctly the same problem.

You mentioned that you used a method to Read the data into a list<Product> then close the Reader, then set reference to my output param. This is exactly what i am trying to do now could you tell me the code for this.

many thanks

martin

|||

In my Product.cs I have the following new method:

//Used for paged results in catagory searchpublicvoid ProductList(int ProductID,string Medium,string ModelName,string ProductImage,double UnitCost,bool IsAdult)

{

_ProductID = ProductID;

_Medium = Medium;

_ModelName = ModelName;

_ProductImage = ProductImage;

_UnitCost = UnitCost;

_IsAdult = IsAdult;

}

***************************

In my CatalogManager .cs (I didn't put it in the provider project as it is a pain in the ar*e to maintain) I have the following:

public

staticList <Product> GetProductsByCategoryPaging(int categoryID,int pageIndex,int numRows,outint itemCount)

{

using (SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["CommerceTemplate"].ConnectionString))

{

using (SqlCommand command =newSqlCommand("CMRC_PRODUCTS_GetListByCategoryPaging", connection))

{

command.CommandType =

CommandType.StoredProcedure;

command.Parameters.Add(

"@.itemCount",SqlDbType.Int, 4);

command.Parameters[

"@.itemCount"].Direction =ParameterDirection.Output;

command.Parameters.Add(

newSqlParameter("@.categoryID", categoryID));

command.Parameters.Add(

newSqlParameter("@.pageIndex", pageIndex));

command.Parameters.Add(

newSqlParameter("@.numRows", numRows));

connection.Open();

//populate listList<Product> list =newList<Product>();using (SqlDataReader rdr = command.ExecuteReader()) {while (rdr.Read()) {Product temp =newProduct();

temp.ProductList(

(

int)rdr["ProductID"],

rdr[

"Medium"].ToString(),

rdr[

"ModelName"].ToString(),

rdr[

"ProductImage"].ToString(),Convert.ToDouble (rdr["UnitCost"]),

(

bool)rdr["IsAdult"]);

list.Add(temp);

}

rdr.Close();

}

itemCount = (

int)command.Parameters["@.itemCount"].Value;return list;

}

}

}

You'll need to include the this declaration in the CatalogManager:

using System.Collections.Generic;

Good luck I feel you pain :-)

|||

really, many thanks for that it was driving me crazy

cheers

martin

|||

Once again many thanks for your help with this. Everhting is working fine.

Just one question did you use querystrings and if you did, how did you validate

them. I am having a lot of problems trying to figure out how to do this.

many thanks for all your help

martin

|||

Yes I did use query strings. My catalog page handles many queries.

I had a switch statement in the Page Load.

switch (SearchType) <== I have a param for query type.

{

case"c"://category search

cid =

Convert.ToInt32(Request.QueryString["cid"]);

dlCatalog.DataSource = objDSCategory;

dlCatalog.DataBind();

break;case"a"://artist search

dlCatalog.DataSource = objDSArtist;

dlCatalog.DataBind();

break;

etc

}

in the obj DataSource in aspx:

<

asp:ObjectDataSourceID="objDSGallery"runat="server"OldValuesParameterFormatString="{0}"SelectMethod="GetProductsByGalleryPaging"TypeName="CatalogManager"OnSelected="objDSGallery_Selected"OnSelecting="objDSGallery_Selecting"><SelectParameters><asp:QueryStringParameterName="GalleryID"QueryStringField="gid"Type="Int32"/><asp:QueryStringParameterName="pageIndex"QueryStringField="PageIndex"DefaultValue="0"/><asp:QueryStringParameterName="numRows"QueryStringField="NumRows"DefaultValue="6"/><asp:ParameterName="itemCount"Direction="Output"Type="Int32"/></SelectParameters></asp:ObjectDataSource>

Let me know if you have any more dramas, I can email you the code I'm using for reference.

The OnSelected and Selecting methods are used for updating the paging links.

I can email you the code if you like.

Cheers,Shaun.

|||

thanks for your reply

i would be grateful for the code my address istbcmartingharvey@.yahoo.co.jp

many thanks

martin

|||

Is your Yahoo account still active? got a return reply saying:

554 delivery error: dd This user doesn't have a yahoo.co.jp account (tbcmartingharvey@.yahoo.co.jp)

Is that your correct mail address?

Cheers,Shaun

ps I'll be away for several days (biz not pleasure :-( ) so will not get the code to you till Tues. Probably best I provide it as a download from my server so other users can access it too.

|||

thanks for your reply shaun

must be the summer heat

itstbcmartinharvey@.yahoo.co.jp

thank you

martin

Paging problems

Hi
I am consistently having problems getting paging to work in the report
viewer. I just do a simple query with multiple columns output with
more rows than that will fit in a simple page and setup the report
using the report wizard. I run the report in report manager and all
rows in the table are listed as one huge page.
I have had this problem a lot only in 2005 reporting services. I found
out about the interactive size property and it is set to the report
page size which is 8.5 x 11. So, I dont really understand why the
paging wouldnt work.
Since the result has around 15000 records, the whole thing renders in
one shot and IE literally takes over my computer.
Please help.
ThanksHi,
Check two things.
a. Is your width of the report is not bigger than the width of the report.
b. Go to "View code" and go last you can see somthing like "Interactive
Height" make it "11in" if it is "0in" or go to report layout and after the
dots, any emty space right click and properties change the interactive
height to 11in.
second thing is you said you have done it. but try changing the code and see
it should work and it was working for me on a sameple report which is about
10000 rows.
Amarnath
"Sathya" wrote:
> Hi
> I am consistently having problems getting paging to work in the report
> viewer. I just do a simple query with multiple columns output with
> more rows than that will fit in a simple page and setup the report
> using the report wizard. I run the report in report manager and all
> rows in the table are listed as one huge page.
> I have had this problem a lot only in 2005 reporting services. I found
> out about the interactive size property and it is set to the report
> page size which is 8.5 x 11. So, I dont really understand why the
> paging wouldnt work.
> Since the result has around 15000 records, the whole thing renders in
> one shot and IE literally takes over my computer.
> Please help.
> Thanks
>

PageNumber in XML output??

Hello,
Is there a way to get information (page number) in the Page Footer to appear
as an Element in an XML export?
Thanks
David BowsI tried to set these properties but they seem not to render when they are in
the Page Footer. They work fine in the body, but I cannot use
Globals!PageNumber in the body of a report only the Page Footer. What I
want is to be able to save the report to PDF then export to XML and then
write a quick app that will go through the XML and build a table of
contents. Seems straight forward, but cannot get any XML from the Page
Footer to export.
Any help would be appreciated.
David
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:E1A812C9-C9A0-47A1-BC25-F01AC7CE7C08@.microsoft.com...
> If the advanced properties for the textbox item UnderData Output you can
> render as an Element... You can also name the element.
> Is this what your looking for?
> Leo
> "David Bows" wrote:
> > Hello,
> >
> > Is there a way to get information (page number) in the Page Footer to
appear
> > as an Element in an XML export?
> >
> > Thanks
> > David Bows
> >
> >
> >