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