?
Solved

Get return value from SQL stored Procedure with Javascript

Posted on 2010-08-25
2
Medium Priority
?
718 Views
Last Modified: 2012-06-22
Hello experts.

We have a stored procedure written in SQL (MS SQL Server 2005 ):

ALTER PROCEDURE [dbo].[test_insert]

@person_nbr varchar(12) output

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      SELECT @person_nbr = max(last_generated) + 1
      from system_counters
      where counter_type = 'person_nbr'

END


// and the javascript that reads in the SP:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://localhost:1433;databaseName=Test;','un','pw');
var result = 0;
var sql = "exec dbo.test_insert()"
logger.warn("Running query: " + sql); //changeme to info or debug once its all working
result = dbConn.executeUpdate(sql);

globalMap.put('PER_NBR', result)
dbConn.close();

return message;

----------------------

Utlimate goal is to get the value of the person_nbr variable in the SQL Stored Proc to return and store into a javascript variable.  Any thoughts as to where to code goes wrong?    We get an "unknown syntax" error.

Thanks in advance!
0
Comment
Question by:robthomas09
2 Comments
 
LVL 19

Accepted Solution

by:
elimesika earned 2000 total points
ID: 33526302
HI

Change
var sql = "exec dbo.test_insert()"
to
var sql = "exec dbo.test_insert"

0
 

Author Closing Comment

by:robthomas09
ID: 33531253
thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question