Proper use of IF statement in stored procedure?

I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:

Cannot use empty object or column names. Use a single space if necessary.

So, what's the correct way of doing the following?

IF @.filename <> ""
BEGIN
UPDATE Experimental_Images SET contentType = @.contentType, filename = @.filename WHERE (id = @.iconNo)
ENDThe problem you are having is that you are using double quotes when youshould be using single quites. The rest of your statements are fine.

What do you mean exactly by "if the parameter is empty". If youmean if it contains an empty string or a NULL, try it like this:

IF ISNULL(@.filename,'') <> ''

If you mean it contains a NULL, try this:

IF @.filename IS NOT NULL

And if you mean it contains an empty string, try it like this:

IF @.filename <> ''

Programmatically changing a DataReader's SQL statement

Hi,

I was wondering how I could dynamically change the SQL statement (ie the SQLCommand property) of a DataReader source.

The source is reading an IBM AS400 file, with the SQL statement:
SELECT itrloc, itrdat, itrcen, csreg#, cstrn#, csseq#, inumbr, cstime, itrqty, itrret, itrreg, itrcst, idept, isdept, iclas, isclas, csdosp, cmcust, csacct, salit, cstntp, csldgr
FROM clib.scsaltrn
WHERE itrdat between 50901 and 50931

I want to programmatically change the date figures on the end (50901 and 50931) each time the package is run.

I have tried to use variables in place of the date figures but this didn't work. I've also thought of using the Script task to programmatically modify the DataReader source, but this doesn't seem possible.

Any ideas on how I could achieve what I'm trying to do?Switch back to the control-flow, select the data-flow in which your datareader source resides and press F4. You should be able to see, in the "Misc" section of the properties pane, a property called:
[<DatareaderSourceComponentName>].SQLCommand
This refers to the thing you want to change.

You can set an expression on this property to set it to whatever you want.

If you need any help in doing that, just reply here!!

-Jamie|||

Jamie,

i set an expression on the SQL script to run it works fine on the first iteration (of a for loop), second time arround it throws an error

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "membernumber" (1304) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "effectivedate" (1307) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount" (1310) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount2" (1313) does not have a matching source column in the DataReader schema.

Error: 0xC004706B at Run Extract, DTS.Pipeline: "component "Run SQL On SPAN" (147)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

here it's refering to column names that was in the first SQL statement.

is there a way to reset the settings of the components on every execution?

|||

If you are using a component in a loop, the metadata of the component must exactly match the metadata of the external sources for each iteration. If you external metadata is different, you will need a different DataReaderSrc component.

Thanks
Mark

|||

Mark,

what i'm trying to do is, i have a table which have two fields,

1. [sql script]

2. [destination table]

have about 25 records.

basically i got the table into a result set (ADO variable), then trying to iterate through the recordset.

here each sql script is different from one another (different meta data), and each script has a corresponding destination table (I used a OLE DB destination, set it as a variable)

so how can i tackle this problem if you have different meta data in source(Note: source field names are identical to destination fields)

my source database, data provider is ODBC and i cannot use OLE DB source. (and i have another iteration to go through many databases which works fine)

any solution to this problem...?

please help!!?

cheers

AJ

|||How do I do this ?|||

Andrew wrote:

How do I do this ?

Instructions are above.

Programmatically changing a DataReader's SQL statement

Hi,

I was wondering how I could dynamically change the SQL statement (ie the SQLCommand property) of a DataReader source.

The source is reading an IBM AS400 file, with the SQL statement:
SELECT itrloc, itrdat, itrcen, csreg#, cstrn#, csseq#, inumbr, cstime, itrqty, itrret, itrreg, itrcst, idept, isdept, iclas, isclas, csdosp, cmcust, csacct, salit, cstntp, csldgr
FROM clib.scsaltrn
WHERE itrdat between 50901 and 50931

I want to programmatically change the date figures on the end (50901 and 50931) each time the package is run.

I have tried to use variables in place of the date figures but this didn't work. I've also thought of using the Script task to programmatically modify the DataReader source, but this doesn't seem possible.

Any ideas on how I could achieve what I'm trying to do?Switch back to the control-flow, select the data-flow in which your datareader source resides and press F4. You should be able to see, in the "Misc" section of the properties pane, a property called:
[<DatareaderSourceComponentName>].SQLCommand
This refers to the thing you want to change.

You can set an expression on this property to set it to whatever you want.

If you need any help in doing that, just reply here!!

-Jamie|||

Jamie,

i set an expression on the SQL script to run it works fine on the first iteration (of a for loop), second time arround it throws an error

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "membernumber" (1304) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "effectivedate" (1307) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount" (1310) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount2" (1313) does not have a matching source column in the DataReader schema.

Error: 0xC004706B at Run Extract, DTS.Pipeline: "component "Run SQL On SPAN" (147)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

here it's refering to column names that was in the first SQL statement.

is there a way to reset the settings of the components on every execution?

|||

If you are using a component in a loop, the metadata of the component must exactly match the metadata of the external sources for each iteration. If you external metadata is different, you will need a different DataReaderSrc component.

Thanks
Mark

|||

Mark,

what i'm trying to do is, i have a table which have two fields,

1. [sql script]

2. [destination table]

have about 25 records.

basically i got the table into a result set (ADO variable), then trying to iterate through the recordset.

here each sql script is different from one another (different meta data), and each script has a corresponding destination table (I used a OLE DB destination, set it as a variable)

so how can i tackle this problem if you have different meta data in source(Note: source field names are identical to destination fields)

my source database, data provider is ODBC and i cannot use OLE DB source. (and i have another iteration to go through many databases which works fine)

any solution to this problem...?

please help!!?

cheers

AJ

|||How do I do this ?|||

Andrew wrote:

How do I do this ?

Instructions are above.

Programmatically changing a DataReader's SQL statement

Hi,

I was wondering how I could dynamically change the SQL statement (ie the SQLCommand property) of a DataReader source.

The source is reading an IBM AS400 file, with the SQL statement:
SELECT itrloc, itrdat, itrcen, csreg#, cstrn#, csseq#, inumbr, cstime, itrqty, itrret, itrreg, itrcst, idept, isdept, iclas, isclas, csdosp, cmcust, csacct, salit, cstntp, csldgr
FROM clib.scsaltrn
WHERE itrdat between 50901 and 50931

I want to programmatically change the date figures on the end (50901 and 50931) each time the package is run.

I have tried to use variables in place of the date figures but this didn't work. I've also thought of using the Script task to programmatically modify the DataReader source, but this doesn't seem possible.

Any ideas on how I could achieve what I'm trying to do?Switch back to the control-flow, select the data-flow in which your datareader source resides and press F4. You should be able to see, in the "Misc" section of the properties pane, a property called:
[<DatareaderSourceComponentName>].SQLCommand
This refers to the thing you want to change.

You can set an expression on this property to set it to whatever you want.

If you need any help in doing that, just reply here!!

-Jamie|||

Jamie,

i set an expression on the SQL script to run it works fine on the first iteration (of a for loop), second time arround it throws an error

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "membernumber" (1304) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "effectivedate" (1307) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount" (1310) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount2" (1313) does not have a matching source column in the DataReader schema.

Error: 0xC004706B at Run Extract, DTS.Pipeline: "component "Run SQL On SPAN" (147)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

here it's refering to column names that was in the first SQL statement.

is there a way to reset the settings of the components on every execution?

|||

If you are using a component in a loop, the metadata of the component must exactly match the metadata of the external sources for each iteration. If you external metadata is different, you will need a different DataReaderSrc component.

Thanks
Mark

|||

Mark,

what i'm trying to do is, i have a table which have two fields,

1. [sql script]

2. [destination table]

have about 25 records.

basically i got the table into a result set (ADO variable), then trying to iterate through the recordset.

here each sql script is different from one another (different meta data), and each script has a corresponding destination table (I used a OLE DB destination, set it as a variable)

so how can i tackle this problem if you have different meta data in source(Note: source field names are identical to destination fields)

my source database, data provider is ODBC and i cannot use OLE DB source. (and i have another iteration to go through many databases which works fine)

any solution to this problem...?

please help!!?

cheers

AJ

|||How do I do this ?|||

Andrew wrote:

How do I do this ?

Instructions are above.

programatically obtain primary keys

Given a table, is there a way to look into the systables and obtain the primary keys of that table via a select statement?

Thanks,

Phil

I got it nevermind.

select s.name as TABLE_SCHEMA, t.name as TABLE_NAME

, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

programatically change sqldatasource select statement

Hi Everyone,

I am trying to change the select statement of an sqldatasource if a check box is checked.

I am using theSqlDataSourceSelectingEventArgs but i can't get it to work, anyone got any pointers?

Code Behind

ProtectedSub LocMan_Searching(ByVal senderAsObject,ByVal eAs SqlDataSourceSelectingEventArgs)Handles LocManSearch.Selecting

If cb_Today.Checked =TrueThen

LocManSearch.SelectCommand ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &"+ '%') AND [available] LIKE '%' + " &Date.Today &"+ '%')"

Else : LocManSearch.SelectCommand ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &" + '%')"

EndIf

EndSub

My SQLDATSOURCE

<asp:SqlDataSourceID="LocManSearch"runat="server"ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>">

<SelectParameters>

<asp:ControlParameterControlID="dd_Area"Name="area"PropertyName="SelectedValue"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

Thanks in advance

Chris

I have always just used a string for my SQL statement assigned to a variable and just changed what the variable is assigned to, such as:

If checkbox.checked = true

SQLstr = "Select *..."

Else

SQLstr = "Select Column1..."

End if

|||

Thanks for the reply,

How then do i pass the string to my sqldatasource as the select command?

Chris

|||

Hi Chris,

Thanks again for the reply. I have made the change you suggested and moved the sub to the page load event handler which made it work. Problem is i get SQL errors.. Can anyone see where my select might be wrong?

Cheers

Chris

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim SQLstrAsString

If cb_Today.Checked =TrueThen

SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &"+ '%') AND [available] LIKE '%' + " &Date.Today &"+ '%')"

Else : SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() &" + '%')"

EndIf

LocManSearch.SelectCommand = SQLstr

EndSub

|||

Besides the fact that you suffer from possibly getting SQL Injection attacks because you are using sql string concatenation instead of either parameterized queries or encoded strings, here is your problem:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim SQLstrAsString

If cb_Today.Checked =TrueThen

SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() &"%') AND [available] LIKE '%" &Date.Today &"%')"

Else : SQLstr ="SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%" & dd_Area.SelectedValue.ToString() &"%')"

EndIf