-
Parameter returned in a execute
Hello,
How can I pass the value of the count(*) to outside the execute. I need the value to continue with the sp
set @sql= 'declare @res int select @res=count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
???????? set @result=exec (@sql) ????????????? Something like this
Thanks
Paulo
-
1. capture result into a table
create table #res (res int)
set @sql= 'select count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
insert into #res exec(@sql)
2. or through cursor into variable
set @sql= 'declare cr insensitive cursor for select count(*) from t
where tam=(select '+@posxx+' from sffpoxx0 where ponbr='+@Col001+' and poodn='+@Col002+' and pocat='+@Col007+')'
exec(@sql)
open cr
fetch next from cr into @res
deallocate cr
You Have To Be Happy With What You Have To Be Happy With (KC)
-
Thank you very much YuckFou,
The first one runs okay
I tried also the second option but it doesn't run so well, because I need to declare also the cursor outside the execute (in the sp).
No matter, the first one runs okay.
Now tell me just one think, this is the only way that I can get the data from a execute function ?
Thanks for your time
Paulo
-
2. you don't need to declare cursor outside...
declare @sql varchar(100), @res int
set @sql= 'declare cr insensitive cursor for select -1'
exec(@sql)
open cr
fetch next from cr into @res
deallocate cr
select @res
--returns -1
3. another way is through procedure...
declare @sql varchar(100), @res int
set @sql= 'create procedure dbo.#getres as return -1'
exec(@sql)
exec @res = dbo.#getres
drop procedure dbo.#getres
select @res
--returns -1
You Have To Be Happy With What You Have To Be Happy With (KC)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|