December 21, 2004 at 6:50 am
Hello,
I am trying to run a dynamic sql statement in a store procedure, but would like to capture the return result, but can't seem to fugure it out. Here is what I have so far:
DECLARE @SQL as varchar(100)
DECLARE @Result as int
Set @SQL = 'Select Max(Col1) as Year From Table1'
EXEC(@SQL)
How can I pickup the year from the select statement in my calling SP?
Thanks,
Dan
December 21, 2004 at 6:58 am
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount bigint
DECLARE @table nvarchar(255)
SET @table = 'authors'
SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT
IF @rowcount > 0
BEGIN
SELECT @rowcount AS Anzahl
END
RETURN
Oops, forgot to mention that this works with the PUBS sample database. Why do you use dynamic sql here in this case at all?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 21, 2004 at 7:07 am
Thanks Frank, that did the trick for me!
December 21, 2004 at 8:26 am
Another way to skin same cat is to create a UDF that will return the value you are seeking...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply