Only run SQL query if condition met

Question

I'm trying to write an 'idiot proof' SQL script that can be run by non-SQL literate users.

My idea is to have a couple of variables defined at the top of the script, then run particular queries based on those variable.

I'm testing on mySQL, but it will eventually run on SQL-Server.

In pseudo-code this is what I'm trying to do:

# Set matchThis to the value to match
SET @matchThis = "matchMe";

# Uncomment (remove the #) one of the two lines below to update or just view
#SET @update = "YES";
SET @update = "NO";

IF @update = "YES" {
    UPDATE myTable SET myColumn = "changed" WHERE matchVal = @matchThis;
} ELSE {
    SELECT * FROM myTable WHERE matchVal = @matchThis;
}

I want to do this entirely in SQL if there is a way.

I've seen guides on using SELECT IF etc but can't figure out how to achieve the above.

Answer 1

This is for MSSQL. I think that you got everything down but the syntax. I hope this helps/works.

DECLARE @matchthis AS VARCHAR(MAX)
DECLARE @update AS VARCHAR(1)

SET @matchthis = 'matchme'
--@update can be Y or N. User changes this here.
SET @update = 'Y'

IF @update = 'Y'
    UPDATE mytable SET myColumn = 'changed' WHERE matchval = @matchthis
ELSE IF @update = 'N'
    SELECT * FROM myTable WHERE matchval = @matchthis

I didn't know whether to make the changed a variable, but if you wanted to make it a variable, follow the same syntax as for @matchthis (declare and set).

If you want to make this really idiot proof, I'd say the best thing to do is make a stored procedure so that users don't see the code, they just have the input box.

Answer 2

There are two questions here. One is why the IF statement doesn't work - because T-SQL doesn't have braces. The syntax is shown in the documentation.

The important question though is how to pass parameters to the script without having the users modify the script itself. This is done using Script Variables. When a script is executed by using the sqlcmd command, any text of the form $(SomeName) is replaced with command-line parameters or environment variables with the same name.

For example, if you have the following script

USE AdventureWorks2012;
SELECT x.$(ColumnName)
FROM Person.Person x
WHERE c.BusinessEntityID < 5;

This command will run it with FirstName as the column name

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql