Extracting variable value from within Dynamic Sql

Question

Extracting variable value from within Dynamic Sql

blahknow

Right there with Babe

Points: 773
More actions
March 25, 2008 at 9:52 am

#187709

Hi everyone
My sp recieves name of table as parameter and is supposed to show its' number of rows.
Since the table name is a variable i do it wherby Dynamic Sql as follows
[Code]SET @myDynamic=
'
DECLARE @total_recs INT
SELECT @total_recs =count(*) from ' + @table_source
exec(@myDynamic)
print @total_recs
[/code]
The last row in the above script (print @total_recs) yields an error message saying that @total_recs was not declared.
Evidently it was declared but within the Dynamic sql. How can i use this variable from outside of the Dynamic Sql?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

I looked in this forum and saw this post and the previous post and noted that I answered both of these posts in the MSDN Transact SQL Forum. I guess this is not against any policy at the MSDN site since the posts are duplicated across completely different sites. Is that also true here at SqlServerCentral?

blahknow Right there with Babe Points: 773 More actions · Answer 2

kent waldrop (3/25/2008)
I looked in this forum and saw this post and the previous post and noted that I answered both of these posts in the MSDN Transact SQL Forum. I guess this is not against any policy at the MSDN site since the posts are duplicated across completely different sites. Is that also true here at SqlServerCentral?

Is it immoral ? Not ethical ?

As you testify yourself you are answering in both forums.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

You could just use the veiw defined in this article and do a SELECT on it WHERE NAME={your table name}, : http://www.sqlservercentral.com/scripts/tables/62545/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

rbarryyoung (3/26/2008)
You could just use the veiw defined in this article and do a SELECT on it WHERE NAME={your table name}, : http://www.sqlservercentral.com/scripts/tables/62545/

Am I in some sort of time warp? I could swear that the article you linked to is in the future. COOL 😎

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Bob Fazio (3/28/2008)
rbarryyoung (3/26/2008)
You could just use the veiw defined in this article and do a SELECT on it WHERE NAME={your table name}, : http://www.sqlservercentral.com/scripts/tables/62545/
Am I in some sort of time warp? I could swear that the article you linked to is in the future. COOL 😎

Actually most of the contributors here are really time-travelers from a distant dystopic future who have travelled back to your era with insane intentions of world domination through a form of subtle psychic slavery induced by the lexical patterns of the SQL answers that we give in this forum.

Either that or there is a 4-week backlog of articles that are pre-dated as soon as they are placed in the pipeline, I can't remember which.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

shamshudheen SSCrazy Points: 2390 More actions · Answer 6

hi blahknow

infact i am using sybase only now, i did read one article before to calculate row count of tables. which says

Declare @row_cnt INT

SELECT @row_cnt = rowcnt from sysindexes

WHERE id = (

SELECT id from sysobjects where name = @tbl_name

AND xtype = 'u'

)

WHERE name <> 'sysdiagrams'

PRINT @row_cnt

since i have no access to sql server , i am not tested above code.

i derived from seeing anothere article.

could you please test and let me know the result

bye

shamsudheen

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 7

blahknow (3/25/2008)
Hi everyone
My sp recieves name of table as parameter and is supposed to show its' number of rows.
Since the table name is a variable i do it wherby Dynamic Sql as follows
[Code]SET @myDynamic=
'
DECLARE @total_recs INT
SELECT @total_recs =count(*) from ' + @table_source
exec(@myDynamic)
print @total_recs
[/code]
The last row in the above script (print @total_recs) yields an error message saying that @total_recs was not declared.
Evidently it was declared but within the Dynamic sql. How can i use this variable from outside of the Dynamic Sql?
Thanks

TRY THIS

declare @myDynamic varchar(100)

declare @table varchar(100)

set @table = ' '

set @myDynamic = 'SELECT count(*) As Count from ' + @table

exec(@myDynamic)

Thanks,

Mahesh

MH-09-AM-8694