how to EXEC with no output???
-
here is my code:
set @FileExist = 'SELECT * FROM OPENROWSET(BULK ''' + @File + ''' ,SINGLE_CLOB) AS Document'
EXEC (@FileExist)
I want the "EXEC (@FileExist)" to output into a variable, i don't want the value to be shown on the screen... but i can't figure out how ..
-
-
To elaborate, @Results is a table variable with a schema to match the output of your query.
SQL guy and Houston Magician
-
table variable?
can't i just define it as a varchar, and select just one field? or count(1)?
-
you can't return results from EXEC() into a varchar. (unless you take the results out of the table variable)
I may be missing something here, but why are you using dynamic SQL in the first place? If you rewrote the query to use straight SQL you could assign the results to a variable that way.
SQL guy and Houston Magician
-
Also, use a temp table instead of a table variable. You cannot use
insert @TableVariable exec ('select 1')
in SQL 2000.
Try
Create table #X (
c1 varchar(10) null
 
declare @Y varchar(10)
insert #X
exec('select 1')
select @Y = c1 from #X
--print @Y
drop table #X
-
well am using a qynamic query coz i need to do a try catch. I'm trying to know if the file exists, and i can't use xp_fileexist not xp_cmdshell because the user is not in sysadmin... so am using OPENROWSET .... I dunno how to handle the error it might give if not in a dynamic query with exec and try catch
any idea?
BEGIN TRY
set @FileExist = 'SELECT * FROM OPENROWSET(BULK ''' + @File + ''' ,SINGLE_CLOB) AS Document'
EXEC (@FileExist)
END TRY
BEGIN CATCH
select @ErrorMsg = ERROR_MESSAGE()
if @ErrorMsg != '' begin
print 'file not found' + @ErrorMsg
return
end
END CATCH;
-
set @FileExist = 'SELECT @Results=ColumnToCapture FROM OPENROWSET(BULK ''' + @File + ''' ,SINGLE_CLOB) AS Document'
exec(@FileExist)
print @Results
-
Variable @Results is in different scope when you execute via dynamic sql.
You have to use sp_executesql if you want to get result back into variable from dynamic sql. Check proper syntax for sp_executesql in BOL
-
You still cannot output to a variable your results even when using sp_executesql.
-
-
Well, Actually you can get input and output parameters using sp_executesql...
Note the @results parameter is returned just fine:
declare
@results intdeclare
@sql nvarChar(2000)declare
@paramDef nVarChar(500)select
@sql=N'Select @results=1',@paramDef
= N'@results int Output'exec
sp_executeSql @sql, @paramDef, @results=@results Output;print
'"' + convert(varChar(10),@results) + '"' -
"@results=@results Output"
Ooops, didn't realize that.
-
And, xp_FileExists does not require SA privs... neither does xp_DirTree which is xp_FileExists on steroids!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply