Losing my parameters and fields?

Dear MSDN!
I have designed a report in VS 2005 Report Server Projekt. The report
connects to a Analysis 2005 server. Everything works fine in Layout tab and
preview tab until i click data tab. After i have clicked the data tab the
report starts to show errors in the output and error list window:
[rsFieldReference] The Value expression for the textbox
â'Sales_Price_Gross_Incl_Discount_1â' refers to the field
â'Sales_Price_Gross_Incl_Discountâ'. Report item expressions can only refer to
fields within the current data set scope or, if inside an aggregate, the
specified data set scope.
Visual studio have automatically checked out the report and changed the xml,
deleted the parameters and field definitions.
What is the problem?
Thanks in advance!Hello Grundh,
I found some similar issue in our internal database. But I did not found
the solution yet.
I am performing research on this issue and I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

looping parameters collection

Is there any possibility to loop through all the parameters of the parameters
collection in the custom code of a report?
I tried using a "For Each" loop, but aparently that doesn't work.
The goal is to be able to use the parameter name - value pairs,
independently of the number of parameters in the report.Curt, I posted a simular question a few minutes ago ...
"Curt Biernaux" wrote:
> Is there any possibility to loop through all the parameters of the parameters
> collection in the custom code of a report?
> I tried using a "For Each" loop, but aparently that doesn't work.
> The goal is to be able to use the parameter name - value pairs,
> independently of the number of parameters in the report.|||There is no easy way to do it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Curt Biernaux" <Curt Biernaux@.discussions.microsoft.com> wrote in message
news:8182188F-F2D9-4643-A933-2E9E4E69E6F7@.microsoft.com...
> Is there any possibility to loop through all the parameters of the
> parameters
> collection in the custom code of a report?
> I tried using a "For Each" loop, but aparently that doesn't work.
> The goal is to be able to use the parameter name - value pairs,
> independently of the number of parameters in the report.|||Even if there would be a hard way, there is still a way to solve this problem.
Can you tell me more about it?
"Lev Semenets [MSFT]" wrote:
> There is no easy way to do it.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Curt Biernaux" <Curt Biernaux@.discussions.microsoft.com> wrote in message
> news:8182188F-F2D9-4643-A933-2E9E4E69E6F7@.microsoft.com...
> > Is there any possibility to loop through all the parameters of the
> > parameters
> > collection in the custom code of a report?
> > I tried using a "For Each" loop, but aparently that doesn't work.
> > The goal is to be able to use the parameter name - value pairs,
> > independently of the number of parameters in the report.
>
>

LOOKUP: specifying parameters for the SQL Query

guys i'm trying to use a Lookup in a dataflow that looksup stuff in the results of a query.

Problem I have is that the query needs to take two parameters.. (Source

and BaseCurrency in the code below) and i can't figure out how to

supply the parameters..

Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...

PJ

SELECT ForeignCurrency, RateFromFile AS YesterdaysRate

FROM inputrates IR

WHERE fileheaderid in (

SELECT top 1 MAX(ID)

FROM FileInputAttempts FIA

WHERE Source = '?'

AND FIA.BaseCurrency = '?'

AND status = 'SUCCESS'

Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)

order by MAX(loaddate) DESC

)Some more info...

When i click on the advanced tab... and select "enable memory

restriction" I can then access the SQL statement by selecting "Modify

the SQL statement" ... then I can also click the "Parameters" button...

But when I DO click Parameters.. I get "Syntax error, permission violation, or other nonspecific error"....

I have checked the query in query analyser, run it to get good results etc...

The Query when it goes into the "Modify SQL statement" box looks like this:

select * from

(SELECT ForeignCurrency, RateFromFile AS YesterdaysRate

FROM inputrates IR

WHERE fileheaderid in (

-- This gives us the last days rates...

SELECT top 1 MAX(ID)

FROM FileInputAttempts FIA

WHERE Source = '?'

AND FIA.BaseCurrency = '?'

AND status = 'SUCCESS'

Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)

order by MAX(loaddate) DESC

)) as refTable

where [refTable].[ForeignCurrency] = ?
|||Ahh Haahh!!!

i removed the comments from inside the query and , the parameters box popped up.

but it only contains one parameter...

Looks like its not finding the ?'s at all...

I dunno...|||Took away the apostrphes around the ?'s and got the message:

"Parameter Information cannot be derived from SQL statements with

sub-select queries. Set parameter information before preparing command."

Okay.. so how do I set the parameter information?|||Hi,
To my understanding, Lookup doesn't support dynamic SQL query by using parameters like other transformations can do.

The SQL query you specify will be called only once to fill the cache if you use Full cache mode. That parameter setting SQL is only for Partial cache mode, in this mode, Lookup will call this SQL for each row if not already in cache.

For example, if you have Customer_Name in input stream, you want lookup Customer_ID from Customer table, you either use:

SELECT Customer_Name, Customer_ID FROM Customer

Or:

SELECT * FROM
(SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]) as refTable
WHERE [reftable].[Customer_Name] = ?

(Then set Parameter1 to Customer_Name. If you turn on SQL Profiler, you'll see lots of query like above, only the ? was replaced with real data, e.g. 'Tom', 'Jery', ... After a certain time, when all value have cached, you no longer see this query).

If you want to use runtime value to limit the Lookup cache size, i.e. kind of dynamic SQL, see my workaround blog post here.

Hope this helps.

(p.s. Lots of questions have been asked for Lookup, maybe it's time for someone within SSIS team to re-document it for us, about what it can do, what can't, how it works, etc. )|||

Hmm, thats pretty limited so...

I'll have to try a different approach...

Actually have to say that lookup is , in this case, not very efficient...

I get the idea that the SQL looks like this...

SELECT * FROM
(SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]) as refTable
WHERE [reftable].[Customer_Name] = ?

But the inner SQL there:

SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]

loads every record in a table...

And that's fine if you have a small table.. but I have a huge table from which I want to extract a window of say 20 records... And the only way I have of limiting the records that come from the table is the ability to specify what day (or what currency) these records relate to...

If Lookup insists on loading the whole table into a cache and then selecting from that data... well ... it could be caching a half million records in my case.

That can't be good..

So Lookup seems really limited... only able to perform lookup on small tables.. (well if you want things any way efficient)...

PJ

PJ

|||

PJ,

You're absolutely right. There's loads of stuff up on Microsoft Connect regarding the inferiority of the LOOKUP transform. I'd appreciate you going through and voting/adding your comments.

-Jamie

|||

Whoops, how did this end up in its own thread?

I intended it as part of the thread :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=562631&SiteID=1

Can anyone move it?

PJ

|||

[Done!]

|||

Thanks,

I'll have a run over the connect stuff as soon as I get some time...

PJ

|||HI,

There is some mis-understanding here.

The SQL query doesn't work in two steps, in fact the whole query will be sent to server and suppose to only fetch one row.

Also caching half-million rows in memory isn't that bad. Say if you only need to lookup an ID from a Name, that's two columns to query. e.g. 10 bytes for Name, 4 bytes for ID, 14 * 500,000 = 7MB, that's even ok for my laptop.

The problem comes when you know that the lookup only involve a subset of the table, then it's meaningless to cache the rest of data.

Looking for tutorials/walkthroughts on Report parameters

Could someone please point me to a tutorial on how to implement complex
parameters in Reporting Services reports? I ask this because the online help
available seems to be extremely minimal.
What I will probably be doing is writing reports with multiple parameters
including single values, dropdown lists, and calendars. Also I probably will
need to hide/expose/change-value-of some parameters based on the selection of
others.
Any sort of info or even a recognizable starting point to research this
would be of help. Thanks!Sorry. False alarm. Please cancel.
(I should say I found the help for the Report Parameters editing screen
minimal. Finally Googled and found the Technet tutorial on cascading
parameters which is roughly what I was looking for.
However this is all academic because I've just been taken off the project.)
"B. Chernick" wrote:
> Could someone please point me to a tutorial on how to implement complex
> parameters in Reporting Services reports? I ask this because the online help
> available seems to be extremely minimal.
> What I will probably be doing is writing reports with multiple parameters
> including single values, dropdown lists, and calendars. Also I probably will
> need to hide/expose/change-value-of some parameters based on the selection of
> others.
> Any sort of info or even a recognizable starting point to research this
> would be of help. Thanks!

Looking for table design pattern for different value types

Hi,

I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.

However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.

Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.

Options explored so far:

1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.

Your help in this matter will be appreciated.
Regards, Tonn

Tonn:

Can you take advantage of the SQL_VARIANT data type? Something like:

drop table dbo.parameter
go

create table dbo.parameter
( parmName varchar (40),
parmDescription varchar (80),
parmType tinyint,
parmValue sql_variant
)
go

insert into parameter values ('Integer Parm', 'Just an integer parameter', 1, 1)
insert into parameter values ('Numeric (9,2) parm', 'Yeah', 2, cast (17.50 as numeric (9,2)))
insert into parameter values ('Varchar parm', 'A varchar parm', 3, 'Yes, a varchar')
select * from parameter

-- Sample Output:

-- Warning: The table 'parameter' has been created but its maximum row size (8164) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

-- parmName parmDescription parmType parmValue
-- -- -- --
-- Integer Parm Just an integer parameter 1 1
-- Numeric (9,2) parm Yeah 2 17.50
-- Varchar parm A varchar parm 3 Yes, a varchar

|||

Hi Waldrop,

Thanks for your reply.
It most certainly is an interesting suggestion. It's like option no.1 except that the serialization has been moved to the database.

Is this the preferred way of handling this type of problem? We're in the financial market and this is a common problem for us, but there seems to be no documentation, best practices etc. available so our individual programmers tend to insist on their personal preference.

|||

I think that your purpose is the type of application that the SQL_VARIANT datatype is intended to address. It would be helpful if you could provide some sample update or select statements where you might use your proposed column. I don't think I would want to have a column for each different potential datatype. Below is an example of how you might load the data into variables. Note the need to CAST the data:

declare @.numericVar numeric (9,2)
declare @.varcharVar varchar (80)
declare @.intVar integer

set @.numericVar = (select cast (parmValue as numeric (9,2)) from parameter where parmName = 'Numeric (9,2) parm')
set @.varcharVar = (select cast (parmValue as varchar (80)) from parameter where parmName = 'Varchar parm')
set @.intVar = (select cast (parmValue as integer) from parameter where parmName = 'Integer Parm')

select @.numericVar as [@.numericVar],
@.varcharVar as [@.varcharVar],
@.intVar as [@.intVar]

-- S A M P L E O U T P U T :

-- @.numericVar @.varcharVar @.intVar
-- -- --
-- 17.50 Yes, a varchar 1

Dave

|||

Hi Dave,
I think you've already made your point and put your suggestion at the top of the list.

It's no use sending an example since every proposed alternative so far works. I'm actually looking for some form of authority to stop spending all those man-hours debating about it.
In the past I've had success regarding programming issues by referencing Gamma's "Design Patterns". I was hoping to do the same for database issues, but I found out that it's not going to be that easy.

Looking for SQL-Server ODBC parameters

I've been browsing all over the net, through technet and MSDN as well, all to no avail.

I'm looking for the parameters I can use with SQL-Server drivers of MDAC 2.7 when using dbcconfig in scripts.

E.g. with mysql you can do a line "DSN=Test;Description=Test Odbc DSN;Server=Test_server;user=stars"

Using odbcconf configsysdsn "SQL Server" "DSN=Test DSN;Description=For stars;User=Stars" I get the error "Unable to create a data source name for the "sql-server" driver, drivers configdsn, config driver or configtranslator failed.

If i remove everything but the DSN name, it works, so I must be getting the parameters wrong. But exactly where, I'm not sure..

Thanks for your help!

FlixFirst of all, you should consider to use the native SQL Server OLE DB provider, instead of the ODBC provider. Microsoft declared this provider already obsolete.

Secondly, you reporting two different things: Your MySQL line specifies the ConnectionString as required for the ODBC OLE DB provider. Your SQL Server line, however, does the same, but you are trying to mix that with ODBCConfig.

So, I'm not sure what you want to achieve:

1) specify a DSN-based connection string
2) dynamically define a DSN
3) specify a DSN-less connection string

For 1) and 3) look here (www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForODBCDatabases)|||The old ODBC providor is merely contained to an older system not yet phased out. Based on a SQL-Server 6.5. As all programming ressources on that system has died out, its merely a question of time before an entirely different system is set in place.

My problem (literally) is to keep the system alive as is, and in this case it is to define a DSN through loginscripts using ODBCconfig, as I would otherwise have to churn around with 150 machines creating a host of different DSN's.

In my testing case I am able to give a MySQL DSN more specific arguments using ODBCconfig as opposed to the SQL-Server DSN. Namely which user the DSN should use, and what DB to access... and I can't see the sense in that, as I need to specify user and DB as you would normally do using your ODBC administrating tool.

Hope this clarify's my situation just a whee bit.

Thanks,

Flix|||Okay, if I understand you right, your primary problem isn't to generate a DSN, but to connect via ODBC? Why don't you follow my point 3): use an DSN-less ODBC connection.|||That would require a recoding of this ancient Access 95/97 and SQL 6.5 based system, which is practically like sticking your hand into a wasp-nest. Or do I read the stuff wrong?

Its the database front-end that needs the system DSN's, and in the database there are hordes of linked tables, queries and code all built up against the presence of the system DSN's.

Recoding of the app is pretty much out of the question, as it is destined to be replaced within 6 - 12 months.. so I just need a way to distribute the system DSN to our workstations, in which case I chose odbcconf as it could be called from the login-script.

Cheers, Flix|||Okay, this (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q171/1/46.ASP&NoWebContent=1) should help you.

Looking for guidence on Parameters.

I am trying to procduce a report using an " IN (x,y,z) "clause except that
x,y,z could contain 1 or 10 values, is it possible to change the base query
at run time or use a single parameter array to convert the values?
Any help and guuidence appriciated.Richard,
Maybe a filter might be an option and wite some VB.NET code to use instr
command to handle the variable values ?
- peteZ
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>I am trying to procduce a report using an " IN (x,y,z) "clause except that
>x,y,z could contain 1 or 10 values, is it possible to change the base query
>at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||Your report query can be expression-based. You can pass the whole IN clouse
(x, y, x) as a report parameter but be careful about SQL injection attacks.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I am trying to procduce a report using an " IN (x,y,z) "clause except that
> x,y,z could contain 1 or 10 values, is it possible to change the base
query
> at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||No i've tried to look at what you are saying but cant see how you would
implement it
Do you put a parameter in the SQL code, or is it a filer? a simple example
would be really helpful.
Thanks
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> Your report query can be expression-based. You can pass the whole IN
> clouse
> (x, y, x) as a report parameter but be careful about SQL injection
> attacks.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>> I am trying to procduce a report using an " IN (x,y,z) "clause except
>> that
>> x,y,z could contain 1 or 10 values, is it possible to change the base
> query
>> at run time or use a single parameter array to convert the values?
>> Any help and guuidence appriciated.
>>
>|||Hi,
What attached report definition are you referring to? I am also having to
pass multivalues to a parameter and I would like to use in(x,y).
Thanks
"Teo Lachev [MVP]" wrote:
> Namshub,
> Please look at the attached report definition which demostrates an
> expression-based SQL statement that takes comma-separated list of employee
> identifiers passed as a report parameter. It produces a report that shows
> the purchase orders associated with a single or mutiple employees.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:uSEkVxdtEHA.1368@.TK2MSFTNGP15.phx.gbl...
> > No i've tried to look at what you are saying but cant see how you would
> > implement it
> >
> > Do you put a parameter in the SQL code, or is it a filer? a simple
> example
> > would be really helpful.
> >
> > Thanks
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> > > Your report query can be expression-based. You can pass the whole IN
> > > clouse
> > > (x, y, x) as a report parameter but be careful about SQL injection
> > > attacks.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in
> message
> > > news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > >> I am trying to procduce a report using an " IN (x,y,z) "clause except
> > >> that
> > >> x,y,z could contain 1 or 10 values, is it possible to change the base
> > > query
> > >> at run time or use a single parameter array to convert the values?
> > >>
> > >> Any help and guuidence appriciated.
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>|||Hi,
Hi,
What you could do is create a function on the SQL server that will
convert your comma separated list to a table.
Like this:
CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
(
@.CommaSeparatedList VARCHAR(8000)
)
RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
AS
BEGIN
DECLARE @.TableValue VARCHAR(1000)
DECLARE @.Pos INT
SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
IF @.TableValue <> ''
BEGIN
INSERT INTO @.ParsedTableValue (TableValue)
VALUES (RTRIM(@.TableValue))
END
SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
LEN(@.CommaSeparatedList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
END
END
RETURN
END
Then use this function in your query or stored procedure like this:
SELECT a.country, ..., ..., ...
FROM table1 a
JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
RTRIM(b.TableValue) = a.country
HTH,
Eric|||Hi,
Thanks alot for the help.
Now I need to modify the function. I tried but was unsuccessful.
What I need is to be able to pass null values so if the user doesn't select
a value all the employee names will bel returned.
Also I need multiple where statements. I tried multiple inner joins but
obviously it returns the error:
The correlation name 'b' is specified multiple times in a FROM clause
How can I fix the problems?
Thanks
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||I solved my problem for null values and for multiple statements in the where
clause as follows:
WHERE (@.param is null or userid in (Select tablevalue from
dbo.UDF_CharCommaSeparatedListToTable (@.param)))
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||Hi,
To fix the null values issues maybe you could try someting like this:
*** It is not going to work if the parameter @.EmployeeList is null...
maybe en empty string could represent all possible values...
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.EmployeeList) b
ON 1 =1
WHERE a.EmployeeName = CASE WHEN RTRIM(@.EmployeeList) = '' THEN
a.EmployeeName
ELSE RTRIM(b.TableValue)
END
For the correlation name issue, you can change the alias for every join
you make to the function:
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList1)
b ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList2)
c ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList3)
d ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList4)
e ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList5)
f ON ...
HTH,
Eric|||Hi,
Your way of managing null values is better than mine, I'll be using
that in the future!!
Eric|||Ouups,
After giving it a little more thought, I will stick to my way of
managing null (empty) values since your way of managing nulls would
require the function to be called for every records of your resultset
and my way will be calling the function only once...
Regards,
Eric