3

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. Thank you very much.

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)
10

SQL Rockstar's answer is how to fix it: I'll add an explanation.

A stored procedure parameter can be either a constant or variable: not an expression. CAST is an expression. (This is different to udf parameters that can accept expressions)

EXEC on MSDN states

... @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] }

10

Why not do the CAST when you SET @foo instead?

SET @foo = CAST(Substring('11test',1,2) as int)

You could even use a second variable and do this:

SET @foo1 = '11test'
SET @foo2 = CAST(Substring(@foo1,1,2) as int)

and then just call the proc:

EXEC testMe @test = @foo2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.