How can I pass the table name as an argument

Question

I have this code

ALTER PROCEDURE [dbo].[tablepass12]
    (@TableName nvarchar(20))
AS
BEGIN
    DECLARE @SQL VARCHAR(8000)

    SELECT @SQL = 'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv' 

    EXEC MASTER..XP_CMDSHELL @SQL
END;

When execute it, I get this error

Msg 105, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
Unclosed quotation mark after the character string 'AIR_LINE '.

Msg 102, Level 15, State 1, Server ICB3110\SQLEXPRESS, Line 1
Incorrect syntax near 'AIR_LINE '.

Answer 1

As the error states:

Unclosed quotation mark

Your string is:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "'+@TableName+' -o d:\processing\'+@TableName+'.csv'

Once the @TableName variable is replaced with its value, the string gets rendered as:

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM "AIR_LINE -o d:\processing\AIR_LINE.csv'

If you look at the -Q parameter, it is malformed as:

"SELECT * FROM "AIR_LINE

You just need to move the double-quote (") to the other side of the variable (it should be to the left of the -o, not to the right of the FROM):

'SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv'