Environment sql server 2005 sp3
I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error before @foo. I do not see it can someone help me find it please.
CREATE PROCEDURE testme
@test AS INT
AS
BEGIN
SELECT @TEST
END
DECLARE @foo AS CHAR(6)
set @foo = '11test'
EXEC testMe @test = CAST(Substring(@foo,1,2) as int)
first off all, you can't cast '11test' as an int
second, if the value can be converted to an int, you don't need to cast, an implicit cast will happen
DECLARE @foo AS CHAR(6)
set @foo = '2'
EXEC testMe @test =@foo
If you want to test if it can be converted to an int, grab the IsInt function from here: IsNumeric, IsInt, IsNumber and use that to test before making the proc call
EDIT
here is how you can do it
DECLARE @foo AS CHAR(6)
set @foo = '11test'
SET @foo = CAST(Substring(@foo,1,2) as int)
EXEC testMe @test = @foo
you can't pass functions to procs, this is why GETDATE() doesn't work either, either use an intermediate variable or cast back to the same variable
You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure.You cannot convert a char field to int when it contains non-numeric characters.
I would suggest creating a function that loops through the chars and removes any that are non-numeric.
