CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Simran
Simran

Reputation: 569

LEN of value in the variable in TSQL

I have a variable @ColumnnName in T-SQL user defined function that basically contains the column Name.

For example :

declare @ColumnnName= 'firstName'

which means I have a column with Name firstName in my table and have this column name stored in variable named @ColumnnName.

I have a query like this:

Insert into @Temp([Row], VarLength) select  Rowid, LEN(@ColumnnName) from Patients
 where 
/ 
*rest of code 
*/

In above query, LEN(@ColumnnName) will return 9 which is the length of firstName but not the value contained in the column firstName.

How do I get length of the value contained the column firstName and not the length of string firstName itself.

Edit:

Complete query is like this :

Insert into @Temp([Row], Counts, VarLength) select  Rowid, @counter, LEN(@ColumnnName) from Patients where 
(case when @ColumnnName = 'firstname' then firstname 
            when @ColumnnName = 'middlename' then middlename 
            when @ColumnnName = 'lastname' then lastname 
            when @ColumnnName = 'State' then [State] 
            when @ColumnnName = 'City' then City 
            when @ColumnnName = 'StreetName' then StreetName 
            when @ColumnnName = 'StreetType' then StreetType end) LIKE SUBSTRING(@parameterFromUser, 1, @counter) + '%'

Upvotes: 0

Views: 532

Answers (2)

Marcelo Myara
Marcelo Myara

Reputation: 3011

You can use the EXECUTE command to run a dynamic SQL. Say you have a string var, called @myvar with the name of a column. You can then build up a complete SQL statement (something like 'select '+@myvar+' from tableName;') and then use the EXECUTE command, like this simple example (from which you can build up your solution easily):

BEGIN
  DECLARE @temp NVARCHAR;
  SET @temp = 'getdate()';
  EXECUTE ('select '+@temp+';');
END;

Hope this helps.

Upvotes: -1

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

This might be a solution to your problem:

INSERT INTO @Temp([Row], VarLength) 
SELECT Rowid, CASE WHEN @ColumnnName = 'FirstName' THEN LEN(FirstName)
                   WHEN @ColumnnName = 'LastName' THEN LEN(LastName)
                   ... etc
              END AS len
FROM Patients
... etc

If you want to use the above CASE expression more than once in your query then you can wrap it inside a CTE:

;WITH CTE AS (
   SELECT Rowid, CASE WHEN @ColumnnName = 'FirstName' THEN LEN(FirstName)
                      WHEN @ColumnnName = 'LastName' THEN LEN(LastName)
                      ... etc
                 END AS len
   FROM Patients
)
INSERT INTO @Temp([Row], VarLength)
SELECT Rowid, len
FROM CTE

Upvotes: 5

Related Questions