August 27, 2008 at 8:38 am
I have a query that is giving an error, and I understand why it is giving an error, but and I want to know if anyone has any idea how to emulate this behavior...
declare @char_pos int
declare @char_pos2 int
select @char_pos = charindex(',',comma_delimited_field),
substring(comma_delimited_field,1,@char_pos) field1,
@char_pos2 = charindex(',',comma_delimited_field,@char_pos+1),
substring(comma_delimited_field,@char_pos+1, @char_pos2-@char_pos-1) field2
from datatable
(and this an example, i know 30 different ways to redo this specific query to get it to work; i'm more interested in seeing if there is a way to assign variables on a row/field level inside a select statement).
August 27, 2008 at 8:58 am
The only way I could see doing it is through iteration. Ie, the dreaded cursor or a While Loop.
I've never seen anyone assign values to multiple variables in one statement, though. I've always assumed (<- evil word that @=) that each variable had to have an individual Set or Select statement.
August 27, 2008 at 9:18 am
It can be done if it's only variables (I've included an example below). However I'm looking for the holy grail of actually assigning and using the the variables in the same pass. Hopefully it's on MS's to do list 😀
declare @invited_guests table
(
first_name varchar(32),
last_name varchar(32)
)
INSERT INTO @invited_guests
select 'John', 'Smith' UNION ALL
select 'Jane', 'Doe' UNION ALL
select 'Ralph', 'Anderson' UNION ALL
select 'Sarah', 'Connor' UNION ALL
select 'Michael', 'Peterson'
declare @sentence varchar(1024), @last_name_list varchar(1024)
--create string variables
set @sentence = ''
set @last_name_list = ''
select @sentence = @sentence + first_name + ', ',
@last_name_list = @last_name_list + last_name + '''s, '
from @invited_guests
--create sentence
set @sentence = 'We shall invite ' + Left(@sentence,Len(@sentence)-1) --crop last comma
set @sentence = @sentence + ' to the party. Send invitations to the '
set @sentence = @sentence + Left(@last_name_list,Len(@last_name_list)-1) + '.'
select @sentence
August 27, 2008 at 9:31 am
HI there,
Sorry about this, but I'm not 100% sure what you trying to do!
Could you give me an input output example so that I can try and help?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 9:56 am
I am seeing if it is possible to assign a variable, and utilize the same variable that I assigned all in one query. Here's some examples:
declare @int_num int
select @int_num = 1,
@int_num number
select name,
@int_num = Len(name),
@int_num table_name_length
from sys.objects
Both of these queries will fail and report the following error:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
I am asking to see if anyone know of a work around or hack to accomplish the same thing (assign a variable and utilize it) without doing multiple queries
August 27, 2008 at 10:01 am
So you want to assign and return the value?
Or do you simple want to assign and use it again to calculate other variables?
If so then simply doing this will take care of the 2 problem.
DECLARE @Var1 VARCHAR(20)
DECLARE @Var2 VARCHAR(20)
SELECT @Var1 = 'First',
@Var2 = 'Second:' + @var1
PRINT @Var2
as for the first part I've never come across that but will have a look around!
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 10:29 am
gpriester (8/27/2008)
I am seeing if it is possible to assign a variable, and utilize the same variable that I assigned all in one query. Here's some examples:
declare @int_num int
select @int_num = 1,
@int_num number
I see where you're going with this. English language version is:
My variable @test-2 is a varchar(5). (ie, the declaration)
I need to set Test to the number of records in my table as well as display the value of Test all in one statement. (ie, the second statement above).
What you're trying to do can be done in certain programing languages and can be done in Crystal Reports (ie, "StringVar @MyVar = 'WhooHoo' " is a declaration and a set statement all the same time) but T-SQL has no current way of doing what you want. You have to set the variable first, then you can use it.
This behavior may have changed in SQL 2008, btw.
August 27, 2008 at 10:34 am
Actually, looking back at your example, I'm not even seeing the reason you need to use variables at all. Why not just do your Select statements without them?
Going with your first example, I would do:
Select
substring(comma_delimited_field,1,charindex(',',comma_delimited_field)) as field1,
substring(comma_delimited_field,charindex(',',comma_delimited_field)+1,
charindex(',',comma_delimited_fieldcharindex(',',comma_delimited_field)
+1)-charindex(',',comma_delimited_field)-1) as field2
from datatable
August 27, 2008 at 10:48 am
write that query for 10 fields instead of two, then you'll understand my pain 😀
seriously, I've already done it (i ended up writing code to create the code). But I have a feeling that it will expand past 10 fields soon.
August 27, 2008 at 10:55 am
Oh, believe me. I know that pain. I have approximately 150 nested formulas I had to redo from an excel workbook report so I could automate the report.
Nested upon nested upon nested. I didn't actually count the number of times I had to cut-n-paste old formulas into each new select statement and stored procedure, but there were approximately 75 stored procs I had to write and each one had several (10 or more) of those types of formulas. So, your pain is acknowledged. @=)
You just can't do much about it unless you want to utilized temp tables and do constant value updates of the temp tables, or While Loops or just do it the painful way.
August 27, 2008 at 11:21 am
gpriester (8/27/2008)
I am seeing if it is possible to assign a variable, and utilize the same variable that I assigned all in one query.
Not with a SELECT statement. It is, however, possible with an UPDATE as the following code demonstrates:
-- create a sample table
create table #ColLen(
ID int identity(1,1) NOT NULL Primary Key Clustered
, TableName SYSNAME
, Col SYSNAME
, ColLen int
, Cum bigint
)
-- load it with data
INSERT into #ColLen(TableName, Col, ColLen, Cum)
Select Table_Schema+'.'+table_Name
, Column_name
, Coalesce(Character_Octet_length, (Select Max_Length
From sys.types where name=Data_Type), 4)
, 0
From INFORMATION_SCHEMA.COLUMNS
Order By Table_Schema, Table_Name, Ordinal_Position
Declare @cum bigint, @TableName SYSNAME
Select @Cum = 0, @TableName = N''
-- Calculate running Table Widths
UPDATE #ColLen
Set @Cum = Case
When @TableName <> TableName Then ColLen
Else ColLen + @Cum End
, Cum = @Cum
, @TableName = TableName
-- Show it
Select * from #ColLen
-- clean-up
drop table #ColLen
You need to make sure that you have a Clustered index to enforce the order that you want and MAXDOP set to 1.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 27, 2008 at 11:48 am
I think I can make something happen with that.... Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply