Sproc issue using variables and returning results

Posted on 2007-10-18
Last Modified: 2013-12-17
Hey all-
I have a sproc that is doing several steps of validation. In the end, I either want a zero length string or the accumulated error messages of about 10 data checks. The servers may change so I need to pass the server name and database in as a varialble. Ultimately, I need this to fit into a strongly typed dataset in visual studio 2005 so i can use for reporting. Due to the server and database being passed in, I'm creating an execution string and executing it. That's where my problem is...

Here is a sample of what I'm doing:

select @SQLToExecute = 'select bus_unit_id from ' + @IMISDatabaseAccess + '.dbo.business_unit where bus_unit_id = ' + @qh_im + ' and inactive_flag = 0 and company_id = ' + cast(@company_id as varchar(10))
exec (@SQLToExecute)
if @@rowcount = 0
            Select @ErrorMessage = @ErrorMessage + 'Invalid QH IM |'

select @SQLToExecute = 'select bus_unit_id from ' + @IMISDatabaseAccess + '.dbo.business_unit where bus_unit_id = ' + @cc_im + ' and inactive_flag = 0 and company_id = ' + cast(@company_id as varchar(10))
exec (@SQLToExecute)
if @@rowcount = 0
            Select @ErrorMessage = @ErrorMessage + 'Invalid CC IM |'

if isnull(@base_cost,0) =  0  set @ErrorMessage = @ErrorMessage + 'Base Cost cannot equal 0 or null |'
if isnull(@capability_support_cost,0) =  0  set @ErrorMessage = @ErrorMessage + 'Capability Support Cost cannot equal 0 or null |'
if isnull(@service_cost,0) =  0  set @ErrorMessage = @ErrorMessage + 'Service Cost cannot equal 0 or null |'

--Set  return variable
select  @ErrorMessage as Result

Since each of the execs return a value, I think VStudio is getting confused as to what is the actual return value and is saying that my 'result' column is null.

Is there a better way to do this
Question by:KBSLPDev
    LVL 39

    Expert Comment

    did you initialize the variable @ErrorMessage . if not the default is null and when you concatnate null with any other value it returns null.

    try adding the following line immediatly after the declaration of @ErrorMessage

    select @ErrorMessage = ''
    LVL 5

    Expert Comment

    If the above didn't fix it, try changing the 'Select @ErrorMessage = @ErrorMessage + 'MESSAGE'' between the BEGIN/END blocks to just 'SET @ErrorMessage = @ErrorMessage + 'MESSAGE'.

    Author Comment

    The @ErrorMessage is set to a zero length string. at the end of the sproc, i even did this at the end of the sproc:

    select @ErrorMessage = 'this is a test'
    select  @ErrorMessage as Result

    and still get a dbnull error. I think the problem is the other selects returning actual values. My guess is if I can select the results into a variable, I could check the variable rather than using @@rowcount. However, since my sql is a string, I don't know how to do that since the variable has to be returned to a variable in the sproc.
    LVL 5

    Accepted Solution

    Try using an output parameter in your SP.

    @Hello nvarchar(50) OUT

    SET @Hello = 'Hello,'

    [later in your SP]

    SET @Hello = @Hello + ', World!'


    C# Code:

                SqlCommand cmd = new SqlCommand("SayHello", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parHello = new SqlParameter("@Hello", SqlDbType.NVarChar, 50);
                parHello.Direction = ParameterDirection.Output;

                string hello = (string)parHello.Value;

    LVL 21

    Expert Comment

    How do you declare @ErrorMessage?  And try your last post but return it as the first result set in the proc.  If that returns ok, you might be doing something wrong with the multiple result sets being returned and can diagnose by progressively moving those two statements further down in your proc.

    Author Comment

    Earlier I was trying to use the output param but couldn't get it to work in VStudio. I finally got it to work.

    Here's the code i used:
            string strOutput = "";
    dsRulesTableAdapters.dtRuleValidateTableAdapter ta = new dsRulesTableAdapters.dtRuleValidateTableAdapter();

            dsRules.dtRuleValidateDataTable dt = ta.GetData(param1, param2, out strOutput);


    Thanks for the help!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now