Help with Update Stored Procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Update Stored Procedure

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2009-01-09 : 07:19:33

Im having problems getting this Stored procedure to work correctly. This procedure "falls over" since adding the additional parameter so im guessing the parameter is where i have gone wrong. Coud anyone assist?

ALTER PROCEDURE [dbo].[sp_Some_StoredProcedure] @ValueFromUser
AS

UPDATE
		TableName
SET
		IsManager='Y',
		IsManagerFrom=GetDate()
		ValueFromUser=@ValueFromUser
WHERE
		SomeColumn = (SELECT DISTINCT 
				MIN(SomeColumn) AS NewSomeColumn
				FROM         
				TableName
				WHERE
				IsManager = 'N')

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-09 : 07:21:25

@ValueFromUser has no type

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 07:27:24

quote:
Originally posted by jamie_pattison

Im having problems getting this Stored procedure to work correctly. This procedure "falls over" since adding the additional parameter so im guessing the parameter is where i have gone wrong. Coud anyone assist?

ALTER PROCEDURE [dbo].[sp_Some_StoredProcedure]
( @ValueFromUser datatype)
AS

UPDATE
TableName
SET
IsManager='Y',
IsManagerFrom=GetDate(),
ValueFromUser=@ValueFromUser
WHERE
SomeColumn = (SELECT DISTINCT
MIN(SomeColumn) AS NewSomeColumn
FROM
TableName
WHERE
IsManager = 'N')

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 08:34:36

no need of DISTINCT in last subquery as MIN() returns only a single value from table. so DISTINCT doesnt make sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources