[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to convert null to empty string in stored procedure?

Posted on 2006-04-28
2
Medium Priority
?
280 Views
Last Modified: 2012-06-27
I have the following stored procedure (pretty straight forward):

CREATE PROCEDURE usp_GetBioDemData
@uid      nvarchar(255)
AS
SELECT firstname, mid_name, lastname, maiden_firstname, maiden_lastname, maiden_middlename, mail_label, nickname FROM ALUM_UNIV_ID_V
      WHERE uid = @uid
GO

I am using the following C# code to retrieve the results:

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            //Get the first row.
            reader.Read();
            AlumniProfile ap = new AlumniProfile((string)reader["firstname"], (string)reader["lastname"],
                (string)reader["mid_name"], (string)reader["maiden_firstname"], (string)reader["maiden_lastname"],
                (string)reader["maiden_middlename"], (string)reader["mail_label"], (string)reader["nickname"]);
            reader.Close();
            return ap;
        }

The problem is, if one of the fields is null, then trying to do a (string)reader["fieldname"] will return an error.

How can I change the stored procedure to check for null fields and then return, maybe an emptry string, if it finds one so that the cast to string in the C# code doesn't throw an error. Or would you suggest I do that checking in the C# code and leave the stored procedure as it is?
0
Comment
Question by:IUAATech
2 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 800 total points
ID: 16564796
SELECT ISNULL(firstname, '') firstname, ISNULL(mid_name,'') mid_name, ISNULL(lastname,'') lastname, ISNULL(maiden_firstname,'') maiden_firstname, ISNULL(maiden_lastname,'') maiden_lastname, ISNULL(maiden_middlename,'') maiden_middlename, ISNULL( mail_label, '') mail_label, ISNULL(nickname,'') nickname FROM ALUM_UNIV_ID_V
     WHERE uid = @uid
0
 

Author Comment

by:IUAATech
ID: 16565201
that was easy :)

thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question