Returning mutliple strings from stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning mutliple strings from stored proc

Author

Topic

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 02:39:41

Hey guys, could somebody pls provide me with an easy example as to how you would return multiple strings from a stored proc? Even a link to a decent tut would be great!

Muchos gracias!

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-05-14 : 02:43:24

Hi,
You can define an Output Parameter to Stored Procedure and set the Value of Output Parameter during Stored Procedure Execution.
Create Procedure OutputParam @OutValue1 varchar(20) output, @OutValue2 varchar(20) output
AS
Begin
Set @OutValue = 'Return Value 1'
Set @OutValue2 = 'Return Value 2'

End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 02:55:26

or Give us some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 02:56:28

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Peter Larsson
Helsingborg, Sweden

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 03:02:11

Ok here's a simple example.. perhaps you guys can help me. This example returns only a single value but perhaps you can tell me where im going wrong. Everytime I execute the following code, I get a "String[0]: the Size property has an invalid size of 0." error.

Heres the stored proc:

CREATE PROCEDURE sp_test

@output nvarchar(50) OUTPUT

AS

DECLARE @var1 int

BEGIN

SET @var1 = (SELECT COUNT(*) FROM tbl_dummy)

SET @output = CONVERT(nvarchar, @var1)

SELECT @output

END

GO

And heres the C# code that calls it:

sqlConn = new SqlConnection(connectionString);

sqlComm = new SqlCommand();
sqlComm.Connection = sqlConn;
sqlComm.CommandText = "sp_test";
sqlComm.CommandType = CommandType.StoredProcedure;

para = new SqlParameter();
para.ParameterName = "@output";
para.SqlDbType = SqlDbType.NVarChar;
para.Direction = ParameterDirection.Output;
sqlComm.Parameters.Add(para);

sqlConn.Open();

dr = sqlComm.ExecuteReader();

sqlConn.Close();
dr.Close();

//txtBoxResults is a just a textbox on screen to display results
txtBoxResults.Text = Convert.ToString(sqlComm.Parameters["@ouput"].Value);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-14 : 03:02:36

SET @output = CONVERT(nvarchar(50), @var1)

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 03:07:10

Thanks khtan, still seem to get the error though!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 03:07:44

Why would you like to return a number as a string?

Peter Larsson
Helsingborg, Sweden

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 03:11:43

yeah that was my intention.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 04:17:32

quote:
Originally posted by norty911

yeah that was my intention.

Why?
Do you want to make the things complicated than expected?

Madhivanan

Failing to plan is Planning to fail

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 04:22:20

Nah, it works fine when returning an int but seems to bomb out with the "String[0]: the Size property has an invalid size of 0." error message everytime I return a varchar or nvarchar. You see, I want the stored procedure to be flexible enough to return strings and ints.

How would you normally return a string then?

norty911
Starting Member

41 Posts

Posted - 2007-05-14 : 04:30:11

Found the problem! For future reference if anyone else receives the "String[0]: the Size property has an invalid size of 0." error message, you need to explicitly set the size property of the parameters variable eg:

para.Size = 50;

Thanks all for the help tho.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:06:40

If you used proper datatypes (as int for example) this problem would never happened.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources