SET for a local var

[seanken]

Hi all,

The 'SEARCH' functionality doesn't seem to be working on the site so if my question has already been asked, apologies!

I have a table that has a number of date fields names 'EL1A', 'EL2A' and so on up to EL46A. I have fetched the row back via a cursor and am now attempting to perform a WHILE loop to go through each of the fields and select the earliest date from all the record date fields. Any ideas?

Cheers,
Sean

DECLARE @iCounter INT,
@ErrorCode INT,
@StartDate SMALLDATETIME

WHILE (@iCounter <= 46) AND
(@ErrorCode = 0)
BEGIN

SET @StartDate = /**move the value of the ELnA field in here using the @iCounter value**/
CONTINUE

/*Some other processing......*/

END

 

[despierto]

This is a solution, an ugly one, but a solution nonetheless.

select Earliest = CASE
	when  EL1A < EL2A AND EL1A < EL3A THEN EL1A
	when  EL2A < EL1A AND EL2A < EL3A THEN EL2A
	when  EL3A < EL1A AND EL3A < EL2A THEN EL3A
	END
FROM table

Your best bet would probably be to normalize the table though, and put all your dates in one column:

original_key
EL1A
EL2A
...
EL46A

change that structure to:
original_key
EL_Code
DateVal

Then you can do a select using Min and group by.

 

[seanken]

Thanks Despierto. I had this in mind as a last resort. There must be a way to populate the @StartDate var though, in the same way as a dynamic SQL string can be built and run....I was thinking along the lines of

SET @StartDate = N'@EL' + @iCounter + N'A'

Doesn't work, obviously, or I wouldn't have posed the question

Can't change the Db setup though as I am only taking a feed from it. The owners are not very persuasive to change!

 

[despierto]

The owners are not very persuasive to change!" - Most aren't!

okay, what follows is an update trigger that I wrote, kinda complex, and very slow, it doesn't work in the real world. A few of the objects are trigger specific (inserted, deleted tables, COLUMNS_UPDATED(), etc.) And I don't have time to modify it for you, but you might be able to use some of the functionality:

CREATE TRIGGER test_trigger ON [dbo].[tJobDescription] 
FOR UPDATE
AS

DECLARE @OldVal varchar(250)
DECLARE @NewVal varchar(250)
DECLARE @OldKey varchar(250)
DECLARE @NewKey varchar(250)
DECLARE @ColumnName varchar(255)
DECLARE @ColumnID int
DECLARE @RowCount int
DECLARE @TableName varchar(128)
DECLARE @SqlString nvarchar(280)
DECLARE @Param nvarchar(25)
DECLARE @KeyFetchStatus int
DECLARE @FieldFetchStatus int

-- This has to be done before the cursor is declare because the global @@ROWCOUNT 
-- will get reset to 0, as a result of the cursor not returning any rows yet...
SET @RowCount=@@ROWCOUNT
SET @TableName = 'tJobDescription'

DECLARE Fields_Cursor CURSOR FOR
    select syscolumns.[colid], syscolumns.[name] 
    from syscolumns 
        inner join sysobjects on
        syscolumns.[id] = sysobjects.[id]
    where sysobjects.[name] = @TableName

DECLARE NewKey_Cursor CURSOR FOR
    select JobCode 
    from inserted

DECLARE OldKey_Cursor CURSOR FOR
    select JobCode 
    from deleted

SET NOCOUNT ON
OPEN NewKey_Cursor
OPEN OldKey_Cursor
FETCH NEXT FROM NewKey_Cursor INTO @NewKey
FETCH NEXT FROM OldKey_Cursor INTO @OldKey
SET @KeyFetchStatus = @@FETCH_STATUS
WHILE @KeyFetchStatus = 0
BEGIN
	OPEN Fields_Cursor
	FETCH NEXT FROM Fields_Cursor INTO @ColumnID, @ColumnName
	SET @FieldFetchStatus = @@FETCH_STATUS
	WHILE @FieldFetchStatus = 0
	BEGIN
		IF (COLUMNS_UPDATED() & POWER(2, @ColumnID-1) = POWER(2, @ColumnID-1)) 
		BEGIN
			SELECT * INTO ##ins FROM inserted WHERE JobCode = @NewKey
			SELECT * INTO ##del FROM deleted WHERE JobCode = @OldKey
			SET @Param = N'@Val nvarchar(250) output'   
			SET @SqlString = N'SELECT @Val = '  + @ColumnName + N' from ##ins'
			EXEC sp_executesql @SqlString, @Param, @Val = @NewVal OUTPUT
			SET @SqlString = N'SELECT @Val = '  + @ColumnName + N' from ##del'
			EXEC sp_executesql @SqlString, @Param, @Val = @OldVal OUTPUT
			If @OldVal != @NewVal 
				INSERT INTO Table1 VALUES 
				(@ColumnName, @OldVal, @NewVal, getdate())
			DROP TABLE ##ins
			DROP TABLE ##del
		END
		FETCH NEXT FROM Fields_Cursor INTO @ColumnID, @ColumnName
		SET @FieldFetchStatus = @@FETCH_STATUS
	END
	CLOSE Fields_Cursor
	FETCH NEXT FROM NewKey_Cursor INTO @NewKey
	FETCH NEXT FROM OldKey_Cursor INTO @OldKey
	SET @KeyFetchStatus = @@FETCH_STATUS

END

DEALLOCATE Fields_Cursor
DEALLOCATE NewKey_Cursor
DEALLOCATE OldKey_Cursor

SET NOCOUNT OFF

 

[despierto]

Okay, I found the time to modify the trigger I wrote above... You can use this inside a stored procedure. As a warning, though, this is gonna be terribly slow. I built a 4 column table with two rows to test it, and it worked fine. You might want to consider comparing this cursor to a UNION query, see which one runs faster. By UNION, I mean since you can't redo the data in the above table, you can make a relational view at least.

Here's the cursor method:

DECLARE @LowestDate datetime
DECLARE @CurrentDate datetime
DECLARE @NewKey int
DECLARE @ColumnName nvarchar(255)
DECLARE @ColumnID int
DECLARE @RowCount int
DECLARE @TableName nvarchar(128)
DECLARE @TableKey nvarchar(128)
DECLARE @SqlString nvarchar(280)
DECLARE @Param nvarchar(25)
DECLARE @KeyFetchStatus int
DECLARE @FieldFetchStatus int
DECLARE @TableVariable TABLE (pk int, LowestDate datetime)

-- This has to be done before the cursor is declare because the global @@ROWCOUNT
-- will get reset to 0, as a result of the cursor not returning any rows yet...
SET @RowCount=@@ROWCOUNT
SET @TableName = N'Table2'
SET @TableKey = N'pk'

DECLARE Fields_Cursor CURSOR FOR
    select syscolumns.[colid], syscolumns.[name]
    from syscolumns
        inner join sysobjects on
        syscolumns.[id] = sysobjects.[id]
    where sysobjects.[name] = @TableName and syscolumns.[name] <> @TableKey

DECLARE NewKey_Cursor CURSOR FOR
    select pk
    from Table2

SET NOCOUNT ON
OPEN NewKey_Cursor
FETCH NEXT FROM NewKey_Cursor INTO @NewKey
SET @KeyFetchStatus = @@FETCH_STATUS
WHILE @KeyFetchStatus = 0
BEGIN
    OPEN Fields_Cursor
    FETCH NEXT FROM Fields_Cursor INTO @ColumnID, @ColumnName
    SET @FieldFetchStatus = @@FETCH_STATUS
    SET @LowestDate=null
    SET @CurrentDate=null
    WHILE @FieldFetchStatus = 0
    BEGIN
        SET @Param = N'@Val datetime output'   
        SET @SqlString = N'SELECT @Val = '  + @ColumnName + N' from ' + @TableName + N' WHERE ' + @TableKey + N'=' + CAST(@NewKey as nvarchar(10))
        EXEC sp_executesql @SqlString, @Param, @Val = @CurrentDate OUTPUT
        If @CurrentDate < @LowestDate OR @LowestDate IS NULL
            SET @LowestDate = @CurrentDate 
       FETCH NEXT FROM Fields_Cursor INTO @ColumnID, @ColumnName
       SET @FieldFetchStatus = @@FETCH_STATUS
    END
    INSERT INTO @TableVariable VALUES
	(@NewKey, @LowestDate)
    CLOSE Fields_Cursor
    FETCH NEXT FROM NewKey_Cursor INTO @NewKey
    SET @KeyFetchStatus = @@FETCH_STATUS

END

DEALLOCATE Fields_Cursor
DEALLOCATE NewKey_Cursor

SET NOCOUNT OFF

select * from @TableVariable

And the UNION query, followed by the MIN query.

SELECT pk, 'EL1A' as FieldName, EL1A AS dtDate
into #temp
FROM Table2
UNION
SELECT pk, 'EL2A', EL2A
FROM Table2
UNION
SELECT pk, 'EL3A', EL3A
FROM Table2
UNION
SELECT pk, 'EL4A', EL4A
FROM Table2

SELECT pk, MIN(dtDate) FROM #temp 
GROUP BY pk

hope this helps...

 

[philhege]

This stored procedure will isolate the datetime fields in a table and return the earliest date among the fields.
It uses two global temp tables, so it's not a concurrent-capable SP.
Replace the table name and primary key field names with your values, and enjoy.


DECLARE @sql varchar(4000), @table varchar(50),@primarykey varchar(64), @lastdatecol varchar(64)
SELECT @sql = '', @table = 'yourtablename', @primarykey = 'yourprimarykeyfieldname'

select @lastdatecol = min(column_name) from information_schema.columns
where data_type = 'datetime' and table_name=@table

SELECT @sql = @sql + 'SELECT '+@primarykey+', ''' + column_name + ''' AS colname,
Convert(datetime,' + column_name + ') AS colval ' +
case when column_name = @lastdatecol then ' INTO ##normaltable ' else '' end +' FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name=@table AND column_name<>@primarykey and data_type='datetime'
SELECT @sql = Left(@sql,Len(@sql)-5)
print @sql
EXEC (@sql)

select @sql = '
select a.'+@primarykey+' as keyval, min(a.colval) as earlydate into ##lowdates from ##normaltable a
group by a.'+@primarykey
print @sql
EXEC (@sql)

select @sql = '
select * from ##normaltable
join ##lowdates on earlydate = colval and keyval = '+@Primarykey+'
order by keyval'
print @sql
exec (@sql)

drop table ##Lowdates
drop table ##normaltable

--Many thanks and a tip o' the brain bucket to Rob Volk at sqlteam.com for the kernel of this code.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[seanken]

Thanks guys. These should sort me out.