CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
gh9
gh9

Reputation: 10703

stored procedures and using tsql functions in parameters calls

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)

Upvotes: 2

Views: 590

Answers (2)

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 3

dunc
dunc

Reputation: 93

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.

Upvotes: 0

Related Questions