Stored Procedure returns error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure returns error

Author

Topic

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-05 : 13:58:46

hi

i just migrated from oracle to sqlserver.

i needed a common stored proceedure for returning last identity value from a group of tables which store no data. they are only used to generate new identity value. after inserting a record the transaction is rolled back in these tables and the identity value is to be returned by the procedure. all the tables have a single field which is an identity.

this way i am just emulating oracle sequence concept.

the name of the table is passed as a parameter to the stored procedure. Although the code works but unfortunately the procedure returns the following error :

Msg 208, Level 16, State 1, Line 1
Invalid object name 's'.
The 'getpkey' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.


alter PROCEDURE dbo.getpkey(@tname varchar,@pkey  numeric(18,0))
AS
declare @qrystr varchar(8000) 
BEGIN
SET NOCOUNT ON;
set @qrystr='insert '+ quotename(@tname) +' DEFAULT VALUES'
begin transaction
exec (@qrystr)
rollback transaction

select @pkey=ident_current(quotename(@tname,''))
return @pkey 
END
GO

code for testing :
------------------


declare @pkey numeric(18,0)
exec dbo.getpkey 'seqaccmst',@pkey
print @pkey

Please HELP.

thanks in advance.

Debadatta

deb

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-05 : 14:15:19

Two things..

1. Change your declaration to include the length of a varchar, otherise it defaults to 1:
alter PROCEDURE dbo.getpkey(@tname varchar(128), @pkey numeric(18,0))

2. @pkey is always going to be NULL outside the procedure because it is ever set to a value. If you want to set you can either pass it as a OUTPUT paramter and assign it a value or capture the return code from the EXEC:
exec @pkey = dbo.getpkey 'seqaccmst',@pkey

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-05 : 14:22:59

Thank you Lamprey. Your suggestion has been quite usefull to me. I have been googling about this since last two hours.

deb

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 14:28:09

I really think you want an OUTPUT variable

alter PROCEDURE dbo.getpkey @tname varchar, @rc int OUTPUT, @error int OUTPUT, @rowcount int OUTPUT
AS

SELECT @rc = 0, @error = 0, @rowcount = 0

But What's with the dynamic sql?

Trying to creat 1 sproc for all inserts?

bad....be berry, berry bad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-05 : 15:04:40

yes X002548 you are right i need an out put parameter.
but this is just a concept.

Actually my requirement is to generate pr.key values for tables in independent databases which can not be linked as some of the servers reside in rural villages where internet is not available.

but the data needs to be posted regularly at the central server at head office manually.

during this merging process identity values may be duplicated as there are dozens of servers.

that is why i need to take care so that no two servers should generate the same pr.key value.

thats the reason i am emulating an oracle like sequence by use of a table where no data will be stored. The record will be deleted or rolled back immediately after insertion. this table will be only used for generating the next integer value to be used in a pr.key

and the last identity value thus generated by the above operation will be clubbed with a server id to geerate a primary key value which will be used in the actual table which stores records. and this actual table willl not have an identity column. so there will be no problem during merging of records from different servers.

please suggest any better ideas.

Thank you

deb

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-05 : 15:50:24

If I understand you correctly, you are going to genereate an ID from a single source to use across multiple servers and that ID combined with a ServerID will be your Primary Key?

Assuming I read your post correctly, my question is why generate the ID from a single place? If you are combining it with a Server ID then it seems you can generate an ID via idnetity on that same server and not have any collisions.

Does that make sense?

hello.d3b
Starting Member

16 Posts

Posted - 2009-08-06 : 09:26:05

i did not get you properly. perhaps this will make it more clear

every branch office have a server with 3 letter server ids like ASK, BER, BAM.... etc

my table for storing data is "acc_mst" which requires a pr.key with values like "ASK999999999999999999"
this is a combination of server id "ASK" and an integer value. This integer value needs to be auto generated through a identity column in each server at every branch office.

now i keep this identity column in another table like "seq_accmst" this table will contain no data and only be used for generating the next identitty value for the pr.key for "acc_mst" table.

now when we combine all the records from all servers and put it together in the central server there will be no collision as the integeral part may collide but the server id will be allways different.

e.g. : ASK000000000000000123, BER000000000000000123, BAM000000000000000123 (in acc_mst)

here are the table structures :

seq_accmst :
1. keyid - numeric(18,0) (Primary key)

acc_mst :
1. accid nchar(21) pr.key (this is a combination of serverid+identity value of seqaccmst)
2. accname varchar
3. accgrp
4......... and so on

this is my requirement.

deb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources