Difference between SP and function

Hi All,

It may be sound weird. I want to find the difference between SQL Stored procedure and functions. I knew a couple one is for function, parameter is must where as in SP its not, second is function would return a value whereas procedure wont.

Is there anythign else?That was it! You are on the right track :)|||>> second is function would return a value whereas procedure wont.

not necessarily. even a stored proc can return values ( see OUTPUT PArameters).

Function can return only ONE value where as a stored proc can return multiple values.
performance wise there isnt any diff.

I remember having googled about this once and did come across a couple of articles that xtensively described the differences..so I would say google and you can find more definitive answers.

hth|||I guess "parameter is must where as in SP " he ment for OUTPUT param!|||one use of a function is to use it to return a table object that can be used in another sql statement|||Hi

Thanks you all for your answers. But in the recent inteview which i attended they asked for one more difference between these two apart from those i specified earlier.|||I'm sure you could Google and find all of the information you need.

The main thing about UDFs is that they need to be deterministic -- that is, the same input parameters will always return the same result. So therefore you cannot, for example, directly use GETDATE() in your UDF. Another biggie is that you cannot use either @.@.ERROR or RAISERROR. And another biggie is that dynamic SQL cannot be executed.

I've gotten burnt by all of the above, and others. Some have workarounds, some do not.

Terri

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