Can OSQL retrun a value?
-
Hi,
I have a stored procedure as given below.
CREATE Procedure SpGenerateTableKey_Test
(
@sTable as varchar(100),
@lKeyCount as integer = 1
)
AS
BEGIN
DECLARE @lKey int
SELECT @lKey = 0
BEGIN TRAN
SELECT @lKey = lKey FROM TableKeys (UPDLOCK) WHERE sTable = @Stable
UPDATE TableKeys SET lKey = lKey + @lKeyCount WHERE sTable = @Stable
COMMIT TRAN
RETURN @lKey
END
I got a function to call this procedure as shown below
AlTER FUNCTION fnGenerateTableKey_Test
(
@sTable VARCHAR(100),
@lKeyCount INTEGER
)
RETURNS INT
AS
BEGIN
DECLARE @cmd VARCHAR(250),@lKey INT
SET @cmd = 'osql -E -d ' + db_name() + ' /Q "declare @lkey Int exec spGenerateTableKey_Test ' + @Stable + '" '
EXEC master.dbo.xp_cmdshell @cmd
RETURN @lKey
END
My intension is to return the same value that has been returned by the procedure using a function. But I have no idea how to return the value that the SP return, to the calling function using OSQL. Anybody got any idea?
Why I am so much keen on function is , I can give this function name directly in any insert statement for eg:
insert into entity (lEntityKey) values ( dbo.fnGenerateTableKey_Test ('entity',1)) .
TableKeys table has got two fields , sTable and lKey. sTbale is the name of the tables in the database and lKey is the last ID key of the respective tables.
Regards
Rajesh
-
osql can return an integer value by EXIT() fucntion. But you cannot get it in a UDF. You can do it in a SP.
CREATE TABLE #ReturnValue(Rtn nvarchar(255) NULL)
INSERT #ReturnValue EXEC master.dbo.xp_cmdshell @cmd.
SELECT * FROM #ReturnValue
In your @cmd:
DECLARE @RtnVal int
EXEC @RtnVal=YourSP
EXIT(SELECT @RtnVal)
-
It seems a very awkward way shelling out to xp_cmdshell and osql only to generate the next number in a sequence.. (although I can appreciate your intentions
)Even if it should have worked to place this inside a function (which it doesn't), have you thought about what would happen when you have to generate FK values in child tables as well? The 'standard' method of handling generation of sequence numbers and inserting those into tables is by stored procedure(s), not by functions.
Also, just wanted to mention that your procedure of generating the new ID's aren't waterproof. As written, it's possible for two concurrent connections to grab the same value as the next ID. (the locking isn't what you expect it to be)
Though, there is a failsafe method of generating countervalues without risking duplicates being produced. The code is below, enjoy.
if object_id('dbo.getNextID') is not null drop proc dbo.getNextID
go
CREATE proc dbo.getNextID @tabname sysname, @nextid int OUTPUT
as
-- file: getNextID.sql
-- why: generic proc that returns the next avilable id-counter for the specified table
-- Usage:
-- declare @varForNewId int
-- exec getNextID 'tableName', @varForNewId OUTPUT
-- @varForNewId now contains the new id....
-- table: uniqueId xxUx - table to keep the counter in - 1 row for each counter and table
--
-- by: Kenneth Wilhelmsson / Cybernetics
-- when: 2001-02-09 - first version
set nocount on
declare @err int,
@rc int
-- check that counter for this table exists
if not exists ( select * from uniqueId where tablename = @tabname )
begin
set @err = 1
goto errhandler
end
-- get the next id
update uniqueId
set @nextid = nextId = nextId + 1
where tablename = @tabname
select @err = @@error, @rc = @@rowcount
if (@err <> 0) goto errhandler
if (@rc <> 1) goto errhandler
return @err
errhandler:
if ( @@trancount > 0 ) ROLLBACK TRANSACTION
declare @errmsg varchar(255)
set @errmsg = 'procedure: ' + object_name(@@procid) + ' *** FATAL ERROR *** '
raiserror(@errmsg, 16, -1) with log
return @err
GO
/Kenneth
-
Thank you very much for your valuable reply , both peter and Kenneth.
Kenneth, I can't really agree with what you have said.
it's possible for two concurrent connections to grab the same value as the next ID. (the locking isn't what you expect it to be)
because if you have noticed there is an updLock lock hint on the select statement and update locks are not compatible each other there will not any situation where two concurrent users accessing the same key. More over if not given updLock hint, it would lead to a conversion deadlock situation. Having said that the below shown code would be more ideal to me. I think the logic that has been here is almost the same as of Kenneth.
BEGIN
DECLARE @lKey int
SELECT @lKey = 0
BEGIN TRAN
UPDATE TableKeys SET lKey = lKey + @lKeyCount WHERE sTable = @Stable
SELECT @lKey = lKey - @lKeyCount FROM TableKeys WHERE sTable = @Stable
COMMIT TRAN
RETURN @lKey
END
As you said am I doing some stupid here to generate table keys using UDF as opposed to the traditional way of generating it using SP. may be yes, but sometimes we may have to obey the application development team the business.
I have slightly changed my posted function and Sp in order to achieve the same what I intended to do.
AlTER FUNCTION fnGenerateTableKey_Test
(
@sTable VARCHAR(100),
@lKeyCount INTEGER
)
RETURNS INT
AS
BEGIN
DECLARE @cmd VARCHAR(250),@lKey INT
SET @cmd = 'osql -E -d ' + db_name() + ' /Q "exec spGenerateTableKey_Test ' + @Stable + '" '
EXEC master.dbo.xp_cmdshell @cmd
SELECT @lKey = lKey - @lKeyCount FROM TableKeys WHERE sTable = @Stable
RETURN @lKey
END
CREATE PROCEDURE SpGenerateTableKey_Test
(
@sTable as varchar(100),
@lKeyCount as integer = 1
)
AS
BEGIN
DECLARE @lKey int
SELECT @lKey = 0
BEGIN TRAN
UPDATE TableKeys SET lKey = lKey + @lKeyCount WHERE sTable = @Stable
COMMIT TRAN
RETURN @lKey
END
--below code is given for testing purpose (No blocking or no deadlock identified)
--Connection 1
while 1=1
begin
select dbo.fnGenerateTableKey_Test('entity',1)
end
--Connection 2
while 1=1
begin
select dbo.fnGenerateTableKey_Test('entity',1)
end
I haven't incorporated anything in the SP for the error handling. I have to consider that as well to make it a bullet proof script.
Anyway thank you very much both of you for your invaluable response.
Regards
Rajesh
-
Try this:
-- Construct test UId table.
If Object_Id('Test') is not Null Drop Table Test
Select 0[UId] into Test
-- Obtain the next UId.
Declare @i Int
Update Test Set @i=UId,UId=UId+1
-- or --
Update Test Set @i=UId=UId+1
Which "Update" you use depends on whether you want the "Before" or "After" value of the current UId.
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Rajesh... Your method is a sure recipe for disaster. I would be very interested if you would want to (if possible) share the reasons and motifs that forces you to shell out to osql and pick up the id-value from there. This is a method that has an extremely high chance of generating duplicates!
Yes, you are correct that the updlock hint will block other readers (sry bout that, missed that one
) - BUT... That is all in vain when you're using the convoluted method of shelling out to cmdshell and calling the proc with osql..I too did use a similar test as your's, and on my laptop managed to generate a staggering 33% of duplicate values! No matter what business says, I cannot see any reason for you to do it this way - it just won't work.
Try this:
create table con1 ( id int not null )
create table con2 ( id int not null )
go
-- in QA window 1
while 1 = 1
begin
insert con1
select dbo.fnGenerateTableKey_Test('customers',1)
end
-- switch to QA window 2 and run:
while 1 = 1
begin
insert con2
select dbo.fnGenerateTableKey_Test('customers',1)
end
When satisfied ( a few seconds ) stop both loops and try:
select *
from con1 join con2
on con1.id = con2.id
When I tried it, after generating 164 id's an amazing 58 of them were duplicates!
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply