How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
> This paramter has to take value from table which have column with
> datatype as text.
> How will i set value to parameter having text datatype to value
> present in table?
You would just SELECT it, not set it to a variable. You can't have a
variable of type TEXT. And you can't RETURN anything other than an INT, so
I assume you meant OUTPUT, not RETURN.
In SQL Server 2005 you can use VARCHAR(MAX) which is a first-class
citizen -- meaning you can DECLARE @.foo VARCHAR(MAX) and proceed with
storing 2GB of text in there if you want.
A|||> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

How to return text data type from stored procedure.

Hi all,
I am having one stored procedure which is returing parameter having
text data type.
This paramter has to take value from table which have column with
datatype as text.
How will i set value to parameter having text datatype to value
present in table?
Any help will be truely appreciated.> I am having one stored procedure which is returing parameter having
> text data type.
You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.
And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

How to return all records when date filter parameter is missing

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date

I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.

I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of

select * from table where (MONTH([AD ENDS]) = @.month)

MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?

Diane

HI Diane,

It think there are two options that might be better than having two objectdatasources.

1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}

2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)

Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0

|||I don't know how you can get all records without send anything in with Select * from table wherelast=@.last

But I would try to use Select * from table where last=ISNULL(@.last,last).

For the month query,please try something like this:

WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))

|||

Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?

select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?

or

select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?

Diane

|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||

Thank you Dinakar! That worked!

Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.

Diane

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

How to retrieve large string from stored procedure?

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.
Have you tried using a C# string datatype?|||Hello! Thanks for the reply. But for do you mean C# string data type here? Hmm... Let me show some codes here to clarify the question. :-)

OleDbParameter prm_Xml = new OleDbParameter("@.Xml", OleDbType.VarWChar, -1);

prm_Xml.Direction = ParameterDirection.Output;

OleDbParameter's constructor needs a OleDbType enumerator for second parameter. VarWChar has a limitation for 4K characters. If I change that to LongVarWchar, then will have a runtime error...
|||

Take a look at this link - http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

Hope this helps

|||Raj, thanks for your prompt reply. Sorry for the late reply.

The link you sent does give me a clear picture of how to store and retrieve data that larger than 8K. But I still wonder whether there is a way to get the data as a output parameter of a stored procedure in OLEDB way.
|||

I think there is an example in the link that uses stored procedure with output parameter and gives VB/C# sample to retrieve it - Was that not helpful?

Take a look at - http://www.dotnet247.com/247reference/a.aspx?u=http://support.microsoft.com/?kbid=317016 and

http://www.dotnet247.com/247reference/a.aspx?u=http://www.kbalertz.com/Feedback_308049.aspx

Hope this helps

How to retreive set of rows from stored procedure

hi,

i am new to SQL. i have created a stored procedure which gets a input parameter "Category" and it selects datas which falls under this category. when i run this procedure it returns only the last row. it doesnt retreive the entire set of rows. which method should i follow to solve my problem.. i want all the rows which comes under the category to be returned ....

MY PROCEDURE

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10),

@.ProductID Char(10) OUTPUT,

@.Name Char(50) OUTPUT,

@.UnitPrice Numeric(9) OUTPUT,

@.Stock Numeric(9) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;

SELECT @.ProductID=ProductID,

@.Name=Name,

@.UnitPrice=UnitPrice,

@.Stock =Stock

From

ProductDetails

Where

Category=@.Category

END

As you're using output parameters, you can only have one value per parameter and this will be set to values in the last row returned by your query.

To return a recordset of multiple values, try:

CREATE PROCEDURE [dbo].[Items_Category_sorted]

(

@.Category varchar(10)

)

AS

BEGIN

SET NOCOUNT ON;

SELECT ProductID,

Name,

UnitPrice,

Stock

From

ProductDetails

Where

Category=@.Category

END

Hope this helps!