• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Sproc issue using variables and returning results

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
1 Solution
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 = ''
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'.
KBSLPDevAuthor Commented:
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.
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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;

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.
KBSLPDevAuthor Commented:
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!!

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now