August 13, 2009 at 12:35 am
I have a small question.
Procedure:
CREATE PROCEDURE [Procedure1] @Param1 AS varchar(8000), @Param2 as datetime AS
set nocount on
declare @strSQL VARCHAR(1000)
BEGIN distributed TRANSACTION
SET @strSQL = 'update table1 set valid=1 where valid =0 and SomeField in (1,4) and TableDate = '''+ CAST(@Param2 as VARCHAR) + ''' and gest_id in (' + @Param1 + ')'
Print @strSQL
--exec sp_sqlexec @strSQL
IF @@ERROR<>0
begin
GOTO ERROR
end
COMMIT TRANSACTION
RETURN
ERROR:
ROLLBACK TRANSACTION
GO
When execute this like this:
DECLARE @Param2 AS DATETIME
SET @Param2 = '2009-09-08'
EXEC Procedure1 '''1'',''2'',''3'',''4'',''5'',''10''' , @Param2
my string look like this
update nir set validat= 1 where validat=0 and tip_nir in (1,4) and data_nir= 'Sep 8 2009 12:00AM' and gest_id in ('1','2','3','4','5','10')
and i can execute it
If i execute like this :
DECLARE @Param1 AS VARCHAR
DECLARE @Param2 AS DATETIME
SET @Param1 = '''1'',''2'''
SET @Param2 = '2009-09-08'
EXEC VALIDARE_INTRARIMEDICAMENTE1 @Param1 , @Param2
my string is like this:
update nir set validat= 1 where validat=0 and tip_nir in (1,4) and data_nir= 'Sep 8 2009 12:00AM' and gest_id in (')
and you can see that i have no gest_id 🙁
I am sure that i am missing something 😛 but i dont know what.
Thank you.
August 13, 2009 at 8:15 am
dragosbucur2000,
You need to specify a length for your VARCHAR declaration. When you declare a variable as VARCHAR without a length specified, it defaults to VARCHAR(1). Thus, you are only seeing the first apostrophe in your string.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply