sql scripts

CREATE FUNCTION FN_HEXTOBINARY (@HEX VARCHAR(8))
	RETURNS VARCHAR(255)
AS


    BEGIN
    DECLARE @BASE TINYINT
    DECLARE @string VARCHAR(255)
    DECLARE @return VARCHAR(255)
    DECLARE @div INT
    DECLARE @char CHAR(1)
    DECLARE @pos INT
    DECLARE @digit INT
    DECLARE @result varchar(16)
    SELECT @string= '0123456789ABCDEF'
    SELECT @pos	= 1
    SELECT @result= ''
    SELECT @return= ''
    	select @base=2
    		
    	while (@pos<>len(@hex)+1)
    	begin
    	if substring(@hex,@pos,1) not in ('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F')
    	begin
    		return NULL
    	end
    	select @digit=charindex(substring(@hex,@pos,1),@string)-1
    		WHILE (@digit<>0)
    		BEGIN
    			select @div=@digit/@base
    			select @result=convert(varchar(1),(@digit % @base)) + @result 
    			select @digit=@div
    		END
    		select @return=@return + case when len(@result)<4 then replace(space(4-len(@result)) + @result,' ','0') else @result end
    		select @result=''
    		select @pos=@pos+1
    	end 
    RETURN @return
END

declare @t varchar (8)
select @t = 'FFFFFFFF'
select sum(
case lower( substring( hexstr , number , 1 ) )
	when '0' then 0
	when '1' then 1
	when '2' then 2
	when '3' then 3
	when '4' then 4
	when '5' then 5
	when '6' then 6
	when '7' then 7
	when '8' then 8
	when '9' then 9
	when 'a' then 10
	when 'b' then 11
	when 'c' then 12
	when 'd' then 13
	when 'e' then 14
	when 'f' then 15
end * convert( decimal( 28 , 0 ) , power( 16 , number - 1 ) )
)
from 
(
select reverse( @t ) as hexstr
) as h1
join
Numbers n
on number between 1 and len( hexstr )
go
-- In SQL6x & above
declare @hexstr varchar( 10 ) , @intval int
select @hexstr = '0x2A'
exec('declare intcur cursor for select convert( int , ' + @hexstr + ' )')
open intcur
fetch intcur into @intval
deallocate intcur
select @intval
go
-- In SQL70
declare @hexstr varchar( 10 ) , @intval int , @cmdstr nvarchar( 255 )
select @hexstr = '0x2A'
Select @cmdstr = N'Select @intval = convert( int , ' + @hexstr + ' )'
Exec sp_ExecuteSql @cmdstr, N'@intval Int Out', @intval Out
select @intval
go

For Creating the Reverse of the String Within the Oracle Database 
Using the Standard Utl_Raw Package Supplied With the Oracle. To 
Be Able to Use this Function You must have the Execute Permission 
To the UTL_RAW Package.
The input Parameter is the String and the Output Produced is the 
Reverse of that String.
CREATE OR REPLACE FUNCTION Reverse(
stringin IN VARCHAR2 )
RETURN VARCHAR2
as
stringout varchar2(2000);


    Begin
    sELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW(stringin))) into stringout From dual;
    return(stringout);
End;

CREATE PROCEDURE spSaveTextResultToFile 
@Mytbl VARCHAR(50),
@TheSQL VARCHAR(MAX),
@Filename VARCHAR(255),
@Unicode INT=0
AS
SET NOCOUNT ON
--Declare Variables used in the Procedure
DECLARE @MySpecialTempTable VARCHAR(255)
DECLARE @Command NVARCHAR(4000)
DECLARE @RESULT INT
	
IF CHARINDEX ('Select ',LTRIM(@TheSQL))=0


    BEGIN
    RAISERROR ('Usage spSaveTextResultToFile <The SQL Expression> <The Filename>)',16,1)
    RETURN 1
END
--firstly we create a global temp table with a unique name
SELECT @MySpecialTempTable = '##temp'
+ CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
--then we create it using dynamic SQL with a Cursor,
--CURSOR DECLARATION STARTS HERE
--Declare Variables
Declare @FieldName varchar(50)
DECLARE @tablename AS VARCHAR(100)
DECLARE @Columns VARCHAR(4000)
DECLARE @Query AS VARCHAR(4000)
SET @tableName = @MyTbl
--Declare Cursor
Declare Crate_Table Cursor Static
FOR
--Select the columns joining on the SYSTEMS table
SELECT
clmns.name As [Column Name]
FROM
sys.tables tbl
INNER JOIN sys.all_columns clmns 
ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types usrt 
ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types baset 
ON baset.user_type_id = clmns.system_type_id
AND baset.user_type_id = baset.system_type_id
WHERE
tbl.name= @TableName 
AND SCHEMA_NAME(tbl.schema_id)=N'dbo'
--ORDER BY [Column Name] ASC
--Loop through all the Records/ColumnNames
Open Crate_Table
SET @Query = 'Create Table ['+ @MySpecialTempTable + ']('
Fetch from Crate_Table
Into @FieldName
while @@Fetch_Status = 0


    BEGIN
    	SET @Query = @Query + '[' + @fieldName + '] varchar(50), '
    Fetch from Crate_Table
    Into @FieldName
END
--Set the query syntax
SELECT @Command = SUBSTRING(@Query,1,LEN(@query) - 1) + ')'
	PRINT 'Select * from ' + @MySpecialTempTable 
EXECUTE sp_ExecuteSQL @command
	--Insert values from the query into the table
EXECUTE ( 'Insert into [' + @MySpecialTempTable + '] SELECT * FROM [' + @mytbl + ']')
-- SELECT @Command = 'Insert into [' + @MySpecialTempTable + '] SELECT * FROM [' + @mytbl + ']'

--then we execute the BCP to save the file
SELECT @Command = 'bcp " '+ @TheSQL +' from ['
+ @MySpecialTempTable + ']'
--SELECT @Command = 'bcp "' + @TheSql
+ '" queryout '
+ @Filename
+ CASE WHEN @Unicode=0 THEN ' -c' ELSE ' -w' END
+ ' -T -S' + @@SERVERNAME
PRINT @Command
EXECUTE @RESULT= MASTER..xp_cmdshell @command, NO_OUTPUT
PRINT @result 
--EXECUTE ( 'Drop table [' + @MySpecialTempTable + ']' ) 

close Crate_Table
deallocate Crate_Table
--DEALLOCATE THE CURSOR
GO 

spSaveTextResultToFile 'Region','Select * ', 'C:\Region_NW.txt','0'
GO