SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database

http://blog.sqlauthority.com/2007/09/16/sql-server-2005-list-all-the-constraint-of-database-find-primary-key-and-foreign-key-constraint-in-database/

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)
Ads by Google
免費 SQL Server 2008 軟件
Get Free Software by Attending SQL Server 2008 Official Training
www.welkin.com.hk/promotions
Mssql Monitoring
Monitor SQL Server Performance Track DB usage. Download Now!
manageengine.adventnet.com
MS SQL Repair SW
Repair Corrupt MDF Database Files Instant Repair, Free Demo Preview
www.mssqldatabaserecovery.com
Sql Server
Ready to learn SQL Server? Try our free videos before you buy!
cbtnuggets.com


Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 25 Comments
25 Responses to “SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database”

1.
on September 16, 2007 at 11:07 pm abhay

Dear Pinal,

To get the idea of any relationship between tables using foreign key relationships, I think, following query will be more useful :

select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

Regards,
Abhay

2.
on September 30, 2007 at 10:38 pm Ujjaval Suthar

Hey Pinal,

I was going through google to look for the query that you’ve posted here and came across your blog.

Its surprising to see you and remember your face from Nirma Bus, if my memory is not wrong. I was studying in Nirma between 1999 - 2003. And you are also from Gandhingar, right? yeah I am from Gandhinagar.

Regards,
Ujjaval

PS:- Oh by the way, useful post. Thanks for that.

3.
on October 2, 2007 at 6:03 pm pinaldave

Hi Ujjaval Suthar,

Yes, I am Pinal from Gandhinagar. Nirma 1999-2003.

Regards,
Pinal

4.
on October 23, 2007 at 7:43 am Rob

This query works and was very helpful to me. Thanks.

5.
on December 21, 2007 at 1:11 am Shishir Khandekar

For SQL Server 2005, you can use the sys.foreign_keys view to achieve the same. The columns in this view are self explanatory so am not publishing the query here.

Regards
Shishir

6.
on December 21, 2007 at 5:47 pm Sandeep

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

7.
on December 21, 2007 at 6:11 pm Sandeep

sir,
i got my answer, initially i didn’t search in sqlauthority.com!!

thx

8.
on December 28, 2007 at 2:25 pm rajesh

Hello Sir,
I am a .net Developer and I came across some difficult questions during the interview on database. The question
is :
There is a table with 5 coloumns, in that under country coloumn the fields should be all asain countries. If the user
enters other than asian country it should throw an error.
How to write a query for this?

9.
on January 18, 2008 at 7:21 pm Tom W

1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.

10.
on February 22, 2008 at 3:20 pm szolarp

Hi!
I made an easier to understand script that shows all constraint in the database including that rows where the referencing_column_name and the referenced_column_name are different and the name of the constraint
select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id

11.
on March 27, 2008 at 10:44 pm Tushar Mehere

Hi szolarp,

Your query is prefect. It had some defect, i refined it below.

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name=’tblUserDetails’ — this predicate for a specific table

12.
on March 29, 2008 at 7:42 am Aviv

Hi
Does anyone know how can I find the relationship between colomn and default constraint. I need it for dynamicly drop the column in different databases

13.
on April 16, 2008 at 4:34 pm Rakesh Dewangan

Hi Pinal,
really this procedure its very very useful,
I was facing trouble from last half an hour, and your query has resolved my problem, with in second.

thanks a lot.

great work!!!!

14.
on May 5, 2008 at 2:57 pm Henko

Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.

15.
on May 12, 2008 at 5:07 pm Gerry

This blog helped me a lot! Many thanks to you all.

I was looking for days to solve my constraint problem.
this is very usefull in my ADO application. Looks like ADO does not have functions to read detailed constraint information like this script from an SQL database. Therefore I was forced to search for a solution in SQL scripting and here it was!

Thx!

16.
on May 24, 2008 at 6:16 pm Niraimathi

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint “hrpyprc_ps_auth_r01_fk”. The conflict occurred in database “HRMS40_CBI”, table “dbo.hrpyprc_payset_auth_cnt”.
The statement has been terminated.

17.
on June 23, 2008 at 2:41 pm Tim4it

szolarp & Tushar

Many thanks .. you really helped me ..

18.
on August 4, 2008 at 5:00 pm kalyan

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

19.
on August 7, 2008 at 4:47 pm Ankush

What r the differences b/w Sql Server 2005 and 2000

20.
on August 26, 2008 at 10:04 am Muhammad Usman Arshad

Hi,

I think using information schema is much useful than using sysobjects. To see the list of constraints you can use the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

or you can use database name with it:

SELECT * FROM [SugarCMS].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Here is the complete list of information_schema views:

CHECK_CONSTRAINTS: Check Constraints
COLUMN_DOMAIN_USAGE: Every column that has a user-defined data type.
COLUMN_PRIVILEGES: Every column with a privilege granted to or by the current user in the current database.
COLUMNS:Lists every column in the system
CONSTRAINT_COLUMN_USAGE: Every column that has a constraint defined on it.
CONSTRAINT_TABLE_USAGE: Every table that has a constraint defined on it.
DOMAIN_CONSTRAINTS: Every user-defined data type with a rule bound to it.
DOMAINS: Every user-defined data type.
KEY_COLUMN_USAGE: Every column that is constrained as a key
PARAMETERS: Every parameter for every user-defined function or stored procedure in the datbase. For functions this returns one row with return value information.
REFERENTIAL_CONSTRAINTS: Every foreign constraint in the system.
ROUTINE_COLUMNS: Every column returned by table-valued functions.
ROUTINES: Every stored procedure and function in the database.
SCHEMATA: Every database in the system.
TABLE_CONSTRAINTS: Every table constraint.
TABLE_PRIVILEGES: Every table privilege granted to or by the current user.
TABLES: Every table in the system.
VIEW_COLUMN_USAGE: Every column used in a view definition.
VIEW_TABLE_USAGE: Every table used in a view definition.
VIEWS: Every View

21.
on August 29, 2008 at 10:27 pm Syed

Hi,
Does any one know how to get DEFAULT Constraint along with the name of the column on which it has defined.
Nirmal query gives the table name, but not the column name, because most of the time DEFAULT is created on the column by using DEFAULT, which create the system generated name of the DEFAULT, so I would like to know how to get the column name along with the table name.

I would appreciate any suggestion.

Thanks

22.
on August 30, 2008 at 3:44 am Imran Mohammed

@SYED,

This might help,

SELECT OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID)COLUMN_NAME ,
NAME DEFAULT_CONSTRAINT_NAME
FROM SYS.DEFAULT_CONSTRAINTS ORDER BY 1

Hope this helps,
Imran.

23.
on September 12, 2008 at 10:51 pm Arthur

I think is easier… it’s pretty much like the one publish by szolarp and “fixed” by Tushar Mehere…

is this:
SELECT RO.NAME AS ParentTable, RC.NAME AS ParentColumn, FO.NAME AS ForeignTable, FC.NAME AS ForeignColumn
FROM sysforeignkeys F INNER JOIN sysobjects RO
ON F.rkeyid = RO.id INNER JOIN syscolumns RC
ON RC.id = RO.id AND RC.colid = F.rkey INNER JOIN sysobjects FO
ON F.fkeyid = FO.id INNER JOIN syscolumns FC
ON FC.id = FO.id AND FC.colid = F.fkey
ORDER BY RO.NAME, RC.NAME, FO.NAME, FC.NAME

we don’t really to know the name…

24.
on September 27, 2008 at 4:17 am Mark

Hi Pinal,
I have a situation wherein I need to change a particular name in my entire database.
Please tell me how can i trace this particular word - it might be occuring in ‘n’ number of tables in the database, in ‘n’ number of column values and in ‘n’ number of records throughout present in any table of that database.
I need to write a cursor, go to each record and trace that word, likewise for others, for each table. I heard that sysobjects and syscolumns could help in this scenario, could you please generate the code if possible.

Thanks, Mark

Java中调用SQL Server存储过程示例

2007-09-03 08:48作者：孟子E章出处：论坛整理责任编辑：方舟

　 　最近做了个Java的小项目（第一次写Java的项目哦）， 到网上搜索了半天，找到了一个比较好点的调用存储过程的例子，而且网上普遍采用的都是setXXX((int parameterIndex, XXX x)的形式。这种形式感觉不是很直观，下面就发布一个完整的采用setXXX(String parameterName, XXX x)的编写方法。创建数据表，存储过程的代码都完整发布。

　　创建表：

CREATE TABLE [BookUser] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BookUser_Guid] DEFAULT (newid()), [BirthDate] [datetime] NOT NULL , [Description] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL , [Photo] [image] NULL , [Other] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BookUser_Other] DEFAULT ('默认值'), CONSTRAINT [PK_BookUser] PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

　　创建存储过程：

CREATE PROCEDURE InsertUser @UserName varchar(50), @Title varchar(255), @Guid uniqueidentifier, @BirthDate DateTime, @Description ntext, @Photo image, @Other nvarchar(50), @UserID int output As Set NOCOUNT ON If Exists (select UserID from BookUser Where UserName = @UserName) RETURN 0 ELSE Begin INSERT INTO BookUser (UserName,Title,Guid,BirthDate,Description,Photo,Other) VALUES(@UserName,@Title,@Guid,@BirthDate,@Description,@Photo,@Other) SET @UserID = @@IDENTITY RETURN 1 End GO

　　JSP代码：

＜%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%＞
＜%@ page import = "java.sql.*"%＞
＜!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"＞
＜html xmlns="http://www.w3.org/1999/xhtml"＞
＜head＞
＜/head＞
＜body＞
＜%
//注意：下面的连接方法采用最新的SQL Server的JDBC，
//请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password=";
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
try
{
　cn = DriverManager.getConnection(url);
　cmd = cn.prepareCall(sql);
　java.util.UUID Guid = java.util.UUID.randomUUID();
　String FilePath = application.getRealPath("") + "\test\logo.gif";
　java.io.FileInputStream f = new java.io.FileInputStream(FilePath);
　Date rightNow = Date.valueOf("2007-9-9");
　cmd.setString("UserName","mengxianhui"); //注意修改这里，存储过程验证了UserName的唯一性。
　cmd.setString("Title","孟宪会");
　cmd.setString("Guid",Guid.toString());
　cmd.setString("BirthDate","2007-9-9");
　cmd.setDate("BirthDate",rightNow);
　cmd.setString("Description","【孟子E章】");
　cmd.setBinaryStream("Photo",f,f.available());
　cmd.setString("Other",null);
　cmd.registerOutParameter(1,java.sql.Types.INTEGER);
　cmd.registerOutParameter("UserID",java.sql.Types.INTEGER);
　cmd.execute();
　int returnValue = cmd.getInt(1);
　int UserID = cmd.getInt("UserID");
　if(returnValue == 1)
　{
　　out.print("＜li＞添加成功！");
　　out.print("＜li＞UserID = " + UserID);
　　out.print("＜li＞returnValue = " + returnValue);
　}
　else
　{
　　out.print("＜li＞添加失败！");
　}
　f.close();
}
catch(Exception ex)
{
　out.print(ex.getLocalizedMessage());
}
finally
{
　try
　{
　　if(cmd != null)　
　　{
　　　cmd.close();
　　　cmd = null;
　　}
　　if(cn != null)
　　{
　　　cn.close();
　　　cn = null;
　　}
　}
　catch(Exception e)
　{
　　e.printStackTrace();
　}
}
%＞
＜/body＞
＜/html＞

Running a batch file in Java.

import java.io.*;
import java.lang.Runtime;

/**
*
* @author yamin Si
*/
public class testCallExe {
public testCallExe(){
Runtime r=Runtime.getRuntime();
Process p=null;
try
{
p = r.exec(new String[]{"cmd","/c","start C:/temp/test.bat"});
BufferedReader input = new BufferedReader(new InputStreamReader(p.getInputStream()));

String line=null;

while((line=input.readLine()) != null) {
System.out.println(line);
}
System.out.println("Exit Value = " p.waitFor());//Method waitFor() will make the current thread to wait until the external program finish and return the exit value to the waited thread.

}

catch(Exception e){
System.out.println("error===" e.getMessage());
e.printStackTrace();
}
}
public static void main(String args[]) {
testCallExe test=new testCallExe();
}
}

MiniConnectionPoolManager - A lightweight standalone JDBC connection pool manager

http://www.source-code.biz/snippets/java/8.htm

The standard Java library (JDK 1.5) does not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like Apache Commons DBCP or c3p0, but these are huge complex packages. Modern JDBC drivers provide implementations of ConnectionPoolDataSource and PooledConnection. This makes it possible to build a much smaller connection pool manager.

MiniConnectionPoolManager is a lightweight JDBC connection pool manager. It may be used in Java servlets as well as in Java standalone applications. It only requires Java 1.5 (or newer) and has no dependencies on other packages.

API documentation:	MiniConnectionPoolManager.html
Source code:	MiniConnectionPoolManager.java
Test program:	TestMiniConnectionPoolManager.java
Download full package:	MiniConnectionPoolManager.zip
Related work 1:	org.opensolaris.auth.db.DbDataSource (by Alan Burlison), a DataSource wrapper class for MiniConnectionPoolManager, which can be used in JSP SQL tags.
Related work 2:	org.h2.jdbcx.JdbcConnectionPool (source code), a version of MiniConnectionPoolManager ported to Java 1.4 and adapted to H2 by Thomas Müller.

Examples of how to use the MiniConnectionPoolManager class

For H2 (embedded mode):

org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
dataSource.setURL ("jdbc:h2:file:c:/temp/testDB");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Apache Derby (embedded mode):

org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName ("c:/temp/testDB");
dataSource.setCreateDatabase ("create");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For JTDS:

net.sourceforge.jtds.jdbcx.JtdsDataSource dataSource = new net.sourceforge.jtds.jdbcx.JtdsDataSource();
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For the Microsoft SQL Server driver:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
// The sqljdbc 1.1 documentation, chapter "Using Connection Pooling", recommends to use SQLServerXADataSource instead of SQLServerConnectionPoolDataSource.
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Oracle (example for Thin driver):

oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setDriverType ("thin");
dataSource.setServerName ("server1.yourdomain.com");
dataSource.setPortNumber (1521);
dataSource.setServiceName ("db1.yourdomain.com");
dataSource.setUser ("system");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

Design pattern for working with JDBC connections

It is important to use error handling to ensure that Connection and Statement class objects are always closed, even when an exception occurs.

Example:

public static String getFirstName (int personKey) throws Exception {
  Connection connection = null;
  PreparedStatement statement = null;
  try {
     connection = poolMgr.getConnection();
     final String sql = "select firstName from person where personKey = ?";
     statement = connection.prepareStatement(sql);
     statement.setInt (1, personKey);
     ResultSet rs = statement.executeQuery();
     if (!rs.next()) throw new Exception ("Person not found);
     return rs.getString(1); }
   finally {
     if (statement != null) statement.close();
     if (connection != null) connection.close(); }}

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
Index

Java Using a Stored Procedure with Output Parameters

Using a Stored Procedure with Output Parameters

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. The Microsoft SQL Server 2005 JDBC Driver provides the SQLServerCallableStatement class, which you can use to call this kind of stored procedure and process the data that it returns.

When you call this kind of stored procedure by using the JDBC driver, you must use the call SQL escape sequence together with the prepareCall method of the SQLServerConnection class. The syntax for the call escape sequence with OUT parameters is the following:

{call procedure-name[([parameter][,[parameter]]...)]}

Note:
For more information about the SQL escape sequences, see Using SQL Escape Sequences.

When you construct the call escape sequence, specify the OUT parameters by using the ? (question mark) character. This character acts as a placeholder for the parameter values that will be returned from the stored procedure. To specify a value for an OUT parameter, you must specify the data type of each parameter by using the registerOutParameter method of the SQLServerCallableStatement class before you run the stored procedure.

The value that you specify for the OUT parameter in the registerOutParameter method must be one of the JDBC data types contained in java.sql.Types, which in turn maps to one of the native SQL Server data types. For more information about the JDBC and SQL Server data types, see Understanding the JDBC Driver Data Types.

When you pass a value to the registerOutParameter method for an OUT parameter, you must specify not only the data type to be used for the parameter, but also the parameter's ordinal placement or the parameter's name in the stored procedure. For example, if your stored procedure contains a single OUT parameter, its ordinal value will be 1; if the stored procedure contains two parameters, the first ordinal value will be 1, and the second ordinal value will be 2.

Note:
The JDBC driver does not support the use of CURSOR, SQLVARIANT, TABLE, and TIMESTAMP SQL Server data types as OUT parameters.

As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:

Copy Code

CREATE PROCEDURE GetImmediateManager
  @employeeID INT,
  @managerID INT OUTPUT
AS
BEGIN
  SELECT @managerID = ManagerID
  FROM HumanResources.Employee
  WHERE EmployeeID = @employeeID
END

This stored procedure returns a single OUT parameter (managerID), which is an integer, based on the specified IN parameter (employeeID), which is also an integer. The value that is returned in the OUT parameter is the ManagerID based on the EmployeeID that is contained in the HumanResources.Employee table.

In the following example, an open connection to the AdventureWorks sample database is passed in to the function, and the execute method is used to call the GetImmediateManager stored procedure:

Copy Code

public static void executeStoredProcedure(Connection con) {
  try {
     CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
     cstmt.setInt(1, 5);
     cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
     cstmt.execute();
     System.out.println("MANAGER ID: " + cstmt.getInt(2));
  }
  catch (Exception e) {
     e.printStackTrace();
  }
}

This example uses the ordinal positions to identify the parameters. Alternatively, you can identify a parameter by using its name instead of its ordinal position. The following code example modifies the previous example to demonstrate how to use named parameters in a Java application. Note that parameter names correspond to the parameter names in the stored procedure's definition:

Copy Code

public static void executeStoredProcedure(Connection con) {
  try {
     CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
     cstmt.setInt("employeeID", 5);
     cstmt.registerOutParameter("managerID", java.sql.Types.INTEGER);
     cstmt.execute();
     System.out.println("MANAGER ID: " + cstmt.getInt("managerID"));
     cstmt.close();
  }
  catch (Exception e) {
     e.printStackTrace();
  }

}

Note:
These examples use the execute method of the SQLServerCallableStatement class to run the stored procedure. This is used because the stored procedure did not also return a result set. If it did, the executeQuery method would be used.

Stored procedures can return update counts and multiple result sets. The Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, which states that multiple result sets and update counts should be retrieved before the OUT parameters are retrieved. That is, the application should retrieve all of the ResultSet objects and update counts before retrieving the OUT parameters by using the CallableStatement.getter methods. Otherwise, the ResultSet objects and update counts that have not already been retrieved will be lost when the OUT parameters are retrieved. For more information about update counts and multiple result sets, see Using a Stored Procedure with an Update Count and Using Multiple Result Sets.

See Also

SQL 92 dialect question: Insert or update a record if it already exists?

ben

View Public Profile

Send a private message to ben

Find all posts by ben

ANSWER(S):

#2 (permalink) 04-17-2006, 07:09 AM

Dimitar Posts: n/a

SQL 92 answer. Re: Insert or update a record if it already exists? I am afraid there is no sql92 answer to this question. For sq192 an approach would be to insert every row and use grouping functions for reporting.

Dimitar

#3 (permalink) 04-17-2006, 07:54 AM

ben Administrator Join Date: Mar 2007 Posts: 93

MySQL answer. Re: Insert or update a record if it already exists? MySQL has a special construct for this. Assume the 'username' column below is UNIQUE: INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com') ON DUPLICATE KEY UPDATE email = 'jo@email.com' The 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.

ben

View Public Profile

Send a private message to ben

Find all posts by ben

#4 (permalink) 05-08-2006, 12:20 AM

Rob137 Posts: n/a

T-SQL answer. Re: Insert or update a record if it already exists? How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end

Rob137

#5 (permalink) 09-18-2006, 07:35 AM

srinivas Posts: n/a

Oracle answer. Re: Insert or update a record if it already exists? merge INTO users U1 USING (U1.username = 'Jo' ) when matched then UPDATE SET U1.email = 'jo@email.com' when NOT matched then INSERT(U1.email, U1.username) VALUES('Jo', jo@email.com);

srinivas

#6 (permalink) 12-12-2006, 12:44 AM

Evil Overlord Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? INSERT INTO users (username) SELECT 'Jo' WHERE 'Jo' NOT IN (SELECT username FROM users) UPDATE users SET email = 'jo@email.com' WHERE username = 'Jo' I use this method a lot. Obviously, 'Jo' would usually either be a variable or a field selected from another table.

Evil Overlord

#7 (permalink) 05-07-2007, 05:19 PM

Unregistered Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? Quote: Originally Posted by Rob137 How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end This opption worked wonders! Thanks for posting

Unregistered

#8 (permalink) 05-30-2007, 07:57 AM

Unregistered Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? Quote: Originally Posted by Rob137 How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end (I assume there is a typo in the last statement, where ord_num should be set to 'FFF'). You should be VERY CAREFUL with things like this. If you can't afford to set the transaction isolation level to SERIALIZABLE, some other transaction could add the row with ord_num='FFF' after you've tested for its existence, but before you've inserted it. This way you end up with violated primary constraint and error in one of these transactions.

Unregistered

#9 (permalink) 10-03-2007, 07:22 PM

mplusch Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? This is a variation that works for tables with multiple primary keys. If you have a users table with columns of username, dept, and age, and primary keys of username and dept, then this will only insert a user if it doesn't exist already. INSERT INTO users (username, dept, age) SELECT username='mp', dept='tax', age=5 WHERE (SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax')=0; I initially found this syntax confusing, but here's how I 'parsed' it. This part returns 0 if it doesn't exist: SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax' This part creates a record-like row with static values and represents the record to insert: SELECT username='mp', dept='tax', age=5 The WHERE-clause following the above part will only return the value to insert if the count is 0.