CODEWITHSUNDEEP
sql-server
R--Cook
R--Cook

Reputation: 1

How to incorporate a variable as the SIZE argument of VARCHAR in an SQL Server query

I am not an SQL Server expert, by any means, but have enough knowledge to make me dangerous. I've spent hours searching the internet and could not find a valid example of what I am trying to accomplish here. Can someone please explain to me why the following code is failing? This, of course, is not its final usage, but I have simplified the code to clearly demonstrate the problem.

Below are copies of the SQL code and their results:

Query:

DECLARE @MaxLength AS INT    
SELECT @MaxLength = 45    
SELECT CAST(site_name AS VARCHAR(@MaxLength)) FROM sites

Messages:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@MaxLength'.

When I substitute a literal integer (e.g., 45) in place of the local variable (@MaxLength), it works fine, as indicated below:

Query:

--DECLARE @MaxLength AS INT    
--SELECT @MaxLength = 45    
SELECT CAST(site_name AS VARCHAR(45)) FROM sites

Messages:

(1550 row(s) affected)

As stated above, I simplified the code example for the purpose of clarity, but the final code block will present multiple column output and is bound for an 8 1/2 x 11 sheet of paper. My aim is to force the column widths of the output to the smallest width possible without truncation of the data. This is why VARCHAR(MAX) is not a solution. Below is a larger chunk of the code that will appear in the final query, and this may clarify what I'm attempting to accomplish:

DECLARE @MaxLength AS INT
SET @MaxLength = (SELECT MAX(LEN(site_name)) FROM sites)
SELECT CAST(site_name AS VARCHAR(@MaxLength)) AS site_name, CAST(customer_name AS CHAR(40)) AS customer_name, CAST(remote_agent_name AS CHAR(35)) AS RA_Name, CAST(subtype_name AS CHAR(20)) AS subtype_name
FROM sites, customers, remote_agent, remote_agent_subtype

Upvotes: 0

Views: 55

Answers (2)

Mike M
Mike M

Reputation: 1425

Based on what you're describing, I wonder if there is a more direct answer.

If you want to trim down a column during a SELECT, why not 

SUBSTRING()

http://msdn.microsoft.com/en-us/library/ms187748(v=sql.105).aspx

So if you want to make sure it is also as small as possible (the point of varchar), certainly you can put an RTrim on as well...

declare @caseMaxLen int;
select @caseMaxLen = 3;

select 
    rtrim(substring('mystring', 1, @caseMaxLen))

If you want to error on truncation, just check that separately in advance.
If there a maximum, then there is a maximum... you'll have to check that if you want to know.

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6781

You'd have to use dynamic sql to accomplish this.

However, as the Aaron Bertrand stated in the link Bridge shared, it's hard to imagine a good reason to do this rather than just use a large number you know you'll never exceed, such as 8000 or MAX.

Upvotes: 1

Related Questions