Browse 531 attributed reviews, viewable separately for our classroom and online training
What is SQLCMD mode, and how can you use it?
Part one of a three-part series of blogs

You can use SQLCMD mode to parameterise the connection, database name, table name and select columns for a query - and much more besides, as this blog explains.

  1. What is SQLCMD mode, and how can you use it? (this blog)
  2. Invoking SQLCMD mode
  3. Examples of the use of SQLCMD mode

Posted by Andy Brown on 24 November 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

What is SQLCMD mode, and how can you use it?

So for years we've been telling people that passing parameters to stored procedures only goes so far.  So for example the following query will work because you can refer to parameters in the WHERE clause of a query:

CREATE OR ALTER PROC spShowFilms(

@MinLength int

)

AS

-- list out all of the films lasting

-- more than a given number of minutes

SELECT * FROM tblFilm AS f

WHERE f.Minutes >= @MinLength

GO

-- list out long films

spShowFilms 120

But this won't, because you can't pass the table name or column names as parameters:

CREATE OR ALTER PROC spShowRow(

@TableName varchar(max)

)

AS

-- list out all of the rows in a table

SELECT * FROM @TableName

GO

-- list out rows in the film table

spShowRoW 'tblFilm'

However, it appears that you can do this using something called SQLCMD mode, which has actually been around for a very long time.  So this will work:

Showing all rows from a table

You can use the variable TableName to hold the name of the table whose rows you want to list out.

 

Welcome to SQLCMD mode!  You can spot SQLCMD mode statements because they appear shaded in grey, as above.

SQLCMD mode allows you to do many more things than the example shown above; the rest of this blog shows you how to invoke it, then gives lots of ideas for how you might use it.

  1. What is SQLCMD mode, and how can you use it? (this blog)
  2. Invoking SQLCMD mode
  3. Examples of the use of SQLCMD mode
This blog has 0 threads Add post