Solved

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

Posted on 2011-09-13
9
289 Views
Last Modified: 2012-08-13
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.    
0
Comment
Question by:jschlemmer
  • 5
  • 4
9 Comments
 
LVL 51

Expert Comment

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

Author Comment

by:jschlemmer
ID: 36532800
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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36533309
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
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36533315
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:jschlemmer
ID: 36535883
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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36536459
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...
0
 

Accepted Solution

by:
jschlemmer earned 0 total points
ID: 36538333
Oh my god, I just figured it out.  It is one of those moments in programming that makes me wish I'd chosen a different profession.  When I was passing the data from my ajax call to my web service like this:

data: '{ Equip:"' + equipinfo + '"}'

I should've done this:

data: '{ Equip:"' + equipinfo.toString() + '"}'

Voila!

0
 

Author Closing Comment

by:jschlemmer
ID: 36558788
Solution found on my own.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36538460
which contradicts what you said before! you said everything is passed correctly, and I was trying to find out what is passed exactly... anyways...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
2008 to 2016 SQL migration.. 5 29
XML to SQL Table using c# 5 47
SQL Maintenance Plan 3 17
Usage Scenarios for Extended Events? 1 12
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

17 Experts available now in Live!

Get 1:1 Help Now