New Guy Fighting with Update SP

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

New Guy Fighting with Update SP

Author

Topic

PerryP38
Starting Member

4 Posts

Posted - 2008-01-18 : 06:01:10

I am new to writing stored procedure and am having a problem with what should be a simple update stored procedure.

Here is my situation, using SQL 2005 I have a table with the following columns
AssetID int (Idenity Seed 1,1)
Description varchar(50)
blah
blah
blah

I want to update the description field for a particular record selected by the AssetID I tried this

@PDesc as varchar(50),
@PNewValue as varchar(50),
@PColumnName as varchar(50),
@PColumnValue as varchar(50)

AS

Update tblAssets Set @PDesc = @PNewValue
Where @PColumnName = @PColumnValue

When I run it with the following command:
CM_UpdateAssets 'description', 'Changed By SP', 'AssetID', 2

Msg 245, Level 16, State 1, Procedure CM_UpdateAssets, Line 10
Conversion failed when converting the varchar value 'AssetID' to data type int.

Any idea what I have done wrong here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 06:05:41

quote:
Originally posted by PerryP38

Any idea what I have done wrong here?

Dynamic SQL.

E 12°55'05.25"
N 56°04'39.16"

cas_o
Posting Yak Master

154 Posts

Posted - 2008-01-18 : 07:44:25

[code]
CREATE PROCEDURE usp_UpdateAssetDescription

@AssetID int,
@OldDescription varchar(50)
@NewDescription varchar(50)

AS

UPDATE tblAssets
SET Description = @NewDescription
WHERE AssetID = @AssetID AND Description = @OldDescription

EXEC usp_UpdateAssetDescription 2, 'Changed By SP', 'Original description'
[/code]

I included the @OldDescription for concurrency in case the scenario you're doing this in is a multiuser one.

;-]... Quack Waddle

PerryP38
Starting Member

4 Posts

Posted - 2008-01-18 : 11:56:11

Thanks for your help I changed the name of the database field from Description to Asset_Desc and made the corresponding changes in your code and it is now working I appreciarte the help.

PerryP38
Starting Member

4 Posts

Posted - 2008-01-19 : 06:47:13

I have another question about this procedure. It seems I should be able to provide a parameter to select the column that I want to update, but when I edit the code to this:
----------------------------------------------------
CREATE PROCEDURE usp_UpdateAssetDescription_WithColumnName

@AssetID int,
@ColumnToUpdate, varchar(50),
@OldDescription varchar(50),
@NewDescription varchar(50)

AS

UPDATE tblAssets
SET @ColumnToUpdate = @NewDescription
WHERE AssetID = @AssetID AND @ColumnToUpdate = @OldDescription
-----------------------------------------------------

I get the following error message:
-------------------------------------------------------
Msg 102, Level 15, State 1, Procedure usp_UpdateAssetDescription_WithTable, Line 4
Incorrect syntax near ','.
Msg 137, Level 15, State 1, Procedure usp_UpdateAssetDescription_WithTable, Line 11
Must declare the scalar variable "@ColumnToUpdate".
--------------------------------------------------------

Any Suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-19 : 07:38:10

quote:
Originally posted by PerryP38

I have another question about this procedure. It seems I should be able to provide a parameter to select the column that I want to update, but when I edit the code to this:
----------------------------------------------------
CREATE PROCEDURE usp_UpdateAssetDescription_WithColumnName

@AssetID int,
@ColumnToUpdate, varchar(50),
@OldDescription varchar(50),
@NewDescription varchar(50)

AS

UPDATE tblAssets
SET @ColumnToUpdate = @NewDescription
WHERE AssetID = @AssetID AND @ColumnToUpdate = @OldDescription
-----------------------------------------------------

I get the following error message:
-------------------------------------------------------
Msg 102, Level 15, State 1, Procedure usp_UpdateAssetDescription_WithTable, Line 4
Incorrect syntax near ','.
Msg 137, Level 15, State 1, Procedure usp_UpdateAssetDescription_WithTable, Line 11
Must declare the scalar variable "@ColumnToUpdate".
--------------------------------------------------------

Any Suggestions?

You cant pass column name to be updated like this. You need to write D-SQL code. DO you really want it to be done this way? This is not best of approaches to follow. Anyways if you really need it change code like this:-

CREATE PROCEDURE usp_UpdateAssetDescription_WithColumnName 

@AssetID int,
@ColumnToUpdate, varchar(50), 
@OldDescription varchar(50),
@NewDescription varchar(50)

AS

DECLARE @Sql varchar(8000)
SET @Sql='UPDATE tblAssets
SET ' + @ColumnToUpdate + ' = ' + @NewDescription +
' WHERE AssetID = ' + @AssetID +' AND '+ @ColumnToUpdate + ' = ' + @OldDescription

EXEC (@Sql)
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-19 : 07:40:30

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

PerryP38
Starting Member

4 Posts

Posted - 2008-01-19 : 17:20:54

Thanks, I appreciate the help. I am new to the stored procedure world. I am upgrading to SQL 2005 and am trying to learn. If this is not the best way to go can someone tell me the correct way?

I plan to create a web application and need the ability to edit records. Is it better to write a stored procedure to handle each column that may be edited?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources