Stored procedure works in sql, not in .net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi i have a strange problem. I have a stored procedure which inserts a new
row and for one of the values gets a max of a number, adds 1 to it, then puts
this in an integer field. Probably easier if i paste it.

CREATE PROCEDURE dbo.sp_NewRef
AS
declare @nextnum int
SELECT @nextnum = max((anothernumber) + 1) from tblx

INSERT INTO tblx (reference) VALUES (@nextnum);SELECT SCOPE_IDENTITY()
GO

when I run exec sp_newref it creates a new row and returns me the id. when i
view the row it has definately made a new reference, latest one plus 1.
However, when i run this in my aspx page, it creates a new row but does not
create the new reference number. Value is <NULL>. Why? I would at least know
that it just wasnt working if it didnt create a row in the first place, but
it does!
 
I'm assuming you're not using the SQL in your code-behind to do this, but
calling the stored procedure you've created in your database?

If you could post your code that you're using to access the stored procedure
it may be more of a clue...

Thanks.

Daniel.
 
On a side note, you might want to not name your stored procedures starting
with "sp_". Just stick with spNewRef. Calling a stored procedure that
starts with sp_ will cause the server to first look in the master database
for the sproc. This is usually a waste of resources because you shouldn't
really be storing data in master.

There are several links out there, I just randomly picked on.

Don't use the prefix "sp_" in the stored procedure name if you need to
create a stored procedure to run in a database other than the master
database.

http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

bill
 
Back
Top