This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. What is SQLCMD mode, and how can you use it? (this blog) Invoking SQLCMD mode Examples of the use of SQLCMD mode

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.

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

Posted by Andy Brown on 24 November 2021

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

)

-- list out all of the films lasting

-- more than a given number of minutes

SELECT * FROM tblFilm AS f

WHERE f.Minutes >= @MinLength

-- 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)

)

-- list out all of the rows in a table

SELECT * FROM @TableName

-- 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:

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.