Returning a string from a sproc

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 a string from a sproc

Author

Topic

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 08:59:43

I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:

select
@error = @@error,
@row_count = @@rowcount

set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @outgoingXML

where @outgoingXML is an output parameter. And I have tried:

set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @serilializedReturnCode

where @serilializedReturnCode is locally declared as varchar(250)

This is the error I get:

Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-08 : 09:05:35

I don't think SPROCs can have other datatype than INT as RETURN argument.

See also this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71683

Peter Larsson
Helsingborg, Sweden

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 09:13:30

Thank you Peter. Makes sense as a regular return data type. But if I am declaring an output varchar parameter, shouldn't this be the way to do it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-08 : 09:15:26

Yes.
Look at the link I provided, and you will se how to do make it work.

Peter Larsson
Helsingborg, Sweden

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 09:25:06

Thanks again. I tried the output parameter (my first post) and testing within SQL Analyzer I get this error:

Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.

I am going to ignore thsi error for a second and see if the .NET code gets anything...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 10:40:13

That error is happening because you have the RETURN statement with the output parameter variable. To use the output parameter, you just declare it as output and then set its value in the sp. Don't put the RETURN in there.

RETURN is intended for passing something like a status code only, and can only return an int, it is not able to return a typed value.

Note that User Defined Functions can return specific types, so in a UDF, you would use RETURN with something like a string.

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 10:50:25

I just realized that. Thank you very much!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 10:59:12

quote:
Originally posted by meareal

I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following:

select
@error = @@error,
@row_count = @@rowcount

set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @outgoingXML

where @outgoingXML is an output parameter. And I have tried:

set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>'

return @serilializedReturnCode

where @serilializedReturnCode is locally declared as varchar(250)

This is the error I get:

Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.

FYI -- using XML like this is really overkill when you have a nice database that you are using.

What's wrong with simply having your stored procedure do this:

SELECT @Error as Error_Code, @Row_Count as Row_Count

Then your client gets a simple 1 row, 2-column result with both the ErrorCode and RowCount ready to go, no parsing, no converting to and from string, no need for output parameters, and so on.

I wouldn't use XML unless you really need to.

- Jeff

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 11:27:31

Thanks Jeff. I am not sure how I would read these two values from my app but I will give it a try...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:32:49

You read them just like you read values in any normal row, they would just be in a result set with one row.

For example in ADO.NET you'd ExecuteReader, then read the two column values in the one row in the Reader.

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 11:35:56

quote:
Originally posted by meareal

Thanks Jeff. I am not sure how I would read these two values from my app but I will give it a try...

I kept thinking about my response. I would still need to parse the result set (XML format)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 11:53:37

quote:
Originally posted by meareal

quote:
Originally posted by meareal

Thanks Jeff. I am not sure how I would read these two values from my app but I will give it a try...

I kept thinking about my response. I would still need to parse the result set (XML format)

Why do you need XML? Doesn't your client ultimately just want to know how many rows where affected and if there was an error?

If you just return the two values in a SELECT, to get the results, you just do:


int ErrorCode;
int RowCount;
SqlDataReader r = YourCommand.ExecuteReader();
if (r.Read())
{
  ErrorCode = (int) r["Error_Code"];
  RowCount = (int) r["Row_Count"];
}
r.close()

That's it. Now you have two variables set with the error code and the rowcount.

- Jeff

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 14:01:54

I believe the proper syntax is:

SELECT @@error as Error_Code, @@rowcount as Row_Count

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 14:21:37

quote:
Originally posted by meareal

I believe the proper syntax is:

SELECT @@error as Error_Code, @@rowcount as Row_Count

I was just copying your code and returning the variables that you were setting in your SQL. You can return the values any way you want, directly from the @@ variables or by storing them in local vars first and then returning them. This has nothing to do with the advice I am giving you.

- Jeff

meareal
Starting Member

21 Posts

Posted - 2006-09-08 : 15:03:35

I was just trying to be precise just in case someone else reads this thread. I really value all your help. In fact, I like the solution you provided. I tested it and it works!

Thank you.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 15:31:39

You're welcome, glad I could help.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources