results from execute
-
I basically have a process that defines a @sql1 as varchar(1000) then selects @sql1 to some select statement
I then exec the @sql1 statement.. Great.. Ok.. now what I can't seem to get to work right is to also define another var, and populate it with the results from exec @sql1
so.
something like
select @size=exec @sql doesn't work, neither does the set commmand, I have tried some combos of () and no luck either.
I know I can go back and not build my select string upfront, but I do prefer that method.
Does anyone know of a workaround or the correct syntax?
Thanks
-
Here's an example. You'll need to use sp_executesql, and be sure to define the @ncommand (in your case, @sql) and the @nparmlist as nvarchar, not varchar.
declare
@ncommand nvarchar(1000),
@nparmlist nvarchar(100),
@fname varchar(20),
@lname varchar(20)
set @lname = 'accorti'
set @ncommand = N'select @fname = fname from pubs.dbo.employee where job_lvl = 35 and lname = @lname'
set @nparmlist = N'@lname AS VARCHAR(20), @fname as VARCHAR(20) OUTPUT'
EXEC sp_executesql @ncommand, @nparmlist, @lname, @fname OUTPUT
select @fname
-
Before you decide upon preferring using dynamic SQL, I recommend you (and everyone else) to read this article about the curse and blessings of dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
/Kenneth
-
As long as your returning one row, hoo-t's example is prefered. If you're returning more than one row. If not, then use a table to store the returned values.
declare
@ncommand nvarchar(1000),
@nparmlist nvarchar(100),
@fname varchar(20),
@lname varchar(20)
set @lname = 'a'
if object_ID('tempdb..#Return') is not null drop table #Return
create table #Return (Col1 varchar(20))
set @ncommand = N'select fname from pubs.dbo.employee'
Insert #Return
EXEC sp_executesql @ncommand
select * from #Return
But like Ken said, don't use dynamic SQL unless there is a GOOD reason.
Signature is NULL
-
Oh yeah, you can use a csv (or other delimiter) and use the output parameter for multiple rows. However, you then have to parse the string:
declare
@ncommand nvarchar(1000),
@nparmlist nvarchar(100),
@fname varchar(8000)
set @ncommand = N'select @fname = isnull(@fname + '','', '''') + fname from pubs.dbo.employee where job_lvl = 35'
set @nparmlist = N'@fname as VARCHAR(8000) OUTPUT'
EXEC sp_executesql @ncommand, @nparmlist, @fname OUTPUT
select @fname
Signature is NULL
-
Ok.. I am not sure why this isn't working but maybe a second set of eyes? I have tried multiple iterations..but here is one of them..
Bascially the select returns a size in each loop. I am trying to capture the value into the @size variable. Once I get the sp_executesql to run, the @min-2 variable is always null.
declare @name varchar(20)
declare @dbid int
declare @sql1 nvarchar(4000)
declare @size nvarchar(100)
declare @min-2 nvarchar(20)
declare dbname cursor for
select convert(varchar(20),name) as name, dbid from sysdatabases where dbid >6
open dbname
fetch dbname into @name,@dbid
while @@fetch_status >= 0
begin
select @sql1=N'select str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)'+ ' MB ' + 'from ' + @name + '.dbo.sysfiles'
EXEC sp_executesql @sql1, @min-2 OUTPUT
SELECT @min-2
fetch dbname into @name,@dbid
end
deallocate dbname
-
This is JUST A FIX; I am not advocating this as the best method.
declare @name varchar(20),
@dbid int ,
@sql1 nvarchar(4000),
@size nvarchar(100),
@min nvarchar(20)
declare dbname cursor for
select convert(varchar(20),name) as name, dbid from sysdatabases where dbid >6
open dbname
fetch dbname into @name,@dbid
while @@fetch_status >= 0
begin
select @sql1=N'select @min-2 = str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)'+ '+ ''MB'' ' + 'from ' + @name + '.dbo.sysfiles'
EXEC sp_executesql @sql1, N'@Min nvarchar(20) Output', @min-2 OUTPUT
SELECT @min-2
fetch dbname into @name,@dbid
end
deallocate dbname
Signature is NULL
-
Thanks so muc for the help! I had tried something very similar to your suggestion, but must have had a typo? Just couldn't get it to work!
As a follow up question, I would like to control the output better. How can i turn off the column headers via tsql? I know where to do it via the options but not tsql. Can I also set an option to rite the results to a csv file? via tsql, rather than the option screen as well?
Thanks
-
I'm confused by what you mean with "Column Headers". As for exporting to csv; use DTS.
cl
Signature is NULL
-
Calvin,
Thanks! The query analyzer tools/options/results/ print column headers is a option in the gui. do you know of a way to control this via tsql? I dont need the line of ------------------------------------------
in each resulst set. turning off the print column headers option gives the result set I am looking for. But I need to control that via tsql.
Thanks again!
John
-
Ahh...I see. OK, the "Column Headers" you refer to are Query Analyser specific, and have nothing to do with tSQL. It's kinda confusing (like "Go"), but it is just a function of the QA client (controlling how to display the data).
If you're trying to export this dataset as a file (without a header), you should use DTS. The "Text File Source" type has an option to turn the header row off or on.
If you're trying to do something you'll need to explain it in more detail; I think you've got some wires crossed or something.
cl
Signature is NULL
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply