This page has 0 threads | Add post

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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

Head office

London

Manchester