July 2, 2008 at 7:29 am
I am having a few issues properly passing my parameters to sp_executesql. I have a loop that goes through my server and retrieves into a variable @dbname the name of each database. This part is working as I had the following:
declare @dbname nvarchar(128)
declare @sqlstring nvarchar(1000)
...
select @sqlstring = N'select @db'
execute sp_executesql @sqlstring, N'@db nvarchar(128)', @dbname
And I get the output as master, tempdb, msdb, etc.
However when I change @sqlstring to the following: select @sqlstring = N'select @db..sysindexes'
I get an incorrect syntax warning.
I've tried various versions of using [] and ', '', and ''' around @db but to no avail. I know the databasename is being passed so how do I extract it? This script used to work in an execute @sqlstring statement. If in this case, I do the following:
select @sqlstring = N'select ' + @dbname ''
execute sp_executesql @sqlstring
I get no benefit from sp_executesql vs. execute.
Help me Obiwan Kenobi, you're my only hope. 🙂
Gaby Abed
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 2, 2008 at 7:38 am
Gaby Abed (7/2/2008)
However when I change @sqlstring to the following: select @sqlstring = N'select @db..sysindexes'
you need to concatenate the value of the variable into the string. This should more-or-less get you what you want.
select @sqlstring = N'select ' + @db + '..sysindexes'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2008 at 8:13 am
Hmmm...that gave me an error, the reason being that @db only exists in the context of @sqlstring so when I build it outside, I get those errors.
On another note, here's something I tried that works, but is quite convoluted:
set @sqlstring =
'declare @minisql nvarchar(100);
set @minisql = ''select * from ''+@db+''..sysindexes'';
execute(@minisql)'
execute sp_executesql @sqlstring, N'@db nvarchar(128)', @dbname
This way DOES work, although running a direct execute within an sp_executesql seems pointless, I guess it still gives me more control over what variables are passed (such as @dbname).
Thanks for the reply and help. 🙂
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 3, 2008 at 12:00 am
Cuold you post all the code please. Including where @db coems from?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 6:20 am
Gail, here's the original code. It's totally different from the first example I gave except the sp_executesql part.
set @ctr = 1
while @ctr <= @numdb
begin
select @dbname = dbname from @names where id = @ctr
select @sqlstring =
'declare @minisql nvarchar(3000);
select @minisql = ''insert ##idx_results select ind.dbnm, fl.dblog, fl.dbdata, ind.total_reserved, ind.total_used, ind.total_data
from (
select '''''' +@db+ '''''' dbnm,sum(reserved) total_reserved, sum(used) total_used, sum(dpages) total_data
from ['' +@db+ '']..sysindexes (nolock) where indid in (0, 1, 255)
) ind
inner join (
select '''''' +@db+ '''''' dbnm,
sum(case when groupid = 0 then size else 0 end) dblog,
sum(case when groupid <> 0 then size else 0 end) dbdata
from ['' +@db+ '']..sysfiles
) fl on (ind.dbnm=fl.dbnm);''
execute (@minisql);'
execute sp_executesql @sqlstring, N'@db nvarchar(128)', @dbname
set @ctr = @ctr + 1
end
In this example, @names is a table variable that has been populated with all the names of my databases. It fills my @sqlstring with a @minisql string, executing the @minisql inside the sp_executesql. As for the single quotes, they parse in this context fortunately, numerous as they may be.
Thanks.
Gaby
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
July 3, 2008 at 6:34 am
Ok. There's no real gain from passing the db name into the sp_executesql, as it can't be used directly as a variable (as you've seen) This simplifies things a bit
set @ctr = 1
while @ctr <= @numdb
begin
select @dbname = dbname from @names where id = @ctr
select @sqlstring = '
insert ##idx_results
select ind.dbnm, fl.dblog, fl.dbdata, ind.total_reserved, ind.total_used, ind.total_data
from (
select ''' + @dbname + ''' AS dbnm,sum(reserved) total_reserved, sum(used) total_used, sum(dpages) total_data
from [' + @dbname + ']..sysindexes (nolock) where indid in (0, 1, 255)
) ind
inner join (
select ''' + @dbname + ''' AS dbnm,
sum(case when groupid = 0 then size else 0 end) dblog,
sum(case when groupid <> 0 then size else 0 end) dbdata
from [' + @dbname + ']..sysfiles ) fl on (ind.dbnm=fl.dbnm) ; '
--PRINT @sqlstring
exec sp_executesql @sqlstring
set @ctr = @ctr + 1
end
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 6:48 am
Thanks very much, that helped quite a bit.
Gaby
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply