对于jdbc和python下调用sqlserver的存储过程感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解python用jdbc连接数据库，并且为您提供关于.net下调用sqlserver

对于jdbc和python下调用sqlserver的存储过程感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解python用jdbc连接数据库，并且为您提供关于.net下调用sqlserver存储过程的小例子、ASP 调用sqlserver存储过程学习教程、C#调用ODBC连接SQL Server数据库的存储过程、C#调用SQL Server中有参数的存储过程的宝贵知识。

• jdbc和python下调用sqlserver的存储过程（python用jdbc连接数据库）
• .net下调用sqlserver存储过程的小例子
• ASP 调用sqlserver存储过程学习教程
• C#调用ODBC连接SQL Server数据库的存储过程
• C#调用SQL Server中有参数的存储过程

jdbc和python下调用sqlserver的存储过程（python用jdbc连接数据库）

一、环境（docker  mssql-server-linux）

具体不再描述：参见本人博文unbuntu下Docker安装oracle和mysql，sqlserver中的第七部分。

二、创建mydb库，并把owner改为mymotif：

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "wxwpxh01!" 
1> CREATE DATABASE mydb COLLATE Chinese_PRC_CI_AS;
2>go
1> use mydb
2> go
1>EXEC sp_changedbowner ''mymotif'';  
2> go 
-- 或直接赋予mymotif具有sysadmin权限(再mymotif登录后建立数据库mydb)
1> EXEC master.dbo.sp_addsrvrolemember @loginame = N''mymotif'',   @rolename = N''sysadmin''
2> go
1>exit
mymotif登陆后建立表：
docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U mymotif -P "wxwpxh01!" -d mydb
1>use mydb (不是必须，sqlcmd已有-d参数)
2>go
1> CREATE TABLE CUSTOMERS(ID   INT NOT NULL,   NAME VARCHAR (20) NOT NULL, AGE  INT NOT NULL, ADDRESS  CHAR (25) , SALARY   DECIMAL (18, 2), PRIMARY KEY (ID))
2> go
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'', 23, ''Kota'', 2000.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ''Hardik'', 27, ''Bhopal'', 8500.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)  VALUES (7, ''Muffy'', 24, ''Indore'', 10000.00 );
go
建立存储过程并测试：
1> CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM  Customers 
2> go
1> exec SelectAllCustomers
2> go
ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad                	2000.00
2	Khilan	25	Delhi                    	1500.00
3	kaushik	23	Kota                     	2000.00
4	Chaitali	25	Mumbai                   	6500.00
5	Hardik	27	Bhopal                   	8500.00
6	Komal	22	MP                       	4500.00
7	Muffy	24	Indore                   	10000.00
(7 rows affected)
(return status = 0)

三、不带参数的代码：

package www.zjptcc.wxw.jdbc;

/*
 *     
use mydb
1> CREATE TABLE CUSTOMERS(ID   INT NOT NULL,   NAME VARCHAR (20) NOT NULL, AGE  INT NOT NULL, ADDRESS  CHAR (25) , SALARY   DECIMAL (18, 2), PRIMARY KEY (ID))
2> go
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ''kaushik'', 23, ''Kota'', 2000.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ''Hardik'', 27, ''Bhopal'', 8500.00 );  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)  VALUES (7, ''Muffy'', 24, ''Indore'', 10000.00 );
go
1> CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM  Customers 
2> go
 * 
 * */

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestMsSp1 {

   /**
    * @param args
    */
   static String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=mydb";	
   public static void main(String[] args) {
       // TODO 自动生成的方法存根
	   Connection conn = null;
       try {
           Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
           conn = DriverManager.getConnection(url, "mymotif", "wxwpxh01!");
           

			CallableStatement cs=conn.prepareCall("{call SelectAllCustomers()}");
			ResultSet rs=cs.executeQuery();
			while (rs.next()) {
				String name=rs.getString("NAME");
				String address=rs.getString("ADDRESS");
				String UserId=rs.getString("ID");
				System.out.println(name+"\t"+address+"\t"+UserId);
			}
			System.out.println("查询成功");
			System.out.println("-----------------------");

           conn.close();
       } catch (SQLException sqe) {
			System.out.println("Unexpected exception : " + sqe.toString()
					+ ", sqlstate = " + sqe.getSQLState());
			System.exit(1);
			} catch (Exception e) {
           System.out.println(e.getMessage());
       }
   }

}

四、运行

五、带参数的存储过程（利用AdventureWorks 示例数据库）

安装示例数据库：微软官网上去下载AdventureWorks2017.bak

docker cp AdventureWorks2017.bak eb86a8500f0c:/var/opt/mssql/data/.
~$ tsql -Sdockermssql  -U sa -Pwxwpxh01!
locale is "zh_CN.UTF-8"
locale charset is "UTF-8"
using default charset "utf8"
1> RESTORE FILELISTONLY FROM DISK= ''./AdventureWorks2017.bak''
2> go
LogicalName	PhysicalName	Type	FileGroupName	Size	MaxSize	FileId	CreateLSN	DropLSN	UniqueId	ReadOnlyLSN	ReadWriteLSN	BackupSizeInBytes	SourceBlockSize	FileGroupId	LogGroupGUID	DifferentialBaseLSN	DifferentialBaseGUID	IsReadOnly	IsPresent	TDEThumbprint	SnapshotUrl
AdventureWorks2017	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017RTM\MSSQL\DATA\AdventureWorks2017.mdf	D	PRIMARY	276824064	35184372080640	1	0	0	733940A8-D019-4DC5-80F8-13E869A504EC	0	0	215285760	512	1	NULL	000000000-0000-0000-0000-000000000000	0	1	NULL	NULL
AdventureWorks2017_log	C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQL2017RTM\MSSQL\DATA\AdventureWorks2017_log.ldf	L	NULL	75497472	2199023255552	2	0	0	B166C891-E43F-42DA-87FC-8D7F34022B35	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1	NULL	NULL
(2 rows affected)
1> RESTORE DATABASE AdventureWorks FROM DISK=''./AdventureWorks2017.bak'' WITH MOVE ''AdventureWorks2017'' TO ''/var/opt/mssql/data/AdventureWorks2017.mdf'', MOVE ''AdventureWorks2017_log'' TO ''/var/opt/mssql/data/AdventureWorks2017_log.ldf

j代码：

package www.zjptcc.wxw.jdbc;

import java.sql.*;

public class MssqlAdventureWorksp1 {
	public static void main(String[] args) {

		// Create a variable for the connection string.
		// String connectionUrl =
		// "jdbc:sqlserver://<server>:<port>;databaseName=AdventureWorks;user=<user>;password=<password>";
		String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=AdventureWorks;user=mymotif;password=wxwpxh01!";

		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			Connection con = DriverManager.getConnection(connectionUrl);

			CallableStatement  stmt = con.prepareCall("{call uspGetEmployeeManagers(?)}");
			stmt.registerOutParameter(1, Types.INTEGER);
			stmt.setInt(1, 50); 								//设置参数
			stmt.execute();
			ResultSet rs =  stmt.getResultSet();

			// Iterate through the data in the result set and display it.
			while (rs.next()) {
				System.out.println(rs.getString("RecursionLevel") + " " + rs.getString("FirstName")+ " " + rs.getString("LastName"));
			}
			
            // 完成后关闭
            rs.close();
            stmt.close();
            con.close();
		} catch (ClassNotFoundException e1) {
			// TODO 自动生成的 catch 块
			e1.printStackTrace();
		} // Handle any errors that may have occurred.
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

运行截图：

官网上jdbc的例子（jdbc解压后在sqljdbc_8.2\chs/samples/connections/ConnectDataSource.java）：

package www.zjptcc.wxw.jdbc.mssql;

/*=====================================================================
File: 	 ConnectDataSource.java
Summary: This Microsoft JDBC Driver for SQL Server sample application
         demonstrates how to connect to a SQL Server database by 
	     using a data source object.
---------------------------------------------------------------------
This file is part of the Microsoft JDBC Driver for SQL Server Code Samples.
Copyright (C) Microsoft Corporation.  All rights reserved.
 
This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.
 
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
=====================================================================*/
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class ConnectDataSource {

    public static void main(String[] args) {

        // Create datasource.
        SQLServerDataSource ds = new SQLServerDataSource();
        /*
        ds.setUser("<user>");
        ds.setPassword("<password>");
        ds.setServerName("<server>");
        ds.setPortNumber(Integer.parseInt("<port>"));
        */
        ds.setUser("mymotif");
        ds.setPassword("wxwpxh01!");
        ds.setServerName("127.0.0.1");
        ds.setPortNumber(Integer.parseInt("1433"));
        ds.setDatabaseName("AdventureWorks");

        try (Connection con = ds.getConnection();
                CallableStatement cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");) {
            // Execute a stored procedure that returns some data.
            cstmt.setInt(1, 50);
            ResultSet rs = cstmt.executeQuery();

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println("EMPLOYEE: " + rs.getString("LastName") + ", " + rs.getString("FirstName"));
                System.out.println("MANAGER: " + rs.getString("ManagerLastName") + ", " + rs.getString("ManagerFirstName"));
                System.out.println();
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

六、python下调用：

代码

#coding=utf-8
import pymssql

conn =  pymssql.connect(host=''127.0.0.1'',user=''mymotif'',password=''wxwpxh01!'',database=''mydb'',charset="utf8")
cur = conn.cursor() 			
cur.execute("exec SelectAllCustomers")          #cursor.execute("exec 存储过程名称 @参数1=''xxx'',@参数2=''xxx''")
info = cur.fetchall() 
print (len(info))   	#获得表中有多少条数据
for ii in info:
    print (str(ii[0])+'' ''+ii[1]+'' ''+str(ii[2])+'' ''+ii[3]+'' ''+str(ii[4]))
cur.close()
conn.commit()
conn.close()

运行截图：

七、go语言访问SqlServer

安装驱动（可能要翻墙，我把denisenkom打包到网盘上，直接解压到$GOPATH/src/github.com就应该可以用了）

$ cd $GOPATH
 $go get github.com/denisenkom/go-mssqldb

代码：

// mssqlt1.go
// go get github.com/denisenkom/go-mssqldb

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/denisenkom/go-mssqldb"
)

func main() {
    var isdebug = true
    var server = "127.0.0.1"
    var port = 1433
    var user = "sa"
    var password = "wxwpxh01!"
    var database = "AdventureWorks"

    //连接字符串
    connString := fmt.Sprintf("server=%s;port%d;database=%s;user id=%s;password=%s", server, port, database, user, password)
    if isdebug {
        fmt.Println(connString)
    }
    //建立连接
    conn, err := sql.Open("mssql", connString)
    if err != nil {
        log.Fatal("Open Connection failed:", err.Error())
    }
    defer conn.Close()

    //产生查询语句的Statement
    stmt, err := conn.Prepare(`SELECT * FROM Person.ContactType`)
    if err != nil {
        log.Fatal("Prepare failed:", err.Error())
    }
    defer stmt.Close()

    //通过Statement执行查询
    rows, err := stmt.Query()
    if err != nil {
        log.Fatal("Query failed:", err.Error())
    }

    //建立一个列数组
    cols, err := rows.Columns()
    var colsdata = make([]interface{}, len(cols))
    for i := 0; i < len(cols); i++ {
        colsdata[i] = new(interface{})
        fmt.Print(cols[i])
        fmt.Print("\t")
    }
    fmt.Println()

    //遍历每一行
    for rows.Next() {
        rows.Scan(colsdata...) //将查到的数据写入到这行中
        PrintRow(colsdata)     //打印此行
    }
    defer rows.Close()
}

//打印一行记录，传入一个行的所有列信息
func PrintRow(colsdata []interface{}) {
    for _, val := range colsdata {
        switch v := (*(val.(*interface{}))).(type) {
        case nil:
            fmt.Print("NULL")
        case bool:
            if v {
                fmt.Print("True")
            } else {
                fmt.Print("False")
            }
        case []byte:
            fmt.Print(string(v))
        case time.Time:
            fmt.Print(v.Format("2016-01-02 15:05:05.999"))
        default:
            fmt.Print(v)
        }
        fmt.Print("\t")
    }
    fmt.Println()
}

运行：

$ go run mssqlt1.go
server=127.0.0.1;port1433;database=AdventureWorks;user id=sa;password=wxwpxh01!
ContactTypeID	Name	ModifiedDate	
1	Accounting Manager	30046-04-30 00:00:00	
2	Assistant Sales Agent	30046-04-30 00:00:00	
3	Assistant Sales Representative	30046-04-30 00:00:00	
4	Coordinator Foreign Markets	30046-04-30 00:00:00	
5	Export Administrator	30046-04-30 00:00:00	
6	International Marketing Manager	30046-04-30 00:00:00	
7	Marketing Assistant	30046-04-30 00:00:00	
8	Marketing Manager	30046-04-30 00:00:00	
9	Marketing Representative	30046-04-30 00:00:00	
10	Order Administrator	30046-04-30 00:00:00	
11	Owner	30046-04-30 00:00:00	
12	Owner/Marketing Assistant	30046-04-30 00:00:00	
13	Product Manager	30046-04-30 00:00:00	
14	Purchasing Agent	30046-04-30 00:00:00	
15	Purchasing Manager	30046-04-30 00:00:00	
16	Regional Account Representative	30046-04-30 00:00:00	
17	Sales Agent	30046-04-30 00:00:00	
18	Sales Associate	30046-04-30 00:00:00	
19	Sales Manager	30046-04-30 00:00:00	
20	Sales Representative	30046-04-30 00:00:00	

参考：

微软JDBC 驱动程序 API 参考

.net下调用sqlserver存储过程的小例子