2019년 6월 24일 월요일

python pymssql procedure call - callproc - output parameter - 2 cases (set only, set-select)

 mssql procedure
----------------------------------------------------
CREATE PROCEDURE [dbo].[proc_name]
@pnum int null,
@pstr nvarchar(50) null,
@rnum int null output,
@rstr nvarchar(50) null output

AS
BEGIN
SET NOCOUNT ON;

set @rnum = @pnum + 10;
set @rstr = 'passed:' + @pstr;
        -- case 1. no select
-- case 2. SELECT @rnum as result_number, @rstr as result_string
END

--- python - pymssql

#-*- encoding: utf-8 -*-
import pymssql

connection = pymssql.connect('ip address', 'user', 'password', "database")
cursor = connection.cursor()

params = (1, 'str', pymssql.output(int,0), pymssql.output(str,''))
respose = cursor.callproc('dbo.proc_name',params)

print response
print response[2]
print response[3]

for row in cursor:
print row[0]
print row[1]

============================= result compare

case 1.
-----------------------------------------------
(1, 'str', 10, u'passed:str')
10
passed:str
---
case 2.
-----------------------------------------------
(1, 'str', 0, '')
0
<--- ''
10
passed:str
---

댓글 없음:

댓글 쓰기