July 18, 2005 at 3:39 pm
I'm trying to dynamically determine min value for RecordID in a colllection of tables. The code is below. I'm getting error at the line:
set @recIDmin = exec('select min(record_id) from ' + @TblName)
does anyone know hoe to do it right?
Thanks
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare @n int
Declare @n_str varchar(55)
declare @recIDmin int
declare @recIDmin_str varchar(55)
declare @TblName varchar(55)
set @n = 5
while @n < 20
BEGIN
--cast suffix of a table name to varchar
set @n_str = cast(@n as varchar)
--construct table name dynamically
select @TblName = '_D_T_dep_tbl' + @n_str
--get min value of record_id in a tbl into var @recIDmin
set @recIDmin = exec('select min(record_id) from ' + @TblName)
--ERROR: Incorrect syntax near the keyword 'exec'.
--update counter of tables
set @n = @n + 1
END
July 18, 2005 at 3:55 pm
Use sp_executesql with output parameter
Not TESTED.
Declare @n int
Declare @n_str nvarchar(55)
declare @recIDmin int
declare @recIDmin_str nvarchar(55)
declare @TblName nvarchar(55)
declare @Sqlstring nvarchar(4000)
set @n = 5
while @n < 20
BEGIN
--cast suffix of a table name to varchar
set @n_str = cast(@n as varchar)
--construct table name dynamically
select @TblName = '_D_T_dep_tbl' + @n_str
set @sqlstring = 'select min(record_id) from ' + @TblName
--get min value of record_id in a tbl into var @recIDmin
exec sp_executesql @sqlstring, N'@recIDmin int OUTPUT',@recIDmin OUTPUT
--update counter of tables
set @n = @n + 1
END
Edit: Good Reading
July 19, 2005 at 8:41 am
Ray;
i still cannot see how var @RecIDmin gets assigned a value equal to:
'select min(record_id) from ' + @TblName
that's what I wanted to be done in the first place.
Thanks
July 19, 2005 at 9:14 am
You cannot do it directly. Workaround
SET NOCOUNT ON
CREATE TABLE #_D_T_dep_tbl1
(
record_id INT,
Record VARCHAR(100)
)
INSERT INTO #_D_T_dep_tbl1 VALUES (12, 'AAAA')
INSERT INTO #_D_T_dep_tbl1 VALUES (24, 'BBBB')
INSERT INTO #_D_T_dep_tbl1 VALUES (36, 'CCCC')
INSERT INTO #_D_T_dep_tbl1 VALUES (48, 'AADDDAA')
INSERT INTO #_D_T_dep_tbl1 VALUES (60, 'sdffs')
INSERT INTO #_D_T_dep_tbl1 VALUES (72, 'dsfsdfsdf')
CREATE TABLE #_D_T_dep_tbl2
(
record_id INT,
Record VARCHAR(100)
)
INSERT INTO #_D_T_dep_tbl2 VALUES (1, 'AAAA')
INSERT INTO #_D_T_dep_tbl2 VALUES (2, 'BBBB')
INSERT INTO #_D_T_dep_tbl2 VALUES (3, 'CCCC')
INSERT INTO #_D_T_dep_tbl2 VALUES (4, 'AADDDAA')
INSERT INTO #_D_T_dep_tbl2 VALUES (6, 'sdffs')
INSERT INTO #_D_T_dep_tbl2 VALUES (7, 'dsfsdfsdf')
CREATE TABLE #_D_T_dep_tbl3
(
record_id INT,
Record VARCHAR(100)
)
INSERT INTO #_D_T_dep_tbl3 VALUES (2, 'AAAA')
INSERT INTO #_D_T_dep_tbl3 VALUES (4, 'BBBB')
INSERT INTO #_D_T_dep_tbl3 VALUES (6, 'CCCC')
INSERT INTO #_D_T_dep_tbl3 VALUES (8, 'AADDDAA')
INSERT INTO #_D_T_dep_tbl3 VALUES (0, 'sdffs')
INSERT INTO #_D_T_dep_tbl3 VALUES (2, 'dsfsdfsdf')
CREATE TABLE #Result
(
RecordID INT
)
Declare @n int
Declare @n_str varchar(55)
declare @recIDmin int
declare @recIDmin_str varchar(55)
declare @TblName varchar(55)
set @n = 1
while @n < 4
BEGIN
--cast suffix of a table name to varchar
set @n_str = cast(@n as varchar)
--construct table name dynamically
select @TblName = '#_D_T_dep_tbl' + @n_str
--get min value of record_id in a tbl into var @recIDmin
DELETE #Result
INSERT #Result
EXEC('select min(record_id) from ' + @TblName)
SELECT @recIDmin = RecordID FROM #Result
SELECT @recIDmin Minimum, @TblName TableName
--update counter of tables
set @n = @n + 1
END
DROP TABLE #Result, #_D_T_dep_tbl1, #_D_T_dep_tbl2, #_D_T_dep_tbl3
Regards,
gova
July 19, 2005 at 9:18 am
Hi Sergio, Giovin,
Of course it can be done directly.
This should be a little easier to understand:-
DECLARE @SQL nvarchar(4000),
@A_Variable varchar(100)
SET @A_Variable = 10
PRINT '1 - ' + CAST(@A_Variable as varchar(50))
BEGIN TRAN
SET @SQL = N'SET @A_Variable = ''ROGER'''
EXEC sp_executesql @SQL, N'@A_Variable varchar(100) OUTPUT', @A_Variable OUTPUT
COMMIT TRAN
PRINT '2 - ' + CAST(@A_Variable as varchar(50))
SET @A_Variable = 20
PRINT '3 - ' + CAST(@A_Variable as varchar(50))
Just copy it into QA and have look what it is doing. Then change the sql to suit e.g:
SET @SQL = N'SET @A_Variable = select min(record_id) from ' + @TblName
You do need to read the articles about dynamic sql though - and do some searches on the same in here........
Have fun
Steve
We need men who can dream of things that never were.
July 19, 2005 at 9:48 am
ic it really works Steve. Tnaks a lot for the science! U right about reading, we could not get anywhere w/o it. If only I had more free time..
July 19, 2005 at 9:50 am
Qovinn,
i haven't tried yr code yet, I'll definitely do. Thanks for yr time posting it!
July 19, 2005 at 9:56 am
Qovinn, I guess I did it the way quite similar to yr: used a temp table:
--update display_order in all 15 tables
Declare @n int
Declare @n_str varchar(55)
declare @recIDmin int
declare @recIDmin_str varchar(55)
declare @TblName varchar(55)
set @n = 5
while @n < 20
begin
set @n_str = cast(@n as varchar)
select @TblName = '_D_T_dep_tbl' + @n_str
-----USED A TEMP TABLE HERE TO STORE min(record_id) VALUE
--get min value of record_id in a tbl, store it as rec in _tmp1
exec('insert _tmp1 (fld) select min(record_id) from ' + @TblName)
---THEN ASSIGNED IT TO @recIDmin
--assign val to @recIDmin
set @recIDmin = (select fld from _tmp1)
delete from _tmp1
--convert @recIDmin to varchar
set @recIDmin_str = cast(@recIDmin as varchar)
--update target table
exec( 'update ' + @TblName +
' set display_order = 1 + record_id - ' + @recIDmin_str )
set @n = @n + 1
delete from _tmp1
END
I must admit that Steve's solution (using sp_executesql) is more advanced.
Thanks for posting.
July 19, 2005 at 11:56 am
OOPS Forgot an important Part
Declare @n int
Declare @n_str nvarchar(55)
declare @recIDmin int
declare @recIDmin_str nvarchar(55)
declare @TblName nvarchar(55)
declare @Sqlstring nvarchar(4000)
set @n = 5
while @n < 20
BEGIN
--cast suffix of a table name to varchar
set @n_str = cast(@n as varchar)
--construct table name dynamically
select @TblName = '_D_T_dep_tbl' + @n_str
set @sqlstring = 'select @recIDmin = min(record_id) from ' + @TblName
--get min value of record_id in a tbl into var @recIDmin
exec sp_executesql @sqlstring, N'@recIDmin int OUTPUT',@recIDmin OUTPUT
--update counter of tables
set @n = @n + 1
END
February 22, 2006 at 10:55 am
Ray,
Thanks a million. I have been trying to figure out a similiar problem and have spent most of the day fruitlessly, until I found your solution.
It's much appreciated.
Mack
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply