need to use dynamic variable in a select statement

Question

Post reply

need to use dynamic variable in a select statement

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
September 1, 2010 at 12:06 pm

#226445

i am attaching this query which is not working right. I am sure there could be a better way to do this, i am not sure how to get this done.
declare @db_name nvarchar(max),@SQLString nvarchar(max)
set @db_name='TestDB'
--select @SQLString ='select @db_name'
--exec (@SQLString)\
select @db_name
select @SQLString ='
select ''@db_name''as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures
where create_date > ''2009-08-20''
order by modify_date desc'
exec (@SQLString )
I need to get the @db_name in the output. Please help.!! Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Nevyn SSChampion Points: 14210 More actions · Answer 1

Try:

declare @db_name nvarchar(max),@SQLString nvarchar(max)

declare @create_date datetime

set @db_name='TestDB'

set @create_date = '2009-08-20'

--select @SQLString ='select @db_name'

--exec (@SQLString)

select @db_name

select @SQLString ='select ' + @db_name + ' as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures

where create_date > @create_date

order by modify_date desc'

EXECUTE sp_executesql @SQLString,N'@create_date datetime',@create_date=@create_date

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Nevyn (9/1/2010)
Try:
declare @db_name nvarchar(max),@SQLString nvarchar(max)
declare @create_date datetime
set @db_name='TestDB'
set @create_date = '2009-08-20'
--select @SQLString ='select @db_name'
--exec (@SQLString)
select @db_name
select @SQLString ='select ' + @db_name + ' as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures
where create_date > @create_date
order by modify_date desc'
EXECUTE sp_executesql @SQLString,N'@create_date datetime',@create_date=@create_date

Says invalid column name near select '+@dbname +' .

Nevyn SSChampion Points: 14210 More actions · Answer 3

sounds like you didnt paste the first single quote. I can't test the executesql statement but I did test the assignment statement, which seems to be where you're getting tripped up

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

I think it just needs 3 quotes either side of @db_name

select @SQLString ='select ''' + @db_name + ''' as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures

where create_date > @create_date

order by modify_date desc'

EXECUTE sp_executesql @SQLString,N'@create_date datetime',@create_date=@create_date

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

steve-893342 (9/2/2010)
I think it just needs 3 quotes either side of @db_name
select @SQLString ='select ''' + @db_name + ''' as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures
where create_date > @create_date
order by modify_date desc'
EXECUTE sp_executesql @SQLString,N'@create_date datetime',@create_date=@create_date

this worked..thanks

R.P.Rozema SSChampion Points: 12317 More actions · Answer 6

I would suggest using the quotename() function. Here's an example. Plus, as a bonus, this runs the query against all databases in your server.

declare @create_date datetime

declare @cmd nvarchar(max);

set @create_date = '2009-08-20'

select @cmd = stuff((

select ' union all select ' + quotename(name,'''') + ' as databse,type_desc,create_date,modify_date from ' + quotename(name) + '.sys.procedures where create_date > @create_date' as [text()]

from master.sys.databases

for xml path(''), type

).value('.', 'nvarchar(max)'), 1, 11, '') +

' order by 1, modify_date desc'

exec sp_executesql @cmd, N'@create_date datetime', @create_date;

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

thanks but i need to restrict the loop to certain dbs'. I had to create a cursor and works fine. Thanks

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 8

Make sure to read this fully

www.sommarksog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 9

Madhivanan-208264 (9/7/2010)
Make sure to read this fully
www.sommarksog.se/dynamic_sql.html

Oh yes, i have already read this article may be not fully but to certain extent. Also this code is not for any application, just for some validation which runs once in while..thnaks

R.P.Rozema SSChampion Points: 12317 More actions · Answer 10

All you need to do is add a where clause. And you'll have the functionality without a cursor. Something like where name in ('testdb','myotherdb')

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?