Link to home
Start Free TrialLog in
Avatar of KANEDA 0149
KANEDA 0149Flag for United States of America

asked on

SQLCMD Execute Multiple Stored Procedure with Parameters Dependent On Another

I am trying to use SQLCMD via command line to call 2 stored procedures which one is dependent on another.  Both stored procedures have parameters.  I can run 1 stored procedure with parameters, no problem.  

It's trying to combine the 2 together because stored procedure User.pSessionCreate executes a unique SessionGuid ID needed to run the 2nd stored procedure Custom.pTotport_AssetClass as one of the parameters.  In addition, the data results is output to a .txt file.  Thanks in advance for the help!

Here is an example of the SQLCMD I put together but I get the error "Cannot use the OUTPUT option when passing a constant to a stored procedure" in my output .txt file.  You'll see below the @SessionGuid created from the 1st stored procedure is needed in the 2nd stored procedure parameter.
sqlcmd -S serverName" -d dbName -E -h -1 -Q "set nocount on;declare @SessionGuid nvarchar(48) exec User.pSessionCreate 'login', 'password', '@SessionGuid';exec Custom.pTotport_AssetClass '@SessionGuid', 'Group', '2015-3-31', 'PC', 'IN'" -o C:\temp\test.txt

Open in new window

Here is a actual syntax when running out of SSMS directly and works just fine.  Just trying to accomplish this via SQLCMD for another process.  

declare @SessionGuid nvarchar(48)
exec User.pSessionCreate @loginname = 'login', @password='password',@SessionGuid = @SessionGuid out

exec Custom.pTotport_AssetClass
	@SessionGuid = @SessionGuid,
	@Portfolios = 'Group',
	@ThruDate = '2015-3-31',
	@ReportingCode = 'PC',
	@FileType = 'AA'

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of KANEDA 0149

ASKER

Thank you so much ste5an that did it, why didn't I think of that...LOL