Dynamic query issue

Question

Dynamic query issue

Saritha-450752

Say Hey Kid

Points: 690
More actions
October 9, 2007 at 7:12 am

#62960

i have some issue in dynamic queries
i have a table with some month names and need to update based on current month and also the updating parameter value is also dynamic .
for eg consider below table and queries...
create table temp (a char(10),sep decimal(5,1),oct decimal(5,1))
insert into temp values ('as',10,20)
declare @sep decimal(5,1)
declare @oct decimal(5,1)
declare @val decimal(5,1)
declare @col nvarchar(3)
set @sep = 1000
set @oct = 300
set @col = SUBSTRING(CONVERT(varchar,GETDATE(),107),1,3)
select @col
DECLARE @SQL NVARCHAR(500)
select @sql = 'update temp set '+
SUBSTRING(CONVERT(varchar,GETDATE(),107),1,3) +'= '+ 'case when @col = ''Oct''then @oct else @sep end'
select @sql
-- exec sp_executesql @sql,N'@col decimal(5,1)', @col
exec sp_executesql @sql,N'@oct decimal,@col char,@sep decimal',@oct = @oct,@col=@col,@sep=@sep
-- update temp set Oct= case @col when 'Oct'then @oct else @sep end
select * from temp
my expectation was to update the oct column in table with @oct(300)
but this does not happen case when @col = ''Oct'' is not working
what could be tha alternative

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 1

I think you're confused, or at least one of us is. Your code only contains a single value, but you're muddying the issues here.

You have 3 columns, a month, and two values. I'm not sure what you're trying to do with the update. Which column do you want to update based on the month?

Don't use @oct, @sep, @nov, as the variables. These are placeholders. You wouldn't necessarily created separate variables for each month of data. Typically the variables represent something being set in a column, not a row. Rows being

Month Val

------ ----

'Sept' 300

'Oct' 10

Can you better explain what you're trying to do?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 2

you need to set a length for the @col parameter. otherwise it defaults to 1 and all you get is 'O'.

exec sp_executesql @sql,N'@oct decimal,@col char(3),@sep decimal',@oct = @oct,@col=@col,@sep=@sep

Saritha-450752 Say Hey Kid Points: 690 More actions · Answer 3

thanx i needed a scenario which has table with columns for different months.thanx anyways..i solved it.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

Saritha... it would be really nice if you shared how you solved it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

goodguy SSCertifiable Points: 5456 More actions · Answer 5

I ran Saritha's code and my @sql var builds up as:

Update #temp set Oct = case when @col = ''Oct'' then @oct else @sep end

I am puzzled why the CASE statements results to False when it should very obviously default to True?

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

Goodguy, look at my earlier post. If you do not specify a length for the parameter @col it defaults to a length of 1. The value 'Oct' is truncated to 'O' which does not equal 'Oct' and returns false.

goodguy SSCertifiable Points: 5456 More actions · Answer 7

Thanks, Mr Polecat. Problem Solved.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons