jfreeman2010
asked on
t-sql stored procedure calling other stored procedure - sql server 2008 r2
Hi
I am in sql server 2008 r2 and need help in a stored procedure that need to call anther procedure, pass value and return value.
example:
declare @temp_std table
(
std_id int,
ssn varchar(9),
class_last_timestamp datetime
)
;;;;;;
insert @temp_std
(
std_id,
ssn
)
select distinct
std_id,
ssn
from test_tb
where ssn is not null
;;;;;;
In here:
I need to call a stored procedure 'get_class_update_timestam p',
pass ssn and return last_update_timestamp for each row in
@temp_std
thank you
I am in sql server 2008 r2 and need help in a stored procedure that need to call anther procedure, pass value and return value.
example:
declare @temp_std table
(
std_id int,
ssn varchar(9),
class_last_timestamp datetime
)
;;;;;;
insert @temp_std
(
std_id,
ssn
)
select distinct
std_id,
ssn
from test_tb
where ssn is not null
;;;;;;
In here:
I need to call a stored procedure 'get_class_update_timestam
pass ssn and return last_update_timestamp for each row in
@temp_std
thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Stored procs are good for doing things, functions are good for returning stuff.
ASKER
I got a error when try to convert the stored procedure to a function as:
cannot access temporary tables from within a function.
any idea I can fix this?
thanks.
cannot access temporary tables from within a function.
any idea I can fix this?
thanks.
Could you post a piece of code from the stored procedure you want to convert here, please?
ASKER
Thanks:
ALTER PROCEDURE [dbo].[get_class_update_ti mestamp]
@SSN CHAR(9),
,@class_LATEST_TIMESTAMP DATETIME OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
CREATE TABLE #temp_table_class (class_updated_timestamp datetime)
DECLARE @SQLString NVARCHAR(4000)
DECLARE @class_table_id varchar(10), @table_name varchar(50), @SSN_FIELD VARCHAR(10),@class_FIELD_N AME varchar(10)
DECLARE class_cursor CURSOR FOR
SELECT ID,TABLE_NAME,SSN_FIELD, class_FIELD_NAME
FROM class_DEFINITION
WHERE CONTAINS_class_FIELD = 1
OPEN class_cursor
FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name, @SSN_FIELD,@class_FIELD_NA ME
IF @@FETCH_STATUS <> 0
PRINT 'END OF TABLES'
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SSN <> ''
BEGIN
set @SQLString='SELECT TOP 1 TIME_STAMP
FROM '+@table_name+' T,
class_DEFINITION F
WHERE '+@SSN_FIELD+'='''+@SSN+
''' AND ('+@class_FIELD_NAME+' IS NOT NULL ) AND F.ID= '+@class_table_id+
' AND T.FORM_ID = F.FORM_ID
ORDER BY RECORD_ID DESC'
END
INSERT #temp_table_class
EXECUTE (@SQLString)
FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name,@SSN_FIELD, @class_FIELD_NAME
END
CLOSE class_cursor
DEALLOCATE class_cursor
SELECT @class_LATEST_TIMESTAMP =(SELECT MAX(class_updated_timestam p) FROM #temp_table_class)
GO
ALTER PROCEDURE [dbo].[get_class_update_ti
@SSN CHAR(9),
,@class_LATEST_TIMESTAMP DATETIME OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
CREATE TABLE #temp_table_class (class_updated_timestamp datetime)
DECLARE @SQLString NVARCHAR(4000)
DECLARE @class_table_id varchar(10), @table_name varchar(50), @SSN_FIELD VARCHAR(10),@class_FIELD_N
DECLARE class_cursor CURSOR FOR
SELECT ID,TABLE_NAME,SSN_FIELD, class_FIELD_NAME
FROM class_DEFINITION
WHERE CONTAINS_class_FIELD = 1
OPEN class_cursor
FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name, @SSN_FIELD,@class_FIELD_NA
IF @@FETCH_STATUS <> 0
PRINT 'END OF TABLES'
WHILE @@FETCH_STATUS = 0
BEGIN
IF @SSN <> ''
BEGIN
set @SQLString='SELECT TOP 1 TIME_STAMP
FROM '+@table_name+' T,
class_DEFINITION F
WHERE '+@SSN_FIELD+'='''+@SSN+
''' AND ('+@class_FIELD_NAME+' IS NOT NULL ) AND F.ID= '+@class_table_id+
' AND T.FORM_ID = F.FORM_ID
ORDER BY RECORD_ID DESC'
END
INSERT #temp_table_class
EXECUTE (@SQLString)
FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name,@SSN_FIELD, @class_FIELD_NAME
END
CLOSE class_cursor
DEALLOCATE class_cursor
SELECT @class_LATEST_TIMESTAMP =(SELECT MAX(class_updated_timestam
GO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the called procedure give a latest date,
the calling procedure need this latest date to determine if this row/record can be use/continue for the processing.
Do I make myself clear?
Thank you,
the calling procedure need this latest date to determine if this row/record can be use/continue for the processing.
Do I make myself clear?
Thank you,
ASKER
So since I can't convert this procedure into a function since it requires dynamic sql, the only choice I have is loop thru the table variables and call the procedure and update the table variables.
ASKER
Does this seem right?
declare @temp_std table
(
std_id int,
ssn varchar(9),
class_last_timestamp datetime
)
DECLARE @last_update_timestamp datetime;
DECLARE @SSN VARCHAR(9)
DELARE L_CURSOR CURSOR FOR
SELECT SSN
FROM @temp_std
WHERE SSN IS NOT NULL
Begin
insert @temp_std
(
std_id,
ssn
)
select distinct
std_id,
ssn
from test_tb
where ssn is not null
OPEN L_CURSOR
FETCH NEXT FROM L_CURSOR
INTO @SSN, @last_update_timestamp
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.get_class_update_times tamp @ssn, @last_update_timestamp OUTPUT
update t
set class_last_timestamp = @last_update_timestamp
from @temp_std t
where t.ssn = @ssn
FETCH NEXT FROM L_CURSOR
INTO @SSN, @last_update_timestamp
END
CLOSE L_CURSOR
DEALLOCATE L_CURSOR
End
declare @temp_std table
(
std_id int,
ssn varchar(9),
class_last_timestamp datetime
)
DECLARE @last_update_timestamp datetime;
DECLARE @SSN VARCHAR(9)
DELARE L_CURSOR CURSOR FOR
SELECT SSN
FROM @temp_std
WHERE SSN IS NOT NULL
Begin
insert @temp_std
(
std_id,
ssn
)
select distinct
std_id,
ssn
from test_tb
where ssn is not null
OPEN L_CURSOR
FETCH NEXT FROM L_CURSOR
INTO @SSN, @last_update_timestamp
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.get_class_update_times
update t
set class_last_timestamp = @last_update_timestamp
from @temp_std t
where t.ssn = @ssn
FETCH NEXT FROM L_CURSOR
INTO @SSN, @last_update_timestamp
END
CLOSE L_CURSOR
DEALLOCATE L_CURSOR
End
basically yes ...
but do the updates on an SSN basis (one call per ssn in the input) not actually row by row...
but do the updates on an SSN basis (one call per ssn in the input) not actually row by row...
ASKER
does the fetch is base on the row by row?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YES, it works. thank you very much for help!!
ASKER
thanks all for the help
ASKER
I also was thinking to convert that stored procedure to a function. I am going to see if I can convert it.