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.