Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
549 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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(
@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.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.