Difference between SET and SELECT

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Quote:

Originally Posted by sukeshchand

Hii,
Anybody knows the difference between the SET and SELECT statement while assigning variables

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i|||

Quote:

Originally Posted by davash6

Hi

declare @.i int
set @.i=1
This s used to assign constant values

select @.i=max(column_name)from table_name
for ex.
select @.i=max(emp_id) from table_emp

This abve stmnt assign the max of the column value to the variable @.i

we can also use select statement to assign constants like
Select @.i=1 from tableEmp|||We can assign more than one value by using Select Statement but in SET we cant
Eg:
Select @.i=Mark1, @.j=Mark2 from Marks where SID=12323

but if we use SET statement We must write two different statement to do that
like

SET @.i=Select Mark1 From Marks where SID=12323
SET @.j=Select Mark2 From Marks where SID=12323

so i think the select statement is fast in this case

anybody have any other comments??? then pls post...|||SELECT:
Just selects data from a table, and does not change the values in the actual table.
so you see a view of the data.

select * from table1
see all data is table1

select * from table1
order by col01
see all data in table1 with the data ordered by col01.
so your view is different to the actual table but the table is unchanged.

SET:
this is used to change values in tables etc.

example 1:
update table1
set col01 = '23' where col01 = '21'
So set all instances of col01 to value '23' where it currently = '21'

example 2:
declare @.variable as int
set @.variable = '123'
create a variable called @.variable and store value '123' in it.

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob