January 31, 2011 at 8:16 am
I've got a stored procedure as:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spA__InsertAccount]
(
@sAccountNrnvarchar(50)
,@sAddressnvarchar(50)= NULL
,@sCitynvarchar(50)= NULL
,@sNamenvarchar(50)= NULL
,@iNEWACCbigintOUTPUT
)
AS
BEGIN
SET NOCOUNT OFF
BEGIN TRY
BEGIN TRANSACTION
SET @iNEWACC= -1
BEGIN
INSERT INTO accounts
(
[AccountNr], [Address], [City], [Name]
) VALUES
(
@sAccountNr, @sAddress, @sCity, @sName
)
SET @iNEWACC = (SELECT @@IDENTITY)
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @iNEWACC = -1
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH
END
Trying a query as:
DECLARE @iNewint;
EXEC spA__InsertAccount
@sAccountNr= 'abc999',
@sName= 'New name',
@sAddress= 'Address',
@sCity= 'PARIS',
@iNEWACC= @iNew OUTPUT
SELECT @iNew AS [new ID]
works fine but
DECLARE @iNewint
DECLARE @sqlnvarchar(MAX), @params nvarchar(MAX)
SET @sql = '
DECLARE @iNew bigint;
EXEC spA__InsertAccount
@sAccountNr= ''abc999'',
@sName= ''New name'',
@sAddress= ''Address'',
@sCity= ''PARIS'',
@iNEWACC= @iNew OUTPUT
'
SET @params = N'@iNew bigint OUTPUT'
EXEC sp_executesql @s-2, @params, @iNew = @iNew
SELECT @iNew AS [new ID]
does NOT
The table 'accounts' is a simple design:
ID int (auto increment)
AccountNr nvarchar(50)
Name nvarchar(50)
Address nvarchar(50)
City nvarchar(50)
Any suggestions (pls)
TIA
January 31, 2011 at 8:44 am
Change the name of "@iNew", either in the dynamic SQL or in the calling procedure. You're using that one twice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2011 at 8:54 am
Thanks GSquared for answering so fast
But, yeah call me whatever, which exactly?
(been a long day)
January 31, 2011 at 9:03 am
Either one... won't matter.
January 31, 2011 at 9:47 am
yeah OK thanks again, however when I changed to:
DECLARE @iNew_USEint
DECLARE @sqlnvarchar(MAX), @params nvarchar(MAX)
SET @sql = '
DECLARE @iNew bigint;
EXEC spA__InsertAccount
@sAccountNr = ''abc999'',
@sName = ''New name'',
@sAddress= ''Address'',
@sCity = ''PARIS.'',
@iNEWACC= @iNew OUTPUT
'
SET @params = N'@iNewID_OUT bigint OUTPUT'
EXEC sp_executesql @sql, @params, @iNewID_OUT = @iNew_USE OUTPUT;
SELECT @iNew_USE AS [new ID]
@iNew_USE was NULL
??
excuse the slow moving cells
January 31, 2011 at 9:59 am
Slow moving here too... Why do you even need dynamic sql to run this procedure??
February 1, 2011 at 1:44 am
this is only part of the big picture
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply