SQLServer Tip's - Procedure, rename table, xml, dynamic SQL

1. Introduction

I'm just gathering references to well done post and documentation about SQLServer.

2. References

2.1. Stored Procedure - with parameters

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_edi_transf_dummy')

DROP PROCEDURE sp_edi_transf_dummy 

GO

CREATE PROCEDURE sp_edi_transf_dummy

  @pIdLoteEdi int 

AS 

    SET NOCOUNT ON;

    INSERT INTO coc_param_proc_lote_edi ( id_lote_edi, param, id )

    VALUES (@pIdLoteEdi, 'proc_transf_dummy', @pIdLoteEdi);

GO

2.2. Rename Table Column

sp_RENAME 'my_table_name.old_column_name', 'new_column_name' , 'COLUMN'

2.3. Oracle equivalents in SQLServer

• http://jopinblog.wordpress.com/2007/04/24/oracle-plsql-equivalents-for-ms-sql-server-t-sql-constructs/

2.4. String, Date and Time conversion in SQLServer

• http://sqlusa.com/bestpractices2005/sqlserverstringtodate/
• http://www.sqlusa.com/bestpractices/datetimeconversion/
• http://www.karaszi.com/sqlserver/info_datetime.asp

2.5. String and Int conversion in SQLServer

• http://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/

2.6. Dynamic SQL

• http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/
• http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure
• http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable
• http://www.sqlservercentral.com/Forums/Topic817615-8-1.aspx

2.7. XML

• http://social.msdn.microsoft.com/Forums/sqlserver/en-US/688d3044-2072-4912-b5ba-907c2660ba16/exporting-a-table-to-an-xml-file-and-getting-the-xml-schema-for-the-table
• http://stackoverflow.com/questions/11699914/concatenate-xml-without-type-casting-to-string

Java Tip's - JOptionPane, JDBC call to SqlServer Stored Procedure

1. Introduction

I'm just gathering references to well done post and documentation about Java.

2. References

2.1. JOptionPane - Java MessageBox

• http://www.tiexpert.net/programacao/java/joptionpane.php
• http://docs.oracle.com/javase/7/docs/api/javax/swing/JOptionPane.html
• http://docs.oracle.com/javase/tutorial/uiswing/components/dialog.html
• http://stackoverflow.com/questions/8396870/joptionpane-yes-or-no-window

2.2. JDBC call to Sqlserver Stored Procedure with parameters

• http://stackoverflow.com/questions/6113674/how-do-i-execute-a-ms-sql-server-stored-procedure-in-java-jsp-returning-table-d

2.3. Reading and Writting Excel files using apache POI

• http://poi.apache.org/
• http://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/
• http://stackoverflow.com/questions/10631423/how-to-read-from-first-to-last-row-in-excel-using-poi-in-java
• http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html
• http://stackoverflow.com/questions/4683828/hhmmss-xls-reading-using-apache-poi

2.4. JDBC Sqlserver Getting value of last AUTOINC sequence column inserted

• http://www.coderanch.com/t/534142/JDBC/databases/getGeneratedKeys-causing-exception

pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
int insertCount = pstmt.executeUpdate(); // execute insert statement
System.out.println("Insert Count:" + insertCount);
ResultSet resultSet = pstmt.getGeneratedKeys();
if (resultSet != null && resultSet.next()) {
logId = resultSet.getLong(1);
}