SQL Server Update Query: Is it possible to get the return code?

Ignition
1

I am wondering if it's possible to get the return code from the stored procedure when I run an update query. "Update", in this context, is the Ignition query type, the actual procedure runs a couple of insert statements.

Stored Procedures return a code, depending on success or failure, and I would like to get the zero code on success to tell the user that the inserts were successful.

The update query takes a bunch of parameters, sends those to the sproc.

query = 'myUpdateQuery'
params = {about 40 items...}
system.perspective.runNamedQuery(query, params)

Query statement:

EXEC dbo.usp_InsertSurveyData  :p1,  :p2,  :p3,  :p4,  :p5,  :p6,  :p7, :p8, :p9,  :p10,  :p11,  :p12, 
	:p13,  :p14,  :p15,  :p16,  :p17,  :p18,  :p19,  :p20,  :p21,  :p22,  :p23,  :p24,  :p25,  :p26,  
	:p27,  :p28,  :p29,  :p30,  :p31,  :p32,  :p33,  :p34,  :p35,  :p36,  :p37,  :p38,  :p39,  :dept,  :shift,  :position

If I change this to a "Scalar Query" and attempt to get the result...it does not work.

Anyone know of a way to do this?

2

Did you check the return code you are currently getting?

(You may need to script the procedure call and use an output parameter. Named queries can't do that.)

3

well, the only return code I get by default is zero. Unless I specify the return code like RETURN 4, I get 0. But this I do in the SSMS.

Is there a "best practice" for getting a confirmation of an update/insert? Would that be the scripting you mentioned?

4

That's SQL scripting. Don't do that.

Just use a plain SQL INSERT or UPDATE statement (no DECLAREs or BEGINs or other SQL script syntax) and you will automatically get the number of affected rows as the return value.