Problem with Bulk Upload. Uploads twice.

When I run the following stored procedure I get these results. My file has
24 rows in it. However I am uploading it twice for some reason.
Stored Procedure:
CREATE PROCEDURE [dbo].[dsp_Manual_Import]
(
@.PathFileName varchar(50),
@.SQL varchar(2000)
)
AS
SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
return
GO
Results:
(24 row(s) affected)
(24 row(s) affected)
Stored Procedure: ILVS.dbo.dsp_Manual_Import
Return Code = 0Have you confirmed the duplicate rows were inserted by running a query
against the table afterward?
Are there any triggers on the table, perhaps it is inserting to an audit
table?
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
> When I run the following stored procedure I get these results. My file
> has
> 24 rows in it. However I am uploading it twice for some reason.
>
> Stored Procedure:
> CREATE PROCEDURE [dbo].[dsp_Manual_Import]
> (
> @.PathFileName varchar(50),
> @.SQL varchar(2000)
> )
> AS
>
> SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
> WITH (FIELDTERMINATOR = ',') "
> EXEC (@.SQL)
>
> return
> GO
> Results:
> (24 row(s) affected)
>
> (24 row(s) affected)
> Stored Procedure: ILVS.dbo.dsp_Manual_Import
> Return Code = 0|||Yes I checked the table and it is importing twice. There are no triggers.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||Another concern is that I only am importing 24 records, and it is reporting
48.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||That's strange. Just as an experiment, try the following and confirm if they
all behave the same way.
#1 Execute dsp_Manual_Import manually from Query Analyzer instead of from
your application.
#2 Execute the same bulk insert command from Query Analyzer instead of
from within the stored procedure.
#3 Execute a similar bulk insert command against a different table.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:F28E3D8A-5386-4173-998C-5865E2866C23@.microsoft.com...
> Another concern is that I only am importing 24 records, and it is
> reporting 48.
> "JT" wrote:
>

Problem with an IIF Statement

In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses

Problem with a query

Hi, I have a problem with SQL Server 2000:

this query, who works on the table "Flussi_Rivendite" which contains more or less 700.000 rows, doesn't work speedly:

SQL = "SELECT SUM(Quantità) AS Quantità " _

& "FROM Flussi_Rivendite WHERE " _

& "DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "ID_AnagraficaRivendita IN " _

& "(SELECT AnagraficaRivendite.ID_AnagraficaRivendita FROM AnagraficaRivendite WHERE " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

The problem is in last part of query:

& "ID_AnagraficaRivendita IN " _

& "(SELECT AnagraficaRivendite.ID_AnagraficaRivendita FROM AnagraficaRivendite WHERE " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

ID_Agente is a foreign key of another table ("AnagraficaRivendite" which contains 60.000 rows)

I have seen I can improve "waiting time" of this query if I add "ID_Agente" field at table Flussi_Rivendite, so I can transform initial query in other query simpler:

SQL = "SELECT SUM(Quantità) AS Quantità " _

& "FROM Flussi_Rivendite WHERE " _

& "DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente")

I would ask you if it is correct to add column ID_Agente on the table Flussi_Rivendite. Until today I have always avoided to do this, I have always filtered my table by query like: "....IN (SELECT FIELDS FROM TABLE WHERE ecc...)".

But now, these tables have many many records and I have found only this solution to improve the query (like I have just said, adding external key "ID_Agente" directly on table "Flussi_Rivendite". Sorry for my english, I hope someone can suggest something me ;)

Hi Maurodii

I like the table names -- some beautiful language!

Did you try:

"SELECT SUM(Flussi_Rivendite .Quantità) AS Quantità " _& "FROM Flussi_Rivendite, AnagraficaRivendite WHERE " _& " Flussi_Rivendite.DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& " Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite. ID_AnagraficaRivendita " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente") & ")"

Hope this helps and Good Luck!

Fouwaaz

|||

IN can be a performance killer. Look into using EXISTS instead of IN.

|||

Hello, I have tried this but the performance is the same, unfortunately :(

SQL = "SELECT SUM(Flussi_Rivendite.Quantita) AS Quantita " _

& "FROM Flussi_Rivendite, AnagraficaRivendite WHERE " _

& "Flussi_Rivendite.DataFlusso BETWEEN 20070101 AND 20071031 AND " _

& "Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite.ID_AnagraficaRivendita AND " _

& "AnagraficaRivendite.ID_Agente=" & dTable_Agenti.Rows(x)("ID_Agente")

Dear ndinakar, may you post me how you suggest me to do with the clause EXISTS? I have tried it, but I didn't make it :(

I remember you that my target is don't preserve column "ID_Agente" in table Flussi_Rivendite.

Thank you

|||

Try running the query directly in Query Analyzer or Management Studio and see how long its taking. Also I would recommend using >= and <= instead of using BETWEEN.

SELECTSUM(Flussi_Rivendite.Quantita)AS Quantita

FROM Flussi_Rivendite, AnagraficaRivendite

WHERE Flussi_Rivendite.DataFlusso>='20070101'AND Flussi_Rivendite.DataFlusso<='20071031'

AND Flussi_Rivendite.ID_AnagraficaRivendita= AnagraficaRivendite.ID_AnagraficaRivendita

AND AnagraficaRivendite.ID_Agente=<somevalue>

|||

I have changed clause Between like you suggested me but performance doesn't improve. Moreover, I have tried to run both query in Query Analyzer, I post here results:

Query N.1

SELECT SUM(Flussi_Rivendite.Quantità) AS Quantità
FROM Flussi_Rivendite, AnagraficaRivendite WHERE
Flussi_Rivendite.DataFlusso>=20070901 AND Flussi_Rivendite.DataFlusso<=20070931 AND
Flussi_Rivendite.ID_AnagraficaRivendita = AnagraficaRivendite.ID_AnagraficaRivendita AND
AnagraficaRivendite.ID_Agente=1

Flussi_Rivendite: Costs: 67% Anagrafica_Rivendite: Costs: 300%

Query N.2

SELECT SUM(Quantità) AS Quantità
FROM Flussi_Rivendite WHERE
DataFlusso BETWEEN 20070901 AND 20070931 AND
ID_Agente=1

Flussi_Rivendite: Costs: 0% Anagrafica_Rivendite: Costs: 0%

In my opinion when I directly filter ID_Agente in the same table (Query n.2) is best solution for me, also if database's structure will be a little more complicated. But it is too faster then Query n.1

|||

Do you have any indexes on DataFlusso column? or on AnagraficaRivendite.ID_Agente column? Having proper indexes is important for faster data retrieval otherwise SQL Server has to scan your entire table to get to the rows and then perform the computation.

|||

IMPORTANT IMPROVEMENT!!!!

I have built a new index like you suggested me in table Flussi_Rivendite: DataFlusso, ID_AnagragraficaRivendite, ID_Agente.

Now waiting time is pull down until 6/7 seconds!!!! It is good, also if with my alternative procedure waiting time it was 3/4 seconds. Thank you again, next monday I will come back in the office and I'll try it better (now I am working from my home by Remote Desktop and it's not easy...).

Have a good week end!!

ps: If you some indication to built/edit better this new index please tell me ;)

|||

Build this index on AnagraficaRivendite : ID_Agente, ID_AnagraficaRivendita

|||

Motley:

Build this index on AnagraficaRivendite : ID_Agente, ID_AnagraficaRivendita

I've already created.

Please note that if I change range of DataFlusso (for es. 20060101 and 20071031) waiting time increases again, but I think it's normal because so I'm asking for more records. I think I need a newer and faster processor... ;) or not?

|||

I think this is best solution if I don't add a column ID_Agente to table Flussi_Rivendite:

SELECT SUM(dbo.Flussi_Rivendite.Quantità) AS Quantità
FROM dbo.Flussi_Rivendite INNER JOIN
dbo.AnagraficaRivendite ON dbo.Flussi_Rivendite.ID_AnagraficaRivendita = dbo.AnagraficaRivendite.ID_AnagraficaRivendita
WHERE (dbo.Flussi_Rivendite.DataFlusso >= 20070101) AND (dbo.Flussi_Rivendite.DataFlusso <= 20070131) AND
(dbo.AnagraficaRivendite.ID_Agente = 2)

Bye

Problem with a date function

Hello All!

I have a table with a date column. I would like to be able to DELETE the rows based on the date column. The condition is 30 days from todays date. So anything older than 30 days from todays date, it will delete those rows.

Any suggesttion the best way to do this. I was thinking of a simple select statement, but can't figure it out.

TIA!!

Rudy

Hi there,

Is your date column of data type DateTime? If so, try something like:

DELETE FROM [Table Name]
WHERE DATEADD(d, -30, GETDATE()) > [Date Column]

What happens is DATEADD(d, -30, GETDATE()) is used to obtain a date that is 30 days from todays date. Then, anything in the table where the entry in the Date Column (which I assume is of data type DateTime) is older than DATEADD(d, -30, GETDATE()), i.e. older than 30 days from today's date, gets deleted.

Hope that helps a bit, but sorry if it doesn't.
|||Thank you! Just what I needed!

Problem with "phantom rows"

Hi,

I am seeing something strange. I have a data file that has 6 rows that looks like:

ABC, "OPENING BALANCE", 1234, etc

ABC, "CLOSING BALANCE", 1235, etc

ABC, garbage data, etc

XYZ, "OPENING BALANCE", 1234, etc

XYZ, "CLOSING BALANCE", 1235, etc

[][] -- weird box things that shows up in my flat file conn mgr

I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.

I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.

However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.

I really don't understand how this is happening. Please, any ideas.

Thanks

A script transformation doesn't block rows. Use a conditional split instead.|||

Hi,

Are you saying to get rid of my script component and use a conditional split instead?

I would have no clue how to do this, though!

If you could show an example syntax, I would appreciate greatly.

Thanks

|||A conditional split component just uses expressions to test rows. If the expression evaluates to true, then the row goes down that output path.

Since you are working with the row as one big column, here's a sample config:
OUTPUT NAME: BalanceRows
Expression: FINDSTRING("OPENING BALANCE",[Column],1) > 0 || FINDSTRING("ENDING BALANCE",[Column],1) > 0

Then, back in the data flow, just grab the green arrow and hook it to the next component in line. It will prompt you for which output you want to use. Select the "BalanceRows" output.|||

Ok, but then how do I break up the line into columns so that I can map them to my table columns?

Use a script component after that?

|||

sadie519590 wrote:

Ok, but then how do I break up the line into columns so that I can map them to my table columns?

Use a script component after that?

Sure. Or use a derived column and use substrings.|||

Could you explain how to set up the derived column? I don't see how you specify the input or output in this case.

Thanks

|||Never mind, I see now|||

Yes, I see how I could do this, if only I could find some DOCUMENTATION on SSIS expressions.

I don't understand why Microsoft creates something that is so specific that only they can provide documentation, but then they don't bother adding any documentation. This is incredibly frustrating.

I don't see one single example of how to use "SUBSTRING", if there even is such a thing, because I can't find ANY information on using string functions in expressions.

Help.

:-(

|||When you click on substring in the list of available functions, it tells you how to use it. I know you're frustrated with this, but it's right there.

And for that matter, it's all in BOOKS ONLINE. The first link returned when SEARCHING for "substring ssis" yielded the page you apparently think is missing.|||

Ok, after a ridiculous amount of searching, I found the reference page I was looking for.

But I REALLY don't see how I can possibly use substring or findstring to parse my row because it requires that you know what you're looking for first, which I don't.

SUBSTRING(character_expression, position, length)

That is, you have to provide the character_expression which I don't have because each row is different.

Is this what you really mean? Because it doesn't look like it's gonna work.

|||

I am just going to use my script, since I can use the index to select which columns I want to use.

I think using an expression to do this would be very complicated, as the values would have to be determined by the number of commas found, or something like that.

Anyways, even before I get to that issue, I am bummed b/c the conditional split isn't working.

This is my expression:

FINDSTRING("OPENING",Column0,1) > 0 || FINDSTRING("CLOSING",Column0,1) > 0

My data viewer shows nothing being sent to the next component after the conditional split.

Any ideas why?

|||character_expression is your row, or column since you are reading in the row as one column.

If it's not a fixed width row and positions change, then use the script and however you were going to do it before.|||

Yes, I think the script is easier.

But any ideas why the conditional split isn't working as expected?

Thanks

|||

Aha, I see you're just keeping me on my toes :-)

It's like this: FINDSTRING("CLOSING",Column0,1) > 0

character expression comes first, then search string

Problem using merge join transformation in Sql server 2005(SSIS)

Hi,

I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table.

The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?

How do i solve tis problem?

Thanks in advance

Sat

Satishr23:

But for some reason the output of the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?

Not exactly. It depends on the logical operation of the merge join, for example "full outer join" may lead to such issue. You can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5alv.asp

problem using default value in SP

I created an SP for searching rows in a table.
Create Proc stp_search
@.recID varchar(100) = '%'
As
Select * from tbl1
Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
The SP works if I pass in only 1 recordID, or if I don't pass in anything
(default val of %). But I want to be able to search for multiple recID's, s
o
I created a UDF that returns a table of recID's. If the UDF returns one
recID in the return table, the SP works fine. But if I pass in a string wit
h
2 or more recID's (separated by commas - thus varchar), I get this error
message:
'Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
The statement has been terminated.'
My goal is to be able to pass in multiple recID's, or just one, or none -
using the default value. My options to achieve this is to have multiple
select statements like this wrapped inside of IF Else:
if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
select * from tbl1 where recID in coalesce(@.recID, recID)
Else
select * from tbl1 where recID in (select * from dbo.udf(@.recID)
The problem is that the actual select statment is quite large and I have 10
parameters. So the IF else thing would be quite verbose (because I am
actually searching on several tables in this SP - one of the params specifie
s
which table).
So my question is if there is a way to implement the functionality of my
UDF which can return more than one value in the subquery (searching on more
than one recID) and still be able to use the default value (with coalesce)
without having to do the If Else thing.
Any suggestions appreciated (I hope I am not stuck with verbose - it would
be a book)
Thanks,
RichSee if this helps.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||I think that my real question should be
Is there a way to implement a form of If Else inside a Where clause?
Declare @.recIDLen int
select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
select * from tbl1 Where
Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
Is there a way to do something like this?
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||try this
select * from tbl1 Where
(@.recIDLen = 0 and recID = coalesce(@.recID, recID)) or
(recID In (select * from dbo.UDF(@.recID))
"Rich" wrote:
> I think that my real question should be
> Is there a way to implement a form of If Else inside a Where clause?
> Declare @.recIDLen int
> select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
> select * from tbl1 Where
> Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
> Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
> Is there a way to do something like this?
>
> "Rich" wrote:
>|||Thanks all for your replies. And Omnibuzz gave me an even better idea. I
will add a varchar parameter which gets the string of recordIDs or default
value of '%'. If this param is '%' then @.recordID int will be used as the
parameter of choice. I was having a problem with the recID column which is
actually an int.
Anyway, this also solved another problem I was having pulling date columns
that contain nulls. I can bypass using a date params with the OR operator.
Thanks again.
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>