Dynamic SQL and variables
-
Hi Folks
How do I put the following into a variable?
select @sql = 'Select tgt_module_' + right(@Module,1) + ' as Result from tbl_aud_dates where Month_Date = ' + char(39) + 'January-2005' + char(39)
When executing this, I get the column name and value, but how do I put it into a variable to use elsewhere?
Thanks in advance, Andrew
-
Not sure if I understand correctly, but see if this helps:
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount int
DECLARE @table nvarchar(255)
SET @table = 'authors'
SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
EXEC sp_executesql @stmt, N' @count int output', @rowcount OUTPUT
IF @rowcount > 0
BEGIN
SELECT @rowcount AS Anzahl
END
RETURN
Anzahl
--------------------
23
(1 row(s) affected)
This is also interesting http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Check out this ... It May Work
Your Query
select @sql = 'Select tgt_module_' + right(@Module,1) + ' as Result from tbl_aud_dates where Month_Date = ' + char(39) + 'January-2005' + char(39)
To Store the returned Value in variable U have to Use Cursor...
Declare @ReturnValue -- Variable Declaration
Declare @Qry -- Variable for Qry Store
-- Use/Declare Cursor with the Query
SET QUOTED_IDENTIFIER OFF
GO
Set @Qry = "Declare CurSql Scroll Cursor For " +
"Select tgt_module_" + right @Module,1) + " as Result from tbl_aud_dates where Month_Date = " + char(39) + 'January-2005' + char(39) "
Exec(@Qry)
Fetch First From CurSql Into @ReturnValue
Close CurSql
Deallocate CurSql
Select @ReturnValue
SET QUOTED_IDENTIFIER ON
GO
-
Thanks for the help Folks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply