Hopefully a quick/easy question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hopefully a quick/easy question

Author

Topic

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 14:57:56

I am trying to update a table's record, based on a value that comes from another table.

For some reason I am having difficulty. Here is what I have. Hopefully someone can point me in the rigth direction.

CREATE PROCEDURE [dbo].[upUpdateApplicationActionforReferralNotesRole]
@ApplicationAreaIDY INT
AS

SET NOCOUNT ON
SELECT
@ApplicationAreaIDY = a.ApplicationAreaIDY
FROM
[ApplicationArea] a
WHERE
a.internalName = 'ReferralNote'

UPDATE
dbo.ApplicationAction
SET
ApplicationAction.[Name] = 'Add'
WHERE
ApplicationAction.ApplicationAreaIDY = @ApplicationAreaIDY AND ApplicationAction.[Name] = 'ModifyNotes';

UPDATE
dbo.[ApplicationAction]
SET
ApplicationAction.[Name] = 'View'
WHERE
ApplicationAction.[Name] = 'ViewNotes' AND ApplicationAction.ApplicationAreaIDY = @ApplicationAreaIDY

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-05 : 15:03:27

What's the problem? Is it faiing? Not getting the correct data? Something else?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 15:04:52

Its not actually updating the second table (ApplicationAction).

When I run the query, it tells me that it ran fine (no errors), but the table is not actually being updated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-05 : 15:07:56

quote:
Originally posted by ill05nino

Its not actually updating the second table (ApplicationAction).

When I run the query, it tells me that it ran fine (no errors), but the table is not actually being updated.

Did you execute the stored proc, or just executed the CREATE script that you have provided. The script just creates the stored proc for you. You will have to execute it for the actual update to happen.

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 15:09:24

Yeah, I've executed the entire procedure. That's the weird part. I only get this back though, 'Command(s) completed successfully.'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-05 : 15:46:29

quote:
Originally posted by ill05nino

Yeah, I've executed the entire procedure. That's the weird part. I only get this back though, 'Command(s) completed successfully.'

Try to replace the UPDATE statements with SELECT, and make sure you get data returned from dbo.ApplicationAction for the value in @ApplicationAreaIDY.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-05 : 15:53:16

Does the first part (query) get expected results? Does this return one or more rows:

SELECT
    a.ApplicationAreaIDY 
FROM    
    [ApplicationArea] a
WHERE
    a.internalName = 'ReferralNote'

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 15:56:11

Okay, even after doing that, I don't actually get any 'results'. I am ONLY getting 'Command(s) completed sucessfully'. I haven't touched SQL in about 2 years, so I have no clue what I am doing wrong here.

This is exactly what I have currently (including the latest change from vijayisonly).


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[upUpdateApplicationActionforReferralNotesRole]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[upUpdateApplicationActionforReferralNotesRole]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[upUpdateApplicationActionforReferralNotesRole]
	@ApplicationAreaIDY INT
AS


	SELECT
		@ApplicationAreaIDY = a.ApplicationAreaIDY 
	FROM
		[ApplicationArea] a
	WHERE
		a.internalName = 'ReferralNote'

	SELECT *
	FROM ApplicationAction
	WHERE ApplicationAction.ApplicationAreaIDY = @ApplicationAreaIDY AND ApplicationAction.[Name] = 'ModifyNotes'

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 15:57:39

Lamprey -- All I am getting is a 'Command(s) completed successfully'. Nothing is returning at all.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-05 : 15:59:21

Please run Lamprey's query in a separate query window and not as part of the stored proc.

Also, can you tell us how you are executing the stored procedure.

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 16:02:11

Lamprey -- When I run YOUR query in a separate window, I get back the expected result. It returned just ONE row, as it should.

Sorry, I ran it first within the query (by just highlighting it), and got nothing.

ill05nino
Starting Member

11 Posts

Posted - 2011-12-05 : 16:02:55

Vijay -- I am just running the query within SQL Server Management Studio. I am running it against one of several databases that I have.

consultor.dwh
Starting Member

6 Posts

Posted - 2011-12-05 : 16:21:32

Try adding the output clause to your sentences, so we can see what it's doing your sentence. The code will look like:

UPDATE
dbo.[ApplicationAction]
SET
ApplicationAction.[Name] = 'View'
OUTPUT INSERTED.*
WHERE
ApplicationAction.[Name] = 'ViewNotes' AND ApplicationAction.ApplicationAreaIDY = @ApplicationAreaIDY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources