asked on

Null Value When Using Temp Table Variable in SQL Server Stored Procedure Executed from .NET

Not sure whether the problem is in SQL or .Net. I have an asp.net web application that executes SQL stored procedures from a web service. One of the parameters I am passing to the web service is a text string that contains a lot of information that is delimited by various characters. I developed a function in SQL that splits that string into the different pieces and inserts them into a temp table variable that the function then returns. Back to the main stored procedure, I have variables that I am assigning values from this temp table that my function returns. All of this works perfectly. But then, when I try to use the variables whose values were obtained from the temp table to perform other functions, it is as if the variables have no value even though I have confirmed that they do in fact have a value.

Example of a string that might get passed to the Main Stored Procedure into parameter @Equip:
XORA TEST-XORA TEST:0.25^1^2^3^4^5|XORATEST1-XORA TEST:0.75^6^7^8^9^0|

Once in SQL, that string is split up and the appropriate pieces are inserted into @Temp which is returned from my split function
Function Split(@Equip) Returns @Temp Table (Serial Varchar(1000), Model Varchar(1000), Labor Varchar(10), Meter1 Int, Meter2 Int, Meter3 Int, Meter4 Int, Meter5 Int)

Then in my main stored procedure I have variables assigned a value from split
Select @Serl = Serial, @Model1 = Model, @Labor = Labor, @Meter1 = Convert(Varchar,Meter1), @Meter2 = Convert(Varchar,Meter2), @Meter3 = Convert(Varchar,Meter3), @Meter4 = Convert(Varchar,Meter4), @Meter5 = Convert(Varchar,Meter5) From Split(@Equip)

At this point, if I return the values of each of these variables, everything looks exactly as it should. Every variable has a clear and correct value taken from the split @Equip parameter. BUT, if I try to use these variables to do a select on another table, it returns null as if my variables have no value. So when I do:
Select @EquipmentID = EquipID From SVC00300 Where SerlNmbr = @Serl And ItemNmbr = @Model1
EquipmentID returns blank as if both @Serl and @Model1 are blank or invalid even though I know that not to be true.. But here is where it gets super crazy. If I just run an Exec statement on this stored procedure from SQL Server passing it the exact same values I'm passing it from .Net, it works perfectly. The only issue is when I am trying to run it from the website. And I know that the website is passing all the information correctly to SQL because I've returned all the variable values in an alert and everything from the split to the assigning of variable values from the split are working. It is only when I try to use the variable values from the temp table variable to perform other tasks that it is as if those variables are blank.

HainKurt

to much story no code :) please post some code that shows how do you pass it, and code from your sp...

jschlemmer

ASKER

String being passed from .Net to SQL from my AJAX call, as follows:

data: '{Equip: "XORA TEST-XORA TEST:0.25^1^2^3^4^5|XORATEST1-XORA TEST:0.75^6^7^8^9^0|"}'

Open in new window

Applicable portions of the WebMethod the above code is being passed to:

Public Void SubForm(string Equip)
var comm = new SqlCommand("Call_Portal_Basic", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@Equip", Equip);

Open in new window

Set up of Stored Procedure that the Web Method is Executing:

Procedure ProcessEquip(@Equip Varchar(Max))

Open in new window

Once in SQL, it goes into a function that returns the needed info as a table variable:

Function Split(@Equip) Returns @Temp Table (Serial Varchar(1000), Model Varchar(1000), Labor Varchar(10), Meter1 Int, Meter2 Int, Meter3 Int, Meter4 Int, Meter5 Int)

Open in new window

Then use the Split function to assign values to variables within the stored procedure:

Select @Serl  = Serial,  @Model1 = Model, @Labor = Labor, @Meter1 = Convert(Varchar,Meter1), @Meter2 = Convert(Varchar,Meter2), @Meter3 = Convert(Varchar,Meter3), @Meter4 = Convert(Varchar,Meter4), @Meter5 = Convert(Varchar,Meter5) From Split(@Equip)

Open in new window

Then use those variables to perform other functions, such as select statements to pull further information from other tables such as:

Select @EquipmentID = EquipID From SVC00300 Where SerlNmbr = @Serl And ItemNmbr = @Model1

Open in new window

This is where the problem is. The above select statement returns null as if @Serl and @Model1 are null or invalid even though I know they're not. But only if run externally. Works fine when run like this from SQL Server

Exec ProcessEquip 'XORA TEST-XORA TEST:0.25^1^2^3^4^5|XORATEST1-XORA TEST:0.75^6^7^8^9^0|'

Open in new window

Same string being passed to the same Stored Procedure yielding different results.

HainKurt

ok. can you change your sp and do

select var1, var2, ..., varn

where varn is a variable that you passed to this sp
I just want to make sure parameter is passed correctly, and your split functions work properly

can you do this? from your app, call it, pass string and get result, which is above select statement...
and see what you get

HainKurt

if above is too much work for you, then in your sp

select into myvarTable
select var1, var2, ..., varn;

so when you call it, check this table if everything is passed correctly and your split works fine

jschlemmer

ASKER

Thanks but I've done that and it provides no help. I know that the split performs perfectly and I know that the variables are being assigned values from the split. I am returning the values of the @Serl and @Model1 variables to the webpage in an alert, and they say exactly what they should. It's not until I try to use the values of @Serl and @Model1 that something is happening that I don't know how to trap.

The below returns Xora Test Xora Test from the provided string, which is exactly what it should say:

Select @Serl, @Model

Open in new window

But when I try to assign another variable a value from another tablet where I use @Serl and @Model1 in the Where clause, not only does that new variable return blank, but if I add it to the above select:

Select @Serl, @Model, @EquipmentID

Open in new window

The whole thing now returns blank

I may just have my split function insert the dissected string into a physical table instead of a table variable and then just set up a trigger on that table to process each item on insert. Then delete the record after it processes. A little more clunky, but will do the trick I think. But if anyone has any ideas on why this wouldn't work, I am definitely still open to feedback.

HainKurt

I am not sure what is happening...

you say this is fine

Select @Serl, @Model

but this is not

Select @Serl, @Model, @EquipmentID

it is not, maybe there is an error here

Select @EquipmentID = EquipID From SVC00300 Where SerlNmbr = @Serl And ItemNmbr = @Model1

if you have multiple records found, this sql will fail

try top 1

Select top 1 @EquipmentID = EquipID From SVC00300 Where SerlNmbr = @Serl And ItemNmbr = @Model1

or

set @EquipmentID = (Select top 1 EquipID From SVC00300 Where SerlNmbr = @Serl And ItemNmbr = @Model1)

then use

Select @Serl, @Model, @EquipmentID

to see if it makes any difference...

ASKER CERTIFIED SOLUTION

jschlemmer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jschlemmer

ASKER

Solution found on my own.

HainKurt

which contradicts what you said before! you said everything is passed correctly, and I was trying to find out what is passed exactly... anyways...