June 7, 2007 at 1:21 pm
Hello
I want to pass a field to a stored procedure, so the update statement will change according to what is passed.
I want the update statement in the stored procedure to work for many fields.
-- This works....
CREATE PROCEDURE dbo.sp_test1
@MCI as varchar(12)
@Value as varchar (10)
AS
update customer set USR_FIELD_1 = @Value where
master_customer_id=@MCI
GO
exec sp_test1 @MCI='000000100100', @Value='Yes'
-- I want to replace USR_FIELD_1 with a parameter (like USR_FIELD_2, USR_FIELD_3, etc) CREATE PROCEDURE dbo.sp_test1
@MCI as varchar(12),
@FIELDNAME as varchar(50),
@Value as varchar (10)
AS
update customer set @FIELDNAME = @Value where
master_customer_id=@MCI
GO
exec sp_test1 @MCI='000000100100', @FIELDNAME='USR_FIELD_1', @Value='No'
In Query Analyzer it returns "(1 row(s) affected)" - but the data does not change.
What am I doing/understanding wrong?
Thanks
Dave
June 7, 2007 at 2:27 pm
If you need to use the parameter to specify columns for DML statements, you have to build the entire statement on the fly and execute it using special execution syntax. Look up 'dynamic sql' in books online for a complete explanation. There are a couple of different techniques to use, depending on what you want to achieve.
June 11, 2007 at 4:55 pm
This is one:
CREATE PROCEDURE dbo.sp_test1
@MCI as varchar(12),
@Value as varchar (10),
@FIELDNAME as varchar(50)
AS
exec ('update customer set '+@FIELDNAME +' = '''+@Value +''' where master_customer_id='''+@MCI +'''')
GO
exec sp_test1 @MCI='000000100100', @Value='Yes', @FIELDNAME ='USER_FIELD_1'
June 12, 2007 at 8:12 am
I was able to get the sp to work that way. Thank you very much. I appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply