EXECUTE SQL string with parameter and return value

Hi all,

I would like execute an SQL string who calls a stored procedure with param and return a value:

declare @.query nvarchar(50)

set @.query = 'sp_test 1'

declare @.resultat int

exec @.resultat = @.query

select @.resultat

Its returns a error message:

"Could not find stored procedure 'sp_test 1'"

The command

exec(@.query)

works fine, but I can't retreive the return value and I can't do

exec @.resultat = (@.query)

How can I do?

Thanks,

Aurlien

use sp_executesql|||

Thanks very much,

With sp_executesql, the stored procedure is correctly executed, but it don't return the return value...

exec @.resultat = sp_executesql @.requete

@.resultat is still at 0 event if my stored procedure returns other :'(

How can I do?

Thanks very much

|||

check this example i use a cursor.. good luck

CREATE procedure test
as
begin
DECLARE @.AuthorID char(11)
declare @.sql nvarchar(4000)
set @.sql=' SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1'--'SELECT au_id FROM authors'

DECLARE @.c1 CURSOR

EXEC sp_executesql N'SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1', N'@.c1 cursor OUTPUT', @.c1 OUTPUT

FETCH NEXT FROM @.c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN

PRINT @.AuthorID

FETCH NEXT FROM @.c1
INTO @.AuthorID
END
CLOSE @.c1
DEALLOCATE @.c1
end

|||

Another exemple when using dynamic queries

declare @.query nvarchar(50)

set @.query = 'select ''' + 'sp_test 1' + ''''

CREATE TABLE #resultat
(resultat sql_variant)

INSERT INTO #resultat exec sp_executesql @.query

select resultat from #resultat