Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Exec sp_executesql
Message
 
 
To
23/05/2008 13:16:04
Issam Mansour
Jordan Business Machines
Amman, Jordan
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2005
Miscellaneous
Thread ID:
01319249
Message ID:
01319265
Views:
21
The sp_executesql does not allow expressions as its paremeters. You can get desired result w/o sp_executesql.
WHILE @x <= 3 BEGIN
	SET  @xnum =  RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
	IF @x =1 
		SET  @xnum1 =  @xnum
	ELSE IF @x = 2
		SET  @xnum2 =  @xnum
	ELSE
		SET  @xnum3 =  @xnum
	
	SET @x = @x + 1
        SET @position = @position - 3
END
>
>I am trying to move a verable to the sp_executesql but i have an error:-
>SELECT  @position = LEN(@inputNumber) - 2
>set @x=1
>WHILE @x<=3
>        begin
>                set @esql =N'set @xnum'+ convert(varchar(10),@x)+' = RIGHT(''000'' + SUBSTRING('+@inputNumber+','+convert(varchar(10),@position)+', 3), 3)'
>                exec sp_executesql @esql, N'''@xnum'+convert(varchar(10),@x)+'  char(3) INT output''','@xnum'+convert(varchar(10),@x)  output
>                set @x=@x+1
>                        SELECT @counter = @counter + 1
>                         , @position = @position - 3
>        END
>end
>
>the error message:
>
>Msg 102, Level 15, State 1, Procedure Currencyjbm, Line 153
>Incorrect syntax near '+'.
>
>the dynmic @esql ..
>
>set @xnum1 = RIGHT('000' + SUBSTRING(3211110,5, 3), 3)
>set @xnum2 = RIGHT('000' + SUBSTRING(3211110,2, 3), 3)
>set @xnum3 = RIGHT('000' + SUBSTRING(3211110,-1, 3), 3)
--sb--
Previous
Reply
Map
View

Click here to load this message in the networking platform