Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Exec sp_executesql
Message
 
 
À
23/05/2008 13:16:04
Issam Mansour
Jordan Business Machines
Amman, Jordanie
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2005
Divers
Thread ID:
01319249
Message ID:
01319265
Vues:
22
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--
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform