How do I run a SQL Stored Procedure from my DS script?

Products

• Deployment Solution

Issue/Introduction

 

Resolution

Question
 

I have a problem with using Custom Data Sources in Deployment Solution. Currently I have setup an SQL Custom Data Source called: CPCDB.

When I run the following script code from within a DS script, only the SELECT statement below returns a result.

strTestSelect = "%#CPCDB*"SELECT (SUBSTRING(Code,1,4) + UPPER(SUBSTRING(Region,1,6)) + 'SC' + '001') FROM tblCPCDeploySiteInfo WHERE Subnet = '192.168.33.0'"%"

StrTestSP = "%#CPCDB*"EXEC spgqsSubnet4"%"

MsgBox(strTestSelect)
MsgBox(strTestSP)

From Query Analyzer, both of these SQL statements return the same identical result. I can confirm that the Stored Procedure is allowed (as set within the Custom Data Sources - Allowed Stored Procedures list).

The Stored Procedure that I am running is identical to the SQL statement above. I've inlcuded it below for reference:

CREATE PROCEDURE spgqsSubnet4
AS
SELECT (SUBSTRING(Code,1,4) + UPPER(SUBSTRING(Region,1,6)) + 'SC' + '001')
FROM tblCPCDeploySiteInfo WHERE Subnet = '192.168.33.0'
GO

I can't find any example of a Stored Procedure being called from within DS using Custom Tokens and Custom Data Sources. How do I this? 

Answer
 

Use CALL instead of EXEC in your script, as with the following:

StrTestSP = "%#CPCDB*"{CALL spgqsSubnet4}"%"

If your stored procedure requires parameters, these should be passed as a comma-separated list in parentheses after the name of the stored procedure.

For instance, if your stored procedure requires two input parameters - an IP Address as a string and an integer - the syntax for the command is as follows:  

StrTestSP = "%#CPCDB*"{CALL spgqsSubnet4 ('192.168.0.10',4)}"%"