Define a String constant in SQL Server?_问答_开发者

Is it possible in SQL Server to define a String constant? I am rewriting some queries to use stored procedures and each has the same long string as part of an IN statement [a], [b], [c] etc.

It isn't expected to change, but could at some point in future. It is also a very long string (a few hundred characters) so if there is a way to define a global constant for this that would be much easier to work with.

If this is possible I would also be interested to know if it works in this scenario. I had t开发者_C百科ried to pass this String as a parameter, so I could control it from a single point within my application but the Stored Procedure didn't like it.

You can create a table with a single column and row and disallow writes on it.

Use that as you global string constant (or additional constants, if you wish).

You are asking for one thing (a string constant in MS SQL), but appear to maybe need something else. The reason I say this is because you have given a few hints at your ultimate objective, which appears to be using the same IN clause in multiple stored procedures.

The biggest clue is in the last sentence:

I had tried to pass this String as a parameter, so I could control it from a single point within my application but the Stored Procedure didn't like it.

Without details of your SQL scripts, I am going to attempt to use some psychic debugging techniques to see if I can get you to what I believe is your actual goal, and not necessarily your stated goal.

Given your Stored Procedure "didn't like that" when you tried to pass in a string as a parameter, I am guessing the composition of the string was simply a delimited list of values, something like "10293, 105968, 501940" or "Juice, Milk, Donuts" (pay no attention to the actual list values - the important part is the delimited list itself). And your SQL may have looked something like this (again, ignore the specific names and focus on the general concept):

SELECT Column1, Column2, Column3
FROM UnknownTable
WHERE Column1 IN (@parameterString);

If this approximately describes the path you tried to take, then you will need to reconsider your approach. Using a regular T-SQL statement, you will not be able to pass a string of parameter values to an IN clause - it just doesn't know what to do with them.

There are alternatives, however:

Dynamic SQL - you can build up the whole SQL statement, parameters and all, then execute that in the SQL database. This probably is not what you are trying to achieve, since you are moving script to stored procedures. But it is listed here for completeness.
Table of values - you can create a single-column table that holds the specific values you are interested in. Then your Stored Procedure can simply use the column from this table for the IN clause). This way, there is no Dynamic SQL required. Since you indicate that the values are not likely to change, you may just need to populate the table once, and use it wherever appropriate.
String Parsing to derive the list of values - You can pass the list of values as a string, then implement code to parse the list into a table structure on the fly. An alternative form of this technique is to pass an XML structure containing the values, and use MS SQL Server's XML functionality to derive the table.
Define a table-value function that returns the values to use - I have not tried this one, so I may be missing something, but you should be able to define the values in a table-value function (possibly using a bunch of UNION statements or something), and call that function in the IN clause. Again - this is an untested suggestion and would need to be worked through to determine it's feasibility.

I hope that helps (assuming I have guessed your underlying quandary).

For future reference, it would be extremely helpful if you could include SQL script showing your table structure and stored procedure logic so we can see what you have actually attempted. This will considerably improve the effectiveness of the answers you receive. Thanks.

P.S. The link for String Parsing actually includes a large variety of techniques for passing arrays (i.e. lists) of information to Stored Procedures - it is a very good resource for this kind of thing.

In addition to string-constants tables as Oded suggests, I have used scalar functions to encapsulate some constants. That would be better for fewer constants, of course, but their use is simple.

Perhaps a combination - string constants table with a function that takes a key and returns the string. You could even use that for localization by having the function take a 'region' and combine that with a key to return a different string!