October 13, 2003 at 4:28 am
I have Dynamic Sql like this:
declare @CountryName varchar(20),
@sql varchar(1000)
set @sql = 'SELECT '+ @CountryName = + 'nation_e_name FROM T_NationCode
WHERE nation_code = 82'
exec (@Sql)
print 'is: ' + @@CountryName
The above print statement results space value for @CountryName, it means
nothing selected into variable @CountryName.
How can i get country name populated into variable @CountryName using
Dynamic SQL?
Pl. let me know
October 13, 2003 at 5:01 am
Hi,
maybe this one will help you
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=14030
If you need to use dynamic SQL you should really use sp_executeSQL. This will give you a chance of reusable execution plans.
Btw, in addition there is also a typo
print 'is: ' +@@CountryName should be read
print 'is: ' +@CountryName
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 14, 2003 at 6:05 am
HI,
Please find the example below, hope this helps.
Regards,
Affan
declare @RECORDCNT int
declare @TABLE char(100)
declare @cmd Nvarchar(100)
-- Set the table to be used
set @TABLE = '[ORDERS]'
-- Build the dynamic T-SQL command
SET @CMD = 'select @RECORDCNT=count(*) from ' + @TABLE
-- Call the sp_executesql SP to return the record count for @TABLE variable
exec sp_executesql @CMD,N'@RECORDCNT int out, @TABLE char(100)',@RECORDCNT out , @TABLE
-- Display the number of records
print @RECORDCNT
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply