Exec command needed?

[kristinac]

I have a table with 4 different status fields. I have a stored procedure that would work for updating each one, the only difference would be the name of the field that was to be updated. Being new at this, I tried:

CREATE PROCEDURE [dbo].[pPermit_UpdateForms]
@Field varchar(20),
@PermitId int,
@Status int
AS
UPDATE tEnvironmentalHealth
SET @Field = @Status
WHERE PermitId = @PermitId
GO

Someone explained this would not work, and suggested using the EXEC command. Something like this:

CREATE PROCEDURE [dbo].[pPermit_UpdateForms]
@Field varchar(20),
@PermitId int,
@Status int
AS
Exec
("UPDATE tEnvironmentalHealth
SET" + @Field + "= @Status WHERE
PermitId = @PermitId&quot
GO

But that didn't work either. Can someone point me in the right direction? I've never used EXEC before. Any help would be appreciated...Thanks!

 

[JayKusch]

oooow

http://www your

close ...

CREATE PROCEDURE [dbo].[pPermit_UpdateForms]
@Field varchar(20),
@PermitId int,
@Status int
@SQLCommand VarChar(200)
AS
SET @SQLCommand =
'UPDATE tEnvironmentalHealth
SET ' + @Field + ' = @Status WHERE
PermitId = @PermitId'

EXEC (@SQLCommand)

GO


Thanks

J. Kusch

 

[swampBoogie]

You should avoid dynamic SQL. It defies much of the purpose with stored procedures. (You might as well build the update statement in your client application.)

UPDATE tEnvironmentalHealth
   set C1 = case when @field = 'C1' then c1 else @status end,
       C2 = case when @field = 'C2' then c2 else @status end,
       C3 = case when @field = 'C3' then c3 else @status end,
       C4 = case when @field = 'C4' then c4 else @status end
 where PermitId = @permitId

or

  if @field = 'C1' 
  begin
     update t set C1 = @status where PermitId = @Permitid
  end
  else if @field = 'C2'
 ...

(The problem with your current code

Exec
('UPDATE tEnvironmentalHealth ' +
' SET ' +  @Field + ' = ' + cast(@Status as varchar(10)) +
' WHERE PermitId = ' + cast(@PermitId as varchar(10)) )

 

[JayKusch]

oops ... forgot a ,

CREATE PROCEDURE [dbo].[pPermit_UpdateForms]
@Field varchar(20),
@PermitId int,
@Status int,
@SQLCommand VarChar(200)
AS
SET @SQLCommand =
'UPDATE tEnvironmentalHealth
SET ' + @Field + ' = @Status WHERE
PermitId = @PermitId'

EXEC (@SQLCommand)

GO


Thanks

J. Kusch