Problem doing Update and Insert to different tables in same procedure.

We are trying to update and insert to two different tables using the code below. However the code never excutes the second insert statement. (see noted area) Does anybody have any ideas what we are doing wrong? Any help would greatly be appreciated.

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[AddPhoto]

@.AlbumID

int,

@.Caption

nvarchar(MAX)

AS

INSERT

INTO [Photos](

[AlbumID]

,

[Caption]

,

[Location]

,

[LastModified]

)

VALUES

(

@.AlbumID

,

@.Caption

,

'tmpLocation'

,/* tmpLocation needed because app broke when Location column set to Allow NULLs */

GetDate

())

/* Retrieve generated PhotoID */

DECLARE

@.PhotoIDint

SET

@.PhotoID=SCOPE_IDENTITY()

/* Build unique location path from album and photo ID */

DECLARE

@.Locationnvarchar(MAX)

SET

@.Location='\'+CONVERT(nvarchar(10), @.AlbumID)+'\'+CONVERT(nvarchar(10),@.PhotoID)+'.jpg'

/* Update photo with new location path */

UPDATE

[Photos]

SET

[Location]

= @.Location

WHERE

[PhotoID]

= @.PhotoID

/* Update photo with new location path */

******************************************The code never executes the statement below********************************************

INSERT

INTO [PhotoDefault](

[pidm]

,

[defaultPhoto]

,

[activityDate]

)

VALUES

(

'1234'

,

'test'

,

getdate

()

)

/* Return PhotoID and Location */

SELECT

@.PhotoID, @.Location

RETURN

Thanks,

Jason

Next time, when you post your code please use the Code editor available when you post. Your code is hard to read.

The code (After formatting) looks fine to me. Throw in a couple of PRINT statements before and after the INSERT. There is no reason the INSERT should be skipped.

|||

Sorry about the code post. I didn't know about the code editor.

I have posted print statements after the insert and they are never excuted. I have also tried posting the first insert after with no luck as well. Any other suggestion?

|||

I formatted your code for you. Try this new code if you see the messages:

set ANSI_NULLSON set QUOTED_IDENTIFIERON GOALTER PROCEDURE [dbo].[AddPhoto] @.AlbumIDint, @.Captionnvarchar(MAX)ASINSERT INTO [Photos] ( [AlbumID], [Caption], [Location], [LastModified])VALUES ( @.AlbumID, @.Caption,'tmpLocation' ,/* tmpLocation needed because app broke when Location column set to Allow NULLs */GetDate())/* Retrieve generated PhotoID */DECLARE @.PhotoIDint SET @.PhotoID = SCOPE_IDENTITY()/* Build unique location path from album and photo ID */DECLARE @.Locationnvarchar(MAX)SET @.Location ='\' +CONVERT(nvarchar(10), @.AlbumID) +'\' +CONVERT(nvarchar(10),@.PhotoID) +'.jpg'/* Update photo with new location path */UPDATE [Photos]SET [Location] = @.LocationWHERE [PhotoID] = @.PhotoID/* Update photo with new location path */******************************************The code never executes the statement below********************************************SELECT'I am here'INSERT INTO [PhotoDefault] ( [pidm], [defaultPhoto], [activityDate])VALUES ('1234','test',getdate() )SELECT'I am here again'/* Return PhotoID and Location */SELECT @.PhotoID, @.LocationRETURNGo