Solved

MSSQL: run stored proc with variable from select or list

Posted on 2014-04-11
3
367 Views
Last Modified: 2014-04-11
I would like to create one statement if possible. To process the following:
EXEC addAccount @accountNo = '123456'
EXEC addAccount @accountNo = 'ABCDEF'
EXEC addAccount @accountNo = '123ABC'
EXEC addAccount @accountNo = 'ABC123'


Like this:
EXEC addAccount @accountNo = ('123456', 'ABCDEF', '123ABC', 'ABC'123')
or
EXEC addAccount @accountNo = (SELECT accountNo FROM accountList WHERE new = 1)

I cannot modify the stored procedure.

Is this even possible?
0
Comment
Question by:rlarian
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39994577
Not with doing 1 EXEC. Since you can't alter the stored procedure's parameters, you can't send it more than 1 at a time.

As far as automation or making it dynamic, you can do something like:

DECLARE @AcctNbr VARCHAR(30)   --set the datatype and length accordingly
DECLARE @SqlString NVARCHAR(Max)
DECLARE @ParamDef NVARCHAR(Max)

declare AcctList CURSOR FOR
SELECT accountNo FROM accountList WHERE new = 1

Open AcctList

 FETCH NEXT FROM AcctList INTO @AcctNbr
     WHILE @@FETCHSTATUS = 0
         BEGIN
                  Select @SqlString = N'EXEC addAccount @accountNo'
                            ,@ParamDef = N'@accountNo VARCHAR(30)'         --set the datatype and length accordingly
               
            EXECUTE sp_executeSQL @StrSql, @ParamDef, @accountNo = @AcctNbr


              FETCH NEXT FROM AcctList INTO @AcctNbr
         END

CLOSE AcctList
DEALLOCATE AcctList

Open in new window

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39994588
You could create a table type and pass that as a variable.

CREATE TYPE dbo.IntList AS TABLE
(
   Value INT NOT NULL PRIMARY KEY CLUSTERED
);

Open in new window


Procedure:

CREATE PROCEDURE addAccount
(
   @AccountNoList IntList READONLY;
)
AS
...

Open in new window


Using Procedure:

DECLARE @AccountNoList IntList;

INSERT INTO @AccountNoList
SELECT accountNo FROM accountList WHERE new = 1

EXEC addAccount @AccountNoList = @AccountNoList

Open in new window

0
 
LVL 4

Author Closing Comment

by:rlarian
ID: 39994872
thanks!
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 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