SP_ExecuteSQL parameter help 1

[Mdavis123]

Working on a large Report that has 76 queries of demographics on a quartly basis.

I don't want to requery a quarter if it is completed so we
only recalculate the current quarter and write it to a table that has these columns,
Year, Quarter1,Quarter2,Quarter3,Quarter4,ID
I need help with a dynaminic query that will update the appropriate quarter.

Declare @FieldToUpdate nvarchar(10),@CurYear datetime,
@QtrCnt int
Set @CurYear=getdate()
Set @QtrCnt=15-- result of 1 of 76 queries
Set @FieldToUpdate ='Quarter'+Convert(varchar(1),datepart(qq,@CurYear))
Set @ID=3 -- Really the 1 of 76 queries i'm running

Here is the dynaminic query I need help with. I tried adding the parameters and commas but keept getting errors.

Update mytable set @FieldToUpdate = @QtrCnt where id = @ID

I read Sunl7 FAQ but it doesn't cover multiple params.
Thanks
MikeD

 

[zhavic]

Try:

EXECUTE sp_executesql
N'Update mytable set ' + @FieldToUpdate + ' = @QtrCnt where id = @ID', -- statement to execute
N'@QtrCnt int, @ID int', -- list of parameters passed to sp_executesql
@QtrCnt, -- values
@ID


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[mrdenny]

Declare @FieldToUpdate nvarchar(10),@CurYear datetime,
@QtrCnt int
Declare @CMD varchar(1000)
Set @CurYear=getdate()
Set @QtrCnt=15-- result of 1 of 76 queries
Set @FieldToUpdate ='Quarter'+Convert(varchar(1),datepart(qq,@CurYear))
Set @ID=3  -- Really the 1 of 76 queries i'm running

Here is the dynaminic query I need help with.  I tried adding the parameters and commas but keept getting errors.

set @CMD = 'Update mytable set ' + @FieldToUpdate + ' = ' + @QtrCnt + ' where id = ' + @ID
exec (@CMD)

That should do the trick. You can't have a variable for the column name. This sets the @CMD variable to the update statement that needs to be run. If you want to double check the statement before running it, comment out the exec (@CMD) line and add the line "print @CMD"

Denny

 

[Mdavis123]

Thanks very much,

I thought I could pass the FieldName variable in with the other vars.

Thanks again,
Mike

 

[Mdavis123]

Is there any downside to using sp_execute. I read in another post "don't use it often"

I'm actually calling it 140 time is a sp.

Thanks
Mike

 

[mrdenny]

I've never sean any problems using it.

Denny

 

[Mdavis123]

mrDenny,

The word "Set" cause an identifer problem doing your exec(@CMD)

This is why zhavic method is correct.

Thanks to both.

MikeD