Stored procedure calling xp_cmdshell as inline procedure

Question

I want that when a dbo.generate_CSV procedure is called, after all parameters are provided, another inline stored procedure containing xp_cmdshell to be executed.

As it is about bcp export I think there is no other way other than using xp_cmdshell.

Basically user will only have to call generate_CSV procedure to get the desired output.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF EXISTS ( select * from sysobjects where name ='dbo.generate_CSV ' ) 
DROP procedure dbo.generate_CSV 
GO
     CREATE procedure dbo.generate_CSV 
     (
        @dbName varchar(100), 
        @tblName varchar(100),
        @outputPath varchar(100) 
     )

AS

GO

   DECLARE @sql varchar(8000)
   SELECT @sql = 'bcp "select * from @dbName..@tblName"' + ' queryout 
   @outputPath -c -t"; " -r"\n" -T -S localhost' --+ @@servername



--EXEC master..xp_cmdshell @sql

GO

Users only concern is executing this procedure.

EXEC dbo.generate_CSV @dbName = '[dbName]', @tblName = 
'[tblName]',@outputPath = '[outputPath]'

So my question is how can I call exec xp_cmdshell within dbo.generate_CSV procedure so that when generate_CSV is executed, bcp copy is invoked and we get our CSV file.

EDIT 1: When I remove comment and execute this I receive following errors...

SQLState = 37000, NativeError = 137 Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@dbName". SQLState = 37000, NativeError = 8180 Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared. NULL

What do you mean by saying INLINE PROCEDURE? aND WHAT IS YOUR QUESTION? — sepupic
How can I call exec xp_cmdshell within dbo.generate_CSV procedure so that when generate_CSV is executed, bcp copy is invoked and we get our output CSV file. — unknown
You already wrote everything, all you need is to get rid of "GO" after "AS" and to uncomment EXEC — sepupic
Yes, but I will get an error that statements can't be prepared as well as that some variables need to be declared. — unknown
So your question is about how to pass a variable with tblName into xp_cmdshell? Use dynamic sql for doing this — sepupic

sepupic sepupic · Accepted Answer · 2017-12-18T10:06:50

You should concatenate tblName this way:

SELECT @sql = 'bcp "select * from ' + @dbName+ '..' + @tblName + '"' + ' queryout 
   @outputPath -c -t"; " -r"\n" -T -S localhost' --+ @@servername

Stored procedure calling xp_cmdshell as inline procedure

1 Answers