Passing results to a variable 1

[NeilO123]

Help to a begginer

I am trying to pass the results of a SELECT LEN to a variable and then display the results of the variable, but I must have the code wrong as an example:

USE AdventureWorks;
GO
DECLARE @varLen int
SET @varlen = SELECT LEN(FirstName)
SELECT @varLen
FROM Person.Contact
GO
Any help is appreciated since I intent to use the results of @varLen in a string.

Thanks to all in advance

 

[hmckillop]

try

USE AdventureWorks;
GO
DECLARE @varLen int
SELECT @varlen  = LEN(FirstName)
FROM Person.Contact
SELECT @varLen
GO

"I'm living so far beyond my income that we may almost be said to be living apart

 

[NeilO123]

Thanks but I am still getting an error I did add ";" after "int"
but
"Msg 102, Level 15, State1, Line 2
Incorrect syntax near @varLen

 

[markros]

Post here your exact new script.

If you paste the exact code from above into new query window in SSMS and run it, would you get result?

 

[NeilO123]

Thanks markros, I should add that I am using SQL Server 2005 here it is:
--
USE AdventureWorks;
GO
DECLARE @EmpIDVar int;
SET @EmpIDVar = LEN(FirstName)
SELECT @varLen
FROM Person.Contact
GO
Results:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@varLen".
--
I have try changing or adding ";" in different places but with same results

 

[jbenson001]

This is wrong:

SET @EmpIDVar = LEN(FirstName)

Try:

SET @EmpIDVar = (Select LEN(FirstName) From Person.Contact)

 

[NeilO123]

I try the new code see below but I am still getting the same results:
--
USE AdventureWorks;
GO
DECLARE @EmpIDVar int;
SET @EmpIDVar = (SELECT LEN(FirstName)FROM Person.Contact)
SELECT @varLen
--FROM Person.Contact
GO
Results:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@varLen".
--
??

 

[PWise]

USE AdventureWorks;
GO
DECLARE [COLOR=Blue]@EmpIDVar[/color] int;
SET [COLOR=Blue]@EmpIDVar [/color]= (SELECT LEN(FirstName)FROM Person.Contact)
[s]SELECT [COLOR=red]@varLen[/color]
--FROM Person.Contact[/s]
 [COLOR=Blue]Select @EmpIDVar [/color]

GO

 

[NeilO123]

I get a different message, see below
--
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)
--
It should had displayed 19,972

 

[jbenson001]

A subquery can only return one value when you are assigning the result to a variable.

You will have to add a WHERE condition to the subquery.

Can you explain exactly what you are trying to accomplish? I think you may be over thinking something simple.

 

[PWise]

Try

USE AdventureWorks;
GO
DECLARE @varLen int
Select @varLen= Sum(len(FirstName))
From Person.Contact
Select @varLen

 

[NeilO123]

Thanks pwise, I do not get an error but I DO NOT get a listing of all the columns only what appears to be the grand total of all rows:
Results
--
(No column name)
1 117910
--
Expect results
(No column name)
1 7
2 9
etc
--
I need to list and use the results from on each individual rows.

Any suggestions ???

 

[PWise]

How About

Select contactid,len(firstname)
From Person.Contact

 

[markros]

In this case you can not use a variable.

Just try

select FirstName, LastName, Len(FirstName) from Person.Contact

 

[bborissov]

You can
But there is no need of it
It is called TABLE variable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NeilO123]

Thanks pwise and the same to you markros, but I still have a need to pass the results from a column to a variable, in that way I could manipulate the final out come such as:

@varLen=(column1 string length)
SET Colum2 = LEFT(column1,(@varLen -[a number])
---
I know this is feasible inside of VB.NET but I do not know if it can be done in T-SQL?

 

[hmckillop]

declare @varlen int

select @varlen = len(column1) from yourtable

update yourtable
set column2 = left(column1,@varlen - ANumber)

this should do what you want.

Please note the syntax in the first post I made.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[markros]

If will change all the records based on the length of the firstname in the first record.

Is it what you want or you want to update each column2 based on column1 ?

In this case you don't need any variables.

 

[NeilO123]

Thank you all, based on what hmckillop show me I have created the following code that is closer to what I am trying to accomplish;
CODE
--
USE RUMS
GO
--App DCR Tool (CHAR 8)-4
DECLARE @varlen int
SELECT @varlen = len(appName) from dbo.tbl_hits
@varLen=(@varLen-8)
UPDATE dbo.tbl_hits
set buttonText = left(appName,@varlen),
appName='DCR Tool'
FROM dbo.tbl_hits
WHERE (appName LIKE 'DCR%')
--
Results
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@varLen'.
--
I know that I have not properly declare the column "buttonText", I am not sure where I should put the declaration? but the error that I getting is similar to what I was getting earlier??
Column "buttonText" should have the text that was in column "appName" less 'DCR Tool'

Thanks your patience and help

 

[markros]

Perhaps you need instead

update dbo_tbl_hits
set ButtonText = replace(appName,'DCR Tool',''),
appName = 'DCR Tool' from dbo.tbl_Hits
where appName like 'DCR%'