Stored procedure trouble with sp_executesql

Question

Stored procedure trouble with sp_executesql

skeltoncp

SSC Enthusiast

Points: 114
More actions
September 26, 2007 at 9:50 am

#175161

I've been banging my head against the wall trying to get my sproc to work for a while now. Maybe someone can help me out?
The basic problem is that my procedure seems to work fine, unless I call it using exec sp_executesql. The other problem is I'm not an expert with DBs, I primarily develop applications. So for example, when I run this query, it works:
USE [DBName]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_UpdateProcessedTrades]
@vcDeptName = N'IF-PM',
@buySellCoverShortFlag = N'S',
@customerAccountCounterparty = N'SCOTFI',
@shadowAccount = N'VTB1',
@tradeID = 64809
SELECT'Return Value' = @return_value
GO
but when I try to run it this way (which is actually being done from an application, of course) it doesn't work:
exec sp_executesql N'sp_UpdateProcessedTrades',
N'@vcDeptName varchar(5),
@buySellCoverShortFlag varchar(1),
@customerAccountCounterparty varchar(6),
@shadowAccount varchar(4),
@tradeID bigint',
@vcDeptName = 'IF-PM',
@buySellCoverShortFlag = 'S',
@customerAccountCounterparty = 'SCOTFI',
@shadowAccount = 'VTB1',
@tradeID = 64809
the error I get is: Line 1: Incorrect syntax near 'sp_UpdateProcessedTrades'.
here's the create script I use for the SP:
USE [DBName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateProcessedTrades]
@vcDeptName nvarchar(50),
@buySellCoverShortFlag nvarchar(1),
@customerAccountCounterparty nvarchar(10),
@shadowAccount nvarchar(4),
@tradeID bigint
AS
DECLARE @id BIGINT
SET @id = (SELECT ctn.tsnID FROM dbo.CMFiTransNum ctn WHERE ctn.departmentName = @vcDeptName)
Update CMFiTransNum set tsnCounter = tsnCounter + 1
where tsnID = @ID and
(select processedFlag from processedTrades where tradeID = @tradeID) = 0
Update processedTrades
set processedFlag= 1,
buySellCoverShortFlag= @buySellCoverShortFlag,
customerAccountCounterparty = @customerAccountCounterparty,
shadowAccount= @shadowAccount
where (select processedFlag from processedTrades where tradeID = @tradeID) = 0
and tradeID = @tradeID
I suspect the problem is likely either just a typo somewhere I haven't been able to find yet, or possibly I'm doing something illegal with sp_executesql. Any help would be greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

isn't this the problem?: @tradeid ahs a single quote, which would stop theprocess, and then all the other varables need doublesingle quotes, like @vcDeptName = ''IF-PM'',

exec sp_executesql N'sp_UpdateProcessedTrades',

N'@vcDeptName varchar(5),

@buySellCoverShortFlag varchar(1),

@customerAccountCounterparty varchar(6),

@shadowAccount varchar(4),

@tradeID bigint',

@vcDeptName = 'IF-PM',

@buySellCoverShortFlag = 'S',

@customerAccountCounterparty = 'SCOTFI',

@shadowAccount = 'VTB1',

@tradeID = 64809

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sam bryant Right there with Babe Points: 792 More actions · Answer 2

I think this should work

exec sp_executesql N'exec sp_UpdateProcessedTrades @vcDeptName, @buySellCoverShortFlag, @customerAccountCounterparty, @shadowAccount, @tradeID',

N'@vcDeptName varchar(5),

@buySellCoverShortFlag varchar(1),

@customerAccountCounterparty varchar(6),

@shadowAccount varchar(4),

@tradeID bigint',

@vcDeptName = 'IF-PM',

@buySellCoverShortFlag = 'S',

@customerAccountCounterparty = 'SCOTFI',

@shadowAccount = 'VTB1',

@tradeID = 64809

skeltoncp SSC Enthusiast Points: 114 More actions · Answer 3

I'm not sure I see what you mean. Are you saying I should be doing this?:

exec sp_executesql N'p_UpdateProcessedTrades',

N'@vcDeptName varchar(5),

@buySellCoverShortFlag varchar(1),

@customerAccountCounterparty varchar(6),

@shadowAccount varchar(4),

@tradeID bigint',

@vcDeptName = ''IF-PM'',

@buySellCoverShortFlag = ''S'',

@customerAccountCounterparty = ''SCOTFI'',

@shadowAccount = ''VTB1'',

@tradeID = 64809

That doesn't work, gives a syntax error near ''. That doesn't surprise me either, isn't the syntax you're supposed to use for this the following?

sp_executesql [@stmt =] stmt

[

{, [@params =] N'@parameter_name data_type [,...n]' }

{, [@param1 =] 'value1' [,...n] }

]

skeltoncp SSC Enthusiast Points: 114 More actions · Answer 4

sam (9/26/2007)
I think this should work
exec sp_executesql N'exec sp_UpdateProcessedTrades @vcDeptName, @buySellCoverShortFlag, @customerAccountCounterparty, @shadowAccount, @tradeID',
N'@vcDeptName varchar(5),
@buySellCoverShortFlag varchar(1),
@customerAccountCounterparty varchar(6),
@shadowAccount varchar(4),
@tradeID bigint',
@vcDeptName = 'IF-PM',
@buySellCoverShortFlag = 'S',
@customerAccountCounterparty = 'SCOTFI',
@shadowAccount = 'VTB1',
@tradeID = 64809

That worked! Now I just have to figure out how to make the code I have generating this call do that. I'm puzzled how I managed to produce a broken query by using SQLCommand.ExecuteNonQuery()

skeltoncp SSC Enthusiast Points: 114 More actions · Answer 5

I've finally got it figured out. Some code I inherited wasn't defining the commandtype properly. I never would have figured this out without the hints I got here, thanks to all who helped out.