559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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(
-- 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
But this won't, because you can't pass the table name or column names as parameters:
CREATE OR ALTER PROC spShowRow(
-- list out all of the rows in a table
SELECT * FROM @TableName
-- list out rows in the film table
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.
|Parts of this blog|
25 Aytoun Street