Sql-server – How to pass the table name as an argument

sql serversqlcmd

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
'.

Best Answer

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'

Related Solutions

Sql-server – use parameters in a Cte

The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL 
  ,@EndDate As datetime = NULL 
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);


END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.

Sql-server – Update thesql DB when data has a single quote with Dynamic values

NOTE: Several questions/comments that wouldn't fit into a comment block ...

What language is your linux script written in? bash? ksh? perl? something else?

Where is the CN value coming from and how are you handling it, eg, is the CN value initially stored in a variable which is then used to build the actual query?

Have you tried passing in the CN value by way of a variable (either sqlcmd/-v or direct OS variable reference like "update ... CN = '${CN}'...")? [I'm guessing it still fails but want to confirm this.]

Could you pre-process the CN value, replacing any occurrences of a single quote with either a) two single quotes ('') or b) a backslash plus single quote (\'); then feed this resulting value into your dynamically created query? [Depending on your scripting language, and how you're handling the CN value, this may be doable with littler overhead.]

While your example demonstrates the issue, it doesn't show us how you're building the query itself ... so I'm wondering if there could be a (relatively) easy solution that, in effect, allows us to escape the single quote while building the query ... ?

Best Answer

Related Solutions

Sql-server – use parameters in a Cte

Sql-server – Update thesql DB when data has a single quote with Dynamic values

Related Question