dynamic | RELATIONAL

Tag Archives: dynamic

APEX 5: Showing custom success message from Dynamic Action (DA) on Universal Theme

by josepcoves on 17/01/2017 in General @en, Technology

I needed to show a success message after ending a Dynamic Action and I wanted this message to be with the same format as success messages from page processes. Since this is not a standard feature of Oracle APEX, you can do the following trick if you’re using Universal Theme: Create a JavaScript function which […]

Comentaris tancats

SQL Server: Find first column with specified value

by josepcoves on 10/02/2015 in News, Technology

Returns first column of any table which contains a search value

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Josep Coves Barreiro
-- Create date: 10/02/2015
-- Description:	Returns the first column of the table which contains a search value
-- =============================================
BEGIN
	DECLARE @v_count int
	DECLARE @v_table varchar(200)
	DECLARE @sql nvarchar(500)
	DECLARE @ParmDefinition nvarchar(500)
	DECLARE @i_column nvarchar(500)
	DECLARE @p_table nvarchar(2000)
 	DECLARE @p_text_search nvarchar(2000)
	
	declare table_columns cursor for
	select c.name 
	  from sys.columns c
		   inner join sys.tables t on c.object_id = t.object_id
	where t.name = @p_table

	OPEN table_columns
	FETCH NEXT FROM table_columns 
	INTO @i_column
	while @@FETCH_STATUS = 0
	BEGIN
		SET @ParmDefinition = '@vcount int OUTPUT'

		set @sql = 'select  @vcount =COUNT(1) 
					  from '+@p_table+'
		  			 where '+@i_column+' = '''+@p_text_search+''''
		EXECUTE sp_executeSQL @sql,@ParmDefinition, @vcount=@v_count OUTPUT			   
							
		IF (@v_count > 0) 
		  begin
		    CLOSE table_columns
		    DEALLOCATE table_columns
		    PRINT @i_column
                    BREAK
		  end		  
		FETCH NEXT FROM  table_columns into @i_column
	END
	CLOSE table_columns
	DEALLOCATE table_columns
END
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Josep Coves Barreiro

-- Create date: 10/02/2015

-- Description: Returns the first column of the table which contains a search value

-- =============================================

BEGIN

DECLARE @v_count int

DECLARE @v_table varchar(200)

DECLARE @sql nvarchar(500)

DECLARE @ParmDefinition nvarchar(500)

DECLARE @i_column nvarchar(500)

DECLARE @p_table nvarchar(2000)

DECLARE @p_text_search nvarchar(2000)

declare table_columns cursor for

select c.name

from sys.columns c

inner join sys.tables t on c.object_id = t.object_id

where t.name = @p_table

OPEN table_columns

FETCH NEXT FROM table_columns

INTO @i_column

while @@FETCH_STATUS = 0

BEGIN

SET @ParmDefinition = '@vcount int OUTPUT'

set @sql = 'select @vcount =COUNT(1)

from '+@p_table+'

where '+@i_column+' = '''+@p_text_search+''''

EXECUTE sp_executeSQL @sql,@ParmDefinition, @vcount=@v_count OUTPUT

IF (@v_count > 0)

begin

CLOSE table_columns

DEALLOCATE table_columns

PRINT @i_column

BREAK

end

FETCH NEXT FROM table_columns into @i_column

END

CLOSE table_columns

DEALLOCATE table_columns

END

Comentaris tancats

RELATIONAL

APEX 5: Showing custom success message from Dynamic Action (DA) on Universal Theme

SQL Server: Find first column with specified value

Search

Categories

Twitter

Archives