Dynamc SQL & Return Value

Question

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

DanG SSC Eights! Points: 951 More actions · Answer 2

SSC Eights!

Points: 951

December 21, 2004 at 7:07 am

Thanks Frank, that did the trick for me!

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Another way to skin same cat is to create a UDF that will return the value you are seeking...

Good Hunting!