Parse to first non-zero

Hello,
If I have a field that contains varchar type data which looks like '00312'
How can I parse the field to just display '312' ?
Thanks!
Patricedeclare @.i varchar(5)
select @.i ='00123'
select convert(int,@.i)
Convert it to an int
http://sqlservercode.blogspot.com/
"Patrice" wrote:

> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Patrice,
One way...try:
SELECT CAST(<COLUMN> AS INT) FROM <TABLE>
HTH
Jerry
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||Another way, which is a bit safer, though not tremendously:
declare @.char10 char(10)
set @.char10 = '0000001234'
select substring(@.char10, patindex('%[^0a-z]%',@.char10), len(@.char10))
I just look for the first non zero (and I tossed in letter just for kicks)
character and substring from there. No conversion needed, so if you need to
look for
'00000012BC'
it will return:
12BC
instead of crashing out.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:4B70A8AF-6828-4C30-9AE0-7B2DD3E8FF37@.microsoft.com...
> Hello,
> If I have a field that contains varchar type data which looks like '00312'
> How can I parse the field to just display '312' ?
> Thanks!
> Patrice|||In a tiered architecture, display is always done in the front end and
NEVER in the database. This more fundamental that just SQL; don't you
remember this from your first Software Engineering course?
The stinking dirty kludge is to CAST() the string to INTEGER. But
since you did not know that a column is not anything like field, you
probably have serious design problems in many place where you have
string and other data types.|||So to quote Bobby Boucher's mamma in the Waterboy, "CAST IS THE DEVIL!!!"
:-)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>|||That was Kathy Bates! I had forgotten that Movie!
But I do remember my wife and I going to some movie years ago (forgot
the title) where George Cluney as an Army Officier and Nicole Kidman as
an atomic scientist trying to find an atomic bomb. When Kidman came on
the screen we both looked at each other and said "That [atomic bomb
expert] should have been Kathy Bates!"
But getting back from the flashback, the problem with CAST() is that
host languages do not all agree on how to handle SQL data types. Doing
the CAST() in SQL to feed it to an unknown host language conversion
adds overhead and makes results lesspredictable when someone looks at
the schema to figure out what happened. A version of the copy of a
copy of a copy.. problem.|||Yeah, I don't know why didn't think to say this. I got more interested in
just doing something other than cast. The word "display" should have jogged
something in my brain.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129761421.858642.3380@.g47g2000cwa.googlegroups.com...
> In a tiered architecture, display is always done in the front end and
> NEVER in the database. This more fundamental that just SQL; don't you
> remember this from your first Software Engineering course?
> The stinking dirty kludge is to CAST() the string to INTEGER. But
> since you did not know that a column is not anything like field, you
> probably have serious design problems in many place where you have
> string and other data types.
>

Parent ID in a Slowly Changing Dimension

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

Jeroen Alblas wrote:

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish

|||

Why would your Id column change if you have a type2 change?

Are you using a typ2 column to lookup your parentID?

|||

Okay, let me try to give an example:

Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999

Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999

If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...

Thnx again!
Jeroen

|||

I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.

Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning

|||Why don't you normalize that table?|||

Phil wrote:

Why don't you normalize that table?

Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...

Rafael wrote:

You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

Jeroen

|||

Jeroen Alblas wrote:

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:

“No changes there so Doopie's record remains unchanged.”

So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.

In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.

Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.

|||

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".

Thnx, Jeroen

|||

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999

After the change the data would look like this:

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999

|||

Jeroen Alblas wrote:

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.

Indeed, I would not consider it an attribute of the child row.

Jeroen Alblas wrote:

And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Jeroen Alblas wrote:

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

That is certanly not practical...

Jeroen Alblas wrote:

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

That was pretty much my original sugestion...good luck with that

|||

David Frommer wrote:

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)

Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.

Jeroen

|||

Rafael Salas wrote:

You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.

Jeroen

Parent ID in a Slowly Changing Dimension

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

Jeroen Alblas wrote:

Hi There,

Just wondering if any of you implemented a (Kimball type 2) dimension structure, in which a ParentID column exists which points to a record from the same dimension table, using a SCD objects in SSIS. The ParentID column would have to be "Historical".

The challange here is that you would need to go through the table twice somehow, because if I would do a lookup of the parent record in the first run, I wouldn't be sure if I got the right parent record.

Thnx, Jeroen.

I don't understand the problem or question. I think if you define ParentID as 'historical' the SCD component will issue a new 'version' of the rows every time it detects a change in parentID value and it will 'expire' the previous 'version'. It’s just simple like that. It may be helpful is you provide an example of what you are trying to accomplish

|||

Why would your Id column change if you have a type2 change?

Are you using a typ2 column to lookup your parentID?

|||

Okay, let me try to give an example:

Lets say we have an employee named Doopie and his boss named Barkie, both in the dim_Employee table. In our case we work with start- and enddates.

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 12/31/9999
20 Barkie NULL 4000 1/1/1900 12/31/9999

Okay, now, in the source system, Barkie gives himself a raise of 500. We want to track history of Salary. Now say we start the Data flow task with the Slowly changing dimension object. It could very well be, that Doopie's record is processed first. No changes there so Doopie's record remains unchanged. Now Barkie record which introduces a new row:

ID Name ParentID Salary Stardate EndDate
10 Doopie 20 1000 1/1/1900 31/12/9999
20 Barkie NULL 4000 1/1/1900 12/7/2006
21 Barkie NULL 4500 12/8/2006 31/12/9999

If Doopie's record was processed after the insert of Barkie's record, the lookup would return record 21 as the active Boss record, and introduce a new record for Doopie as well...

Thnx again!
Jeroen

|||

I see the problem now. Basically you need to make sure every child record point to the latest version of its parent.

Well, I don't see an easy way to do this at one pass. You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent. That could be safer in the case that an unchanged child row is not in your set of rows to be processed (assuming you are pulling only changed/new rows from the source). That is my best shot considering that I have not drunk my first coffee this morning

|||Why don't you normalize that table?|||

Phil wrote:

Why don't you normalize that table?

Hi Phil, I normalized some columns of it (name of parent), but the parent / child hierarchy goes about six or seven levels. So then I would have to add quite a few columns...

Rafael wrote:

You may try to do an update after the dataflow with the SCD2 finish that assures every row is pointing to the latest version row of its parent.

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

Jeroen

|||

Jeroen Alblas wrote:

I can't just do an update, because the ParentID is defined as type 2 (historical) and should result in a new row for the child record as well...

So let's define 'historical'. Defining a column as 'historical' to me is intended to track changes on a particular attribute. In the example you gave at the beginning you said that no changes were made to the child row:

“No changes there so Doopie's record remains unchanged.”

So, why do you want to issue a new version of the child row? The only change that occurred was to the parent; hence you should generate a new row to the parent, expire the existing (parent)one and update all its children to point to new parent ID. After all, there is not change to track when you look at the child record.

In your example, I would issue a new row for Doopie only if I detected that Barkie is not anymore his manager; that way I would be tracking historical changes of the managers.

Under your approach, have you figured out what would happened if a change occurs to the topmost manager? You would have to generate a new row for the whole organization…kind of messy to me. BTW, the only way I see you could implement this is through recursive queries…if you are using SQL Server 2005 you could use Common table expressions for that.

|||

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record. And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

Still, I think I must conclude that the dimensional modelling technique, at least to my knowledge, does not provide a good method of combining "tracking history" in combination with "recursive relationships".

Thnx, Jeroen

|||

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)
If you have a single source for your employees, you can use that id, otherwise you have to generate one. Then, you
use this key for the relationship like so

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 12/31/9999
20 200 Barkie NULL 4000 1/1/1900 12/31/9999

After the change the data would look like this:

ID EmployeeID Name ParentID Salary Stardate EndDate
10 100 Doopie 200 1000 1/1/1900 31/12/9999
20 200 Barkie NULL 4000 1/1/1900 12/7/2006
21 200 Barkie NULL 4500 12/8/2006 31/12/9999

|||

Jeroen Alblas wrote:

Indeed no changes are made to the attributes of the child record, unless you consider the ParentID column an attribute of the child record.

Indeed, I would not consider it an attribute of the child row.

Jeroen Alblas wrote:

And updating it will solve it for the current point in time, but as you can see in the example, yit would seem Doopie's parent would always have had a salary of 4500...

I disagree. It does not seem like Doopie's parent always have had a salary of 4500; what it actually seems is the current salary of her parent is 4500. You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Jeroen Alblas wrote:

But I agree; if the topmost record would change I would get the whole tree inserted again ... Indeed that's not what I want.

That is certanly not practical...

Jeroen Alblas wrote:

So, I guess I will add the parent columns which must lead to a new child record (Firstname, Lastname, Login, etc) to the child record and update the ParentID to point to the active record of the parent.

That was pretty much my original sugestion...good luck with that

|||

David Frommer wrote:

The way I usually handle this is by using a business Key to uniquely identify each person (see EmployeeID)

Thnx David. I do have the business key of the employee in the dimension. However, if you create a parent-child dimension in Analysis Services, you need a key which identifies a unique row in the dimension table. I didn't mention this before, but this is one of the things for which I use the column.

Jeroen

|||

Rafael Salas wrote:

You should not try to get historical information of a row through its children...I gues that should be part of educating end users on what a SCD 2 is and what is not.

Although I'm still convinced the ParentID is not quite as useful if it doesn't give the same result as joining using the business key in combination with start- and enddate, I think this a quite a good conclusion to wrap this up :) thnx.

Jeroen

parent child relationship

I am using SQL 2000. I have a table with which has both the parent row and child rows.

Pid type status

- - --

1 P 0 -Parent row

2 C 0

3 C 1

4 C 0

I added a new column "Pstatus" to the table. I have to update the table to show the status of parent row against the child row in new col as below.... There are a million records with different parent/child.

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

2 C 0 0

3 C 1 0

4 C 0 0

Thanks...........

How do you know which Parent the Child should be associated with?

It is not clear what you are attempting to accomplish. Please offer more explanition, and perhaps sample data (in the form of INSERT statements).

|||Hey.. What you want to do here? Give more Details?|||

I apologize for the mistake in providing the complete info....The Parent Child relation is defined by Pid..which i have now corrected as below. I hope this helps.....

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

1 C 0 0

1 C 1 0

1 C 0 0

|||

I apologize for the mistake in providing the complete info....The Parent Child relation is defined by Pid..which i have now corrected as below. I hope this helps.....

Pid type status Pstatus(new col)

- - -- -

1 P 0 0 -Parent row

1 C 0 0

1 C 1 0

1 C 0 0

|||

IF PID refers to the Parent, is the record with PID=1 AND Type=P its' own parent? This design only allows for 2 levels.

So, there is no unique identifier for each Child?

Something important is missing here. It seems like there 'should' also an [ID] PRIMARY KEY column. A common design issue is to provide each row with a unique method of distinguishing that row from any other row, and in the case of hierachical designs, also a column to indicate which record is the parent. In this case, while you can create a suposition about parentage by using the [Type] column, you would not be able to tell row 2 from row 4 (using your data above)? Even identical twins have names -and different fingerprints, etc.

Otherwise, as I ask before, how do you determine

Parse Array Data Type to Rows

Hi,

We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.

Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.

Any Ideas?

Thanks

Emilio

In a data flow, use a script task to parse and pivot the array values, then send them to a asynchronous output that writes to the destination table.|||

Hi,

Thank you for the reply.

Do you know of any samples that I can have a look at to get me going in the right direction?

Warm Regards

Emilio

|||

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/17/dynamically-pivoting-columns-to-rows.aspx

Hopefully that will provide a good starting point.

|||

Excellent!!!

I can say that this is the answer to handling those db's that have an array datatype!

Parametrization of xsd:boolean RDL element

Hi,

I'm trying to parametrize <CanGrow> element of the TextBox. It has xsd:boolean type, so when i assign this element with a simple expression like this:

<CanGrow>=Parameters!CanGrow.Value<CanGrow>

I get the following error:

Deserialization failed: The 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:CanGrow' element has an invalid value according to its data type. Line 782, position 59.

because resulted XML doesn't satisfy RDL schema.

The same expression in <Hidden> element does'n involve the error, because <Hidden> has xsd:string type.

Is there any way to have xsd:boolean report element parametrized, but not hardcoded?

I've made no progress on this issue.

Could anybody explain me how to parametrize xs:boolean report elements?

Thanks.

|||Any ideas on this issue?|||

Per the RDL specification (http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx), the CanGrow element is a constant boolean value. Expressions are not supported for CanGrow.

-- Robert

|||Thaks, Robert!

But in this case why not to make all xsd:boolean RDL elements of type

xsd:string? By the way, some RDL element with true/false values already

are of xsd:string type (ex. <VisibilityType><Hidden>).

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means a
re they Default Parameter or Mandatory. Where this information is store? I c
an get ISNULLABLE Column from syscolumns table but it is only applicable to
table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.droptable.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via droptable.com" <forum@.droptable.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQ
droptable.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.droptable.com

parameters of a given stored procedure

hi,

is it possible to get the list of parameters of a given stored procedure? i need the parameter information like name of the parameter, data type of the parameter, size, type (input or output) etc while the name of the stored procedure will be provided.

with regards,

oh, found it.

select * from information_schema.parameters where specific_name = 'stored procedure name here'

Parameters in Reporting Services...

Is it possible to control a parameter type as being single vs. multi-valued
based on the value selected in the previous parameter?
Thank you
Ramdaskeep it multi - but only show one value to select if previous parameter
selection so indicates.
"Ram" wrote:
> Is it possible to control a parameter type as being single vs. multi-valued
> based on the value selected in the previous parameter?
> Thank you
> Ramdas|||Hi,
Thanks for the tip. How would i show only one value based on the previous
parameter selection.
Thank you
Ramdas
"Jimbo" wrote:
> keep it multi - but only show one value to select if previous parameter
> selection so indicates.
>
>
> "Ram" wrote:
> > Is it possible to control a parameter type as being single vs. multi-valued
> > based on the value selected in the previous parameter?
> >
> > Thank you
> > Ramdas|||use a stored procedure to populate your select list - one of the parameters
for this stored procedure would indicate whether the return list will be
multiple records or a single record
this parameter would be set by user selection before being passed to the
stored procedure
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> Hi,
> Thanks for the tip. How would i show only one value based on the previous
> parameter selection.
> Thank you
> Ramdas
> "Jimbo" wrote:
>> keep it multi - but only show one value to select if previous parameter
>> selection so indicates.
>>
>>
>> "Ram" wrote:
>> > Is it possible to control a parameter type as being single vs.
>> > multi-valued
>> > based on the value selected in the previous parameter?
>> >
>> > Thank you
>> > Ramdas|||Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.
"Jim" wrote:
> use a stored procedure to populate your select list - one of the parameters
> for this stored procedure would indicate whether the return list will be
> multiple records or a single record
> this parameter would be set by user selection before being passed to the
> stored procedure
>
>
>
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> > Hi,
> > Thanks for the tip. How would i show only one value based on the previous
> > parameter selection.
> >
> > Thank you
> > Ramdas
> >
> > "Jimbo" wrote:
> >
> >> keep it multi - but only show one value to select if previous parameter
> >> selection so indicates.
> >>
> >>
> >>
> >>
> >> "Ram" wrote:
> >>
> >> > Is it possible to control a parameter type as being single vs.
> >> > multi-valued
> >> > based on the value selected in the previous parameter?
> >> >
> >> > Thank you
> >> > Ramdas
>
>

Parameters in Reporting Services - C# Syntax

I'm using code from microsoft's website and I'm getting the following error:
"The type or namespace name 'ParameterValue' could not be found (are you missing a using directive or an assembly reference?)"
What am I doing wrong here? I want to pass parameters to the .render method.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3]; <-- error occurs on this line
parameters[0] = new ParameterValue();
parameters[0].Name = "EmpID";
parameters[0].Value = "38";
parameters[1] = new ParameterValue();
parameters[1].Name = "ReportMonth";
parameters[1].Value = "6"; // June
parameters[2] = new ParameterValue();
parameters[2].Name = "ReportYear";
parameters[2].Value = "2004";
i figured it out
i had to add ReportServer before ParameterValue
i.e. ReportServer.ParameterValue[] parameters = new ReportServer.ParameterValue[3];