Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

@test AS INT

    DECLARE @foo AS CHAR(6)
set @foo = '11test'
EXEC testMe @test = CAST(Substring(@foo,1,2) as int)
share|improve this question
up vote 9 down vote accepted

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 ] }

share|improve this answer

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
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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