How to define string variable in SQL server, so I can use the string in multiple place in program and make refactor easier?

Question

In high level programming language such as Java or Swift, if I want to use a string multiple times in the same program, I can define a string variable to represent that string, so to avoid typing the same string in multiple places. at the same time, making refracting the string easier

In Java

final String myStringConstant = "hello world";

In Swift

let myStringConstant = "hello world"

I was just wondering if it possible to do the same in Microsoft SQL Server? I have seen code that type the same string value in multiple places in the code, which makes it very easy to misspell the string in one of the place and therefore screw up the program. A string variable that used to represent the string would be extremely useful.

Declare @myStringConstant varchar(100). It will accept Size upto 8000. If you want to store a string of length more than 8000 then use Max — Pரதீப்
– Pரதீப், Commented Jun 21, 2017 at 14:41
If a variable is not what you are looking for because you want this value available in multiple procedures why not put it in a table? — Sean Lange
– Sean Lange, Commented Jun 21, 2017 at 14:47
@Prdp and GuidoG, Thank you both for the suggestion. I was wondering, if I defined a string at the top of a tsql script and this script is divided into several section by the "go" keyword, will I be able to reference the string in each sections between the keyword "go"? — Thor
– Thor, Commented Jun 21, 2017 at 14:58
If your T-SQL script contains GO, it's actually an SQLCMD script. SQLCMD has variables, which fulfil the same purpose. Despite the name, however, they're more like macros, since the replacement is purely textual, and the value cannot be set from an SQL command. — Jeroen Mostert
– Jeroen Mostert, Commented Jun 21, 2017 at 15:22

Mikhail Lobanov · Accepted Answer · 2017-06-21 15:40:26Z

4

It's impossible, but you can create scalar user-defined function that return this constant

CREATE FUNCTION dbo.fn_HelloWorld ()
RETURNS VARCHAR(MAX) 
AS
BEGIN 
    RETURN 'Hello world'
END

Note that unlike final in java scalar function in T-SQL can decrease perfomance.

edited Jun 21, 2017 at 15:40

answered Jun 21, 2017 at 14:45

Mikhail Lobanov

3,02612 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Thor Over a year ago

if possible, Could you please provide an short code example?

Sean Lange Over a year ago

Instead of a scalar function why not put this type of data in a table?

Mikhail Lobanov Over a year ago

@SeanLange I think autor ask about hardcoded constants, not settings stored somewhere

Sean Lange Over a year ago

If it is a hard coded constant doesn't that pretty define what you could put in a table? At least if the "constant" changes you don't have to change any code. And scalar functions are horrible for performance.

Mikhail Lobanov Over a year ago

Question was about something similar to final. Of course, scalar functions is not equals final in JAVA, but I cannot find something better. you should use APPLY or SELECT .. FROM to read from table without scalar function . sometimes write dbo.fn_HelloWorld() easily then SELECT Value FROM dbo.fn_Option('HelloWorld'). With scalar function code is shorter

|

rd_nielsen · Accepted Answer · 2017-06-21 15:57:47Z

2

My solution to a similar need was to write a custom script processing tool as an alternative to sqlcmd. This tool allows you to define "substitution variables", which are like macros that can be substituted anywhere in code. The substitution variable definitions are hidden in SQL comments, using a syntax similar to your examples:

-- !x! sub MyStringConstant hello world

where !x! is a token that identifies this as a command to the script processor, sub is the command to define a substitution variable, and the rest should be obvious. If you're amenable to trying an alternative to sqlcmd, you can get this alternative script processor from https://pypi.python.org/pypi/execsql

answered Jun 21, 2017 at 15:57

rd_nielsen

2,4592 gold badges14 silver badges19 bronze badges

Comments

paparazzo · Accepted Answer · 2017-06-21 17:48:14Z

1

declare @var int = 12;
select @var;
select * from bid;
select @var;

but it will not survive a go;

answered Jun 21, 2017 at 17:48

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Collectives™ on Stack Overflow

How to define string variable in SQL server, so I can use the string in multiple place in program and make refactor easier?

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related