Using a 'bit' vs 'int' in a Stored Procedure

[JeffCarlin]

I am trying to construct an SP that will allow me to pass a fieldname (always a boolean/bit field) as well as a value for that field. Here's what I've got:

CREATE PROCEDURE write_calendar_value_boo

/* @Parm1 not used */
@Parm2 varchar(50) ,    -- The Field Name
@Parm3 bit ,		-- The Field Value
@Parm4 varchar(11) ,   	-- The Patient's SSN
@Parm5 varchar(2) ,     -- The Treatment Num
@Parm6 datetime	        -- The Treatment Date

AS 

DECLARE @str varchar(255) 

SET @str = 'UPDATE tblDailyTreatment SET ' +  @Parm2 +  ' = ' + @Parm3
+ ' WHERE strTreatPatSSN = ' + @Parm4 
+ ' AND strTreatPatTreatNum = ' + @Parm5 
+ ' AND dteDateLogged = ' + @Parm6
+ ' AND LEFT(strAMPM,1) <> &quot;P&quot; '

EXEC (@str)

Problem is, it will not take @Parm3 as a "bit". It will take it as an "int", but then the SP will not execute.

Can anyone figure this out?
Thanks.

 

[JohnDTampaBay]

Try using CAST in the dynamic sql:

SET @str = 'UPDATE tblDailyTreatment SET ' + @Parm2 + ' = ' + CAST(@Parm3 AS char(1))
+ ' WHERE strTreatPatSSN = ' + @Parm4
+ ' AND strTreatPatTreatNum = ' + @Parm5
+ ' AND dteDateLogged = ' + @Parm6
+ ' AND LEFT(strAMPM,1) <> &quot;P&quot; '


--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[JeffCarlin]

Thanks, Angel...works like a charm.