Losing it again - how to pass value to sp?

I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @.C_ID value.

PROCEDURE dbo.EditCompanyInfo
@.C_ID int,
@.CS_CompanyName nchar(100),
@.CS_City nchar(50)

AS
UPDATE tblCompanyInfo_Submit
SET CS_CompanyName = @.CS_CompanyName, CS_City = @.CS_City
WHERE C_ID = @.C_ID
RETURN

This is my aspx. page:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" >
<asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px"
Width="125px">
<Fields>
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted"
SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @.CompanyID "
UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</asp:Content>

CODE BEHIND:

public partial class aaatest : System.Web.UI.Page
{
int CompanyID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
TextBox1.Text = "insert";
TextBox3.Text = Convert.ToString(CompanyID);
}
}
protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
{
foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters)
{
string RValue = Server.HtmlEncode(param.Direction.ToString());
if ( RValue == "ReturnValue" && Page.IsPostBack)
{
TextBox1.Text = Server.HtmlEncode(param.Value.ToString());
TextBox2.Text = "Return";
CompanyID = Convert.ToInt16(TextBox1.Text);
TextBox3.Text = Convert.ToString(CompanyID);
}
}
}
}
}

This might help... although you'll need to adjust it to be UpdateParameters on an update etc.

SqlDataSource1.SelectParameters["ParamName"] =

newParameter("ParamName",TypeCode.String,"Value");sql

Loosing text format in pdf-Export

Hi NG,
in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
field-type is currency
After export in pdf i got something like that:
0761<; ; 1557/5<#0 ander othe unknown characters.
Any suggests?
frank
www.xax.deAdobe 5.00 and 5.05 do not render symbols (e.g. Euro symbol) correctly when
the Arial font is used. Which version are you using? Did you try other fonts
(e.g. Tahoma)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:34hkssF46ueteU1@.individual.net...
> Hi NG,
> in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
> field-type is currency
> After export in pdf i got something like that:
> 0761<; ; 1557/5<#0 ander othe unknown characters.
> Any suggests?
> frank
> www.xax.de
>sql

Looping through each row in an XML object sent to a Stored Procedure

I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure.

Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.

The XML File is layed out as:

<Values>
<value>
<value>1</value>
<key>My_Field</key>
</value>
<value>
<value>3523.2</value>
<key>My_other_Field</key>
</value>
</Values
I basically need to go through it row by row, find out what table I need to insert the value into using thekeyfield.

Any help with this would rock. I'm using SQL 2005.

Tried a bit more but couldn't figure it out.

Basically I need to do something like this (logic):

Foreach row in @.xml

switch(SELECT FieldType FROM fields WHERE Name = @.Xml[key])

case :TextBox:
INSERT INTO TextFields(Value) VALUES (@.Xml[Value])
break;

case: listBox
INSERT INTO ListFields(Values) VALUES)@.Xml[Value])

... etc.

I have to check each row in the XML object sent in to see what table I need to insert the corresponding value to. I know this is possible, i'm just not sure how.

|||

Hi,

Actually you can use XMLDocument to get the data from XML file and store it into your database. See the sample below.

XmlDocument xd =new XmlDocument();xd.Load(inputurl);/// inputurl is the path of the xml file. XmlNodeList xnl = xd.GetElementsByTagName("value");for (int i = 0; i < xnl.Count; i++) { XmlNode xn0 = xnl.Item(i).ChildNodes[1]; XmlNode xn1 = xnl.Item(i).ChildNodes[0];string xn0_str = xn0.InnerText;string xn1_str = xn1.InnerText;// xn0_str stands for the table name // xn1_str stands for the value you want to insert. }

Hope that helps. Thanks.

|||

That would work, however that means I will be hitting the Database with many calls. I'd rather execute 1 call to the DataBase. Sometimes that XML file might have 100 rows in it. I don't want to hit the DB 100 times. I would rather send everything in at once, and then have the Database do all the work. It's less expensive that way.

Thanks :) I found a way to work it though using temp tables :)

|||

Hi,

Well, I know your needs. The main idea is to loop the xml file first, and take down each node's value in an array. And then build your insert statement dynamically by looping the array value.

Thanks.

Looping rows to Increment a value

I currently have a table that contains a Serial Number and a quantity.

SN Qty
4352301 3
6892103 2

I need to be able to loop through this table and increment each unique Serial Number by the quantity. The desired result set is:

4352301
4352302
4302303
6892103
6892104

I can pull off a Do While loop in ASP however I am having trouble with the TSQL syntax. Any help would be GREAT !!

joeUPDATE table SET serial = serial + quantity ?|||Something like this?

Code:
-------------------------------------
create table #tmp(sn int, qty int)
insert into #tmp values(4352301,3)
insert into #tmp values(6892103,2)
select * From #tmp
declare @.sn int, @.qty int, @.cntr int
select @.sn = min(sn) from #tmp
while (@.sn is not null) begin
select @.qty = qty, @.cntr = 1 from #tmp where sn = @.sn
while (@.cntr < @.qty) begin
select @.sn = @.sn + 1, @.cntr = @.cntr + 1
insert into #tmp values(@.sn,@.qty)
end
select @.sn = min(sn) from #tmp where sn > @.sn
end
select * from #tmp order by sn
-------------------------------------

Results:
-------------------------------------
sn qty
---- ----
4352301 3
6892103 2

sn qty
---- ----
4352301 3
4352302 3
4352303 3
6892103 2
6892104 2
-------------------------------------

Loop Update

I'm trying to find a way to loop through the testnewsRecipients table and
insert the value of the USERID field equal to the USERID field in the
tempUsers table. As you can see in my DDL, currently the USERID field in
testnewsRecipients table is empty and the only value the 2 tables have in
common is the email field.
Can this be done with a loop statement?
DDL ****************
CREATE TABLE testUsers(
userID int NULL,
userEmail varchar(50) NULL
) ON [PRIMARY]
GO
insert into testUsers (userID, userEmail) values
('101', 'test1@.test.com')
insert into testUsers (userID, userEmail) values
('102', 'test2@.test.com')
insert into testUsers (userID, userEmail) values
('103', 'test3@.test.com')
insert into testUsers (userID, userEmail) values
('104', 'test4@.test.com')
GO
CREATE TABLE testnewsRecipients(
recipID int IDENTITY(1,1) NOT NULL,
userID int NULL,
recipEmail varchar(100) NULL
) ON [PRIMARY]
--
insert into testnewsRecipients (userID, recipEmail) values
('', 'test1@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test2@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test3@.test.com')
insert into testnewsRecipients (userID, recipEmail) values
('', 'test4@.test.com')UPDATE testnewsRecipients
SET userID = t2.userID
FROM testnewsRecipients t1
INNER JOIN testUsers t2
ON t2.userEmail = t1.recipEmail
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> I'm trying to find a way to loop through the testnewsRecipients table and
> insert the value of the USERID field equal to the USERID field in the
> tempUsers table. As you can see in my DDL, currently the USERID field in
> testnewsRecipients table is empty and the only value the 2 tables have in
> common is the email field.
> Can this be done with a loop statement?
>
> DDL ****************
> CREATE TABLE testUsers(
> userID int NULL,
> userEmail varchar(50) NULL
> ) ON [PRIMARY]
> GO
> insert into testUsers (userID, userEmail) values
> ('101', 'test1@.test.com')
> insert into testUsers (userID, userEmail) values
> ('102', 'test2@.test.com')
> insert into testUsers (userID, userEmail) values
> ('103', 'test3@.test.com')
> insert into testUsers (userID, userEmail) values
> ('104', 'test4@.test.com')
> GO
> CREATE TABLE testnewsRecipients(
> recipID int IDENTITY(1,1) NOT NULL,
> userID int NULL,
> recipEmail varchar(100) NULL
> ) ON [PRIMARY]
> --
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test1@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test2@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test3@.test.com')
> insert into testnewsRecipients (userID, recipEmail) values
> ('', 'test4@.test.com')
>
>|||Thanks, but I was hoping someone would provide a "FOR" loop example for
educational purpose. I've never done a loop with sql and wanted to learn.
would a loop work on my example?
"Mike C#" <xxx@.yyy.com> wrote in message news:p%vag.3964$Xa5.673@.fe11.lga...
> UPDATE testnewsRecipients
> SET userID = t2.userID
> FROM testnewsRecipients t1
> INNER JOIN testUsers t2
> ON t2.userEmail = t1.recipEmail
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:%23ru%23oiVeGHA.2188@.TK2MSFTNGP05.phx.gbl...
>|||Yeah with a CURSOR or a WHILE statement and a counter variable. Lot more
work than doing it with a single UPDATE statement however. Look up DECLARE
CURSOR (ugh) and WHILE in BOL.
"scott" <sbailey@.mileslumber.com> wrote in message
news:OLekAEWeGHA.1792@.TK2MSFTNGP03.phx.gbl...
> Thanks, but I was hoping someone would provide a "FOR" loop example for
> educational purpose. I've never done a loop with sql and wanted to learn.
> would a loop work on my example?
>
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:p%vag.3964$Xa5.673@.fe11.lga...
>

loop over fields?

Hi,
i have to check all fields for a certain value, can I loop over all fields
in a custom code function?
Thx, NigelHello,
I have a similar situation as to the one you've described below. Did you
ever get an answer to your question or did you come up with a solution that
you can share?
Thanks,
Dave
"Nigel Jensen" wrote:
> Hi,
> i have to check all fields for a certain value, can I loop over all fields
> in a custom code function?
> Thx, Nigel
>
>

Loop in SSRS Expression

Hi guys,

I got a multi value parameter which displays all my 'companies'. On my report header, I wish to display all the companies selected by the user.

Parameters!Company.Label(0) only shows the first on selected

Please, any help will be greatly appreciated

W Wilmot

W

Try this.

Join( Parameters!Company.value," ,")

the second part of the Join is your delimiter.

Hammer

|||

Thanx Hammer

It worked fine if i use the 'Join( Parameters!Company.label," ,")' instead of 'Join( Parameters!Company.value," ,")'

Thank you, again!!!

|||

W,

Not a problem, can you mark this post helpful?

Thanks,

Hammer

Lookup value query joining two tables

David:

It is not a join as keys are not present in both the tables; between
will just restrict the values for test_val. However, the following
solution has been proposed by Joe Celko, which works. Thanks to
everybody who contibuted. David and Joe, thanks so much.

UPDATE T1
SET factor
= (SELECT T2.factor
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val)
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.test_val BETWEEN T2.from_val AND T2.to_val);

--CELKO--> It is not a join as keys are not present in both the tables

Keys are not a requirement for a join. Any boolean expression (including the
BETWEEN predicate) can be used as the basis of a join between two tables.
That's with or without keys or corresponding columns in both tables.

--
David Portas
SQL Server MVP
--

Lookup value for a field?

Using VS2005 and creating reports. GUI has combo boxes with lookups. The
main table is storing the selected.value of the combo box, but the combo box
is displaying the "Name" column.
How do I display the "Name" column value on a report for a field instead of
the integer value of the row?
Thanks.If you look at the different options in the parameter creation window, it's
pretty straightforward.
1) While on the "Data" tab, go to "Report | Report Parameters" in the menu.
2) Click on "Add".
3) Give a meaningful name to your parameter (other than
"Report_Parameter_0")
4) Select a datatype (string, int, etc.). In your case, this would be int.
5) Type a prompt for your parameter (this will appear to the left of your
parameter combobox in the browser).
6) In the "Available Values" section, select "From Query"
7) Select the dataset you will use for your parameter values (typically a
separate dataset from the report's main dataset).
8) Put your key field (the integer value) in the "Value Field" drop down.
9) Preview, Rebuild or redeploy your report and voilà!
HTH,
Alain
"Brooke" <tbrooked@.hotmail.com> wrote in message
news:%23dSGgzfrHHA.5024@.TK2MSFTNGP04.phx.gbl...
> Using VS2005 and creating reports. GUI has combo boxes with lookups. The
> main table is storing the selected.value of the combo box, but the combo
> box is displaying the "Name" column.
> How do I display the "Name" column value on a report for a field instead
> of the integer value of the row?
> Thanks.
>

Lookup UDF

Hi,
Can anybody provide me a Lookup UDF? I need to supply columnname,Tablename and condition dynamically and I need the scalar value in return.
Any help will be greatly appreciated...Hello again Rudra - I very recently commented on you not being around much these days :)

I have a suspicion about what you want to do and it is probably bad and likely impossible if interpreted literally. Do you want to supply a column name, table name, a key value to a function and get an alternate key value in return (i.e. have some dynamic SQL within the UDF)? If not please can you give more details about what you want this function to actually do.|||Hello again Rudra - I very recently commented on you not being around much these days :)

If not please can you give more details about what you want this function to actually do.

Thanks Pootle.:p It seems just like the old days.
Yea ,I was not around for a long time.But I always find the Yak Corral's B-E-A-U-T -Y Bcoz it always goes on and on and on...

Well,I know what I am trying to do is quite impossible but can you suggest me a way by which I can fetch a value in by lookup UDF or something like that in a long script?
In one of the script there are no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?|||In one of the script there is no of cases where things are done in this way-
case when Policy.FinanceID = 0 Then ' ' else 'Finance Co.: ' + (select name from Finance where financeId= policy.financeid)Can I use something else for that line?Well, not knowing any more about your problem, the easiest thing would be to insert the data into the table.

INSERT Finance (financeId, name)
SELECT 0, ''
This also covers a bug bear of mine which is sticking your data into code. (linky: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx) Otherwise you could create a view:

CREATE VIEW finance_x
AS
SELECT financeId, name
FROM finance
UNION ALL
SELECT 0, ''
and use that.|||Hmm... that link was great :cool: thanks a lot Pootle...see u soon.

Lookup transformation

Hi Everyone,

I'm trying to perform a lookup transformation. But the deal is, I have this one value that I am passing into the transformation, but I would like to gather all values that match the value I put in....does the lookup transformation do this? I tried it, and it appears as if it only returns one value for the one input. After the lookup, I have an access OLE DB destination setup...so I can capture all those values that corresponds to that one value I passed into the lookup. Does anyone have any ideas on how I can go about this?

Thanks!

Not quite sure what you want to do here. The LOOKUP works on a row at a time in isolation from all other rows.

Do you mean you want to return multiple values from your lookup cache? it doesn't do that, it only returns one value. The first one that it sees.

It sounds like you want to be looking at the MERGE JOIN component.

-Jamie

Lookup Transform error when linking using a DT_R8

I'm trying to lookup a value in another table linking on a column of datatype DT_R8. The lookup transform is complaining that I can't link on that datatype. However, the documentation says that it should work. I'm using the April CTP. Is this fixed in a later version? Any suggestions?

I'm sure DT_R8 does work in a Lookup. You may get an error such as -

[DDD, BackOfficeSoftwareKey]
One or more columns do not have supported data types, or their data types do not match.

This means that the source data type and the reference data type do not match. All lookups, the mappings between source and reference columns, must compare data types that match exactly, so you cannot compare a DT_I4 with a DT_R8 for example. One of them needs to be converted to the same type as the other before the match. You may be able to do this in the source extraction, or in the lookup reference table specification, use a SQL query with a CAST, or finally use a Data Conversion Tramsform or Derived Column.

|||I'm pretty sure that's not the problem becuase I've tried that approach. I've also double-checked the data types in the advanced editor and the datatypes do match. They are both DT_R8. It's not the usual error message that you mentioned. It's a different one that says "input column <column_name> has a datatype which cannot be joined on."|||DT_R8 is not allowed as a join column. So are DT_R4 and BLOBs (DT_TEXT, DT_NTEXT, DT_IMAGE).

Lookup Tables And Joins

In a table I'm storing as integers recCreatedBy and recModifiedBy values; I have a lookup table I'm doing an INNER JOIN with to pull a string value for an Alias 'CreatedBy'. I'm trying to Alias a 'ModifiedBy' from the same lookup table in the query. Any help would be greatly appreciated.
SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, tblUsers.userLogin AS CreatedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers ON tblStaffPhysicians.recCreatedBy = tblUsers.userIndexIf I understand you correctly, the SQL would look like this:

SELECT tblStaffPhysicians.docIndex, tblStaffPhysicians.docFullName, Created.userLogin AS CreatedBy, Modified.userLogin AS ModifiedBy
FROM tblStaffPhysicians INNER JOIN
tblUsers Created ON tblStaffPhysicians.recCreatedBy = Created.userIndex
LEFT OUTER JOIN tblUsers Modified ON tblStaffPhysicians.recModifiedBy = Modified.userIndex

I am assuming recModifiedBy could be NULL. If that is incorrect, you could just do an inner join.

Hope that helps!!

BobP|||Thank you so much for your help; it looks like your suggestion is the solution. Thanks again.

Lookup on a range

I want to check to see if my document number (let's say, 11) is in between two values: from a table in my database, I have a min value and a max value; how can I check to see if the number is between the min and the max?

Thanks!

Jim Work
In the control flow, you may want to issue an execute sql task with the query being: select min(value) as minvalue, max(value) as maxvalue from table.

Then map the results to two variables.

Then, in the data flow, you can compare input to the two variables using a conditional split, derived column, etc...

looking value in table

Hello there
I have huge table with at least 20,000,000 records, On that I have Field1
who has unique values.
The same value sometimes shown on the same table on Field10 but not on the
same record and it isn't unique.
i need to build query that returs all the records with Field1Who exist
somewere on Field10.
Any query that i build took more then 1 hour to work.
How can i do it on query that will run fast?Roy, shalom
CREATE TABLE #Test (c1 INT NOT NULL PRIMARY KEY,c2 INT)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (2,1)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (4,5)
INSERT INTO #Test VALUES (5,4)
INSERT INTO #Test VALUES (6,7)
INSERT INTO #Test VALUES (7,10)
INSERT INTO #Test VALUES (8,1)
SELECT c1 FROM #Test
WHERE EXISTS (SELECT * FROM #Test T WHERE #Test.c1=T.c2)
Try create index on c1,c2 and see what is going on
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e6sex1PbGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>|||From what you had given, field1 is the primary key and it should have been
cluster-indexed. If so, then just do this
and try to execute the query
Create a non-clustered index on Field 10 alone
Hope this helps.
--
"Roy Goldhammer" wrote:

> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>
>

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 query plan determination information

Hi,

I was just helping a coworker optimize a query. He had two versions:
one which used UNION for each value for which he was tallying results
and another query which used GROUP BY. Here is an aproximation of what
they were:

Query #1:
---
SELECT 12 AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
AND T.col2 = 5
UNION
SELECT 13 AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
AND T.col2 = 6

Query #2:
---
SELECT R.row_num AS [Row],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
@.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
END), 0) AS [Tuesday]
FROM My_Table T
INNER JOIN Another_Table T2 ON T2.col1 = T.col1
INNER JOIN Report_Rows R ON R.col2 = T.col2
WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
GROUP BY ALL R.row_num
ORDER BY R.row_num

The Report_Rows table in this case would have had two rows mapping row
12 to a column value of 5 and row 13 to a column value of 6. The
second query was performing horribly until I noticed the ALL keyword
in the GROUP BY, which I didn't think was necessary. When I removed
that it performed more like I expected it to perform.

Before I had noticed that I was scouring over the query plans and
couldn't figure out why in one instance the query optimizer chose to
join My_Table and Another_Table, yet when the ALL keyword was there it
chose to return all of the records from Another_Table (a rather large
table) and join it to the Report_Rows table before then joining to
My_Table, which had the date criteria in the WHERE clause.

So, if you've read this far without giving up...

1. Why would the ALL keyword cause this? I understand the
functionality of ALL, but I still don't see why that caused the
reordering of the joins.

2. (more importantly) Are there any good resources that you know of
that explain how the query optimizer choices its query paths? Do the
"Inside SQL Server" books go into that much detail? Any good online
resources?

Thanks!
-Tom."Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message
news:a2c0eeb8.0309160626.901177@.posting.google.com ...
> Hi,
> I was just helping a coworker optimize a query. He had two versions:
> one which used UNION for each value for which he was tallying results
> and another query which used GROUP BY. Here is an aproximation of what
> they were:
> Query #1:
> ---
> SELECT 12 AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> AND T.col2 = 5
> UNION
> SELECT 13 AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> AND T.col2 = 6
> Query #2:
> ---
> SELECT R.row_num AS [Row],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN @.week_start_date AND
> DATEADD(d, 1, @.week_start_date) THEN 1 ELSE 0 END), 0) AS [Monday],
> ISNULL(SUM(CASE WHEN T.my_date BETWEEN DATEADD(d, 1,
> @.week_start_date) AND DATEADD(d, 2, @.week_start_date) THEN 1 ELSE 0
> END), 0) AS [Tuesday]
> FROM My_Table T
> INNER JOIN Another_Table T2 ON T2.col1 = T.col1
> INNER JOIN Report_Rows R ON R.col2 = T.col2
> WHERE T.my_date BETWEEN @.week_start_date AND @.week_end_date
> GROUP BY ALL R.row_num
> ORDER BY R.row_num
> The Report_Rows table in this case would have had two rows mapping row
> 12 to a column value of 5 and row 13 to a column value of 6. The
> second query was performing horribly until I noticed the ALL keyword
> in the GROUP BY, which I didn't think was necessary. When I removed
> that it performed more like I expected it to perform.
> Before I had noticed that I was scouring over the query plans and
> couldn't figure out why in one instance the query optimizer chose to
> join My_Table and Another_Table, yet when the ALL keyword was there it
> chose to return all of the records from Another_Table (a rather large
> table) and join it to the Report_Rows table before then joining to
> My_Table, which had the date criteria in the WHERE clause.
> So, if you've read this far without giving up...
> 1. Why would the ALL keyword cause this? I understand the
> functionality of ALL, but I still don't see why that caused the
> reordering of the joins.
> 2. (more importantly) Are there any good resources that you know of
> that explain how the query optimizer choices its query paths? Do the
> "Inside SQL Server" books go into that much detail? Any good online
> resources?
> Thanks!
> -Tom.

It's almost impossible (at least for me) to know why the optimizer chose a
particular plan without knowing the table structures, indexes and amount of
data, and even with that knowledge, it may not be clear at all. So I can't
say much about your first question, but I can definitely recommend Inside
SQL Server 2000 for a great explanation of what the optimizer considers when
it produces a query plan. There's a lot of detail, including how to go about
using query plans to tune individual queries. Another useful book is
Advanced Transact SQL for SQL Server 2000, which also explains many of the
examples with reference to their query plans.

Simon|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> Before I had noticed that I was scouring over the query plans and
> couldn't figure out why in one instance the query optimizer chose to
> join My_Table and Another_Table, yet when the ALL keyword was there it
> chose to return all of the records from Another_Table (a rather large
> table) and join it to the Report_Rows table before then joining to
> My_Table, which had the date criteria in the WHERE clause.

I can only echo Simon's reply that without table definitions etc, this
is difficult to tell. In fact, even with all information available,
this might be difficult to tell. Understanding the output of a cost-
based optimizer is by no means an easy task.

> 2. (more importantly) Are there any good resources that you know of
> that explain how the query optimizer choices its query paths? Do the
> "Inside SQL Server" books go into that much detail? Any good online
> resources?

Certainly, you learn a great deal from Kalen's book. But I also like to
add that that experience counts a lot too. And some creative thinking.
The basic thing to understand is why a table scan may be better than
an index seek. This is something which also can be extended to joins.
That is a scan + merge/hash join may be faser than seek + loop join.

But then there are all such wild things which includes parallelism that
I find myself understanding only fragments of.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you both for the input. I had tried to duplicate the effect with
test tables, but as you know, the query optimizer takes a lot into
account and I couldn't find an example that would be practical for
posting here.

I will give the two books that Simon suggested a more thorough read.
I've seen adverts for Kalen's online webinars as well, so perhaps I'll
look into those.

> But then there are all such wild things which includes parallelism that
> I find myself understanding only fragments of.
I hate to think of something that is complex enough that you have
trouble understanding it Erland... ;-)

Thanks again!
-Tom.

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
JakeHope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:

> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate t
he
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data coul
d
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would b
e
> appreciated. Thanks.
> Jake
>
>|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...[vbcol=seagreen]
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
>|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from th
e
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:

> GYK,
> This is great thank you. If possible could you walk me through this so
I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>
>|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...[vbcol=seagreen]
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
>|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('
-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
JakeHope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
> appreciated. Thanks.
> Jake
>
>|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
>> Hello,
>> I have a column of varchar that has data of 4-5 I need to manipulate
>> the
>> data in a query to get it into another table to where the value would be
>> 0040005. How could I accomplish this by parsing out the -? Other data
>> could
>> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
>> be
>> appreciated. Thanks.
>> Jake
>>|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from the
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:
> GYK,
> This is great thank you. If possible could you walk me through this so I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> > Hope this helps
> >
> > CREATE TABLE #test
> > (col1 VARCHAR(5))
> >
> > INSERT INTO #test VALUES('4-5')
> > INSERT INTO #test VALUES('12-99')
> > INSERT INTO #test VALUES('1-234')
> >
> >
> > SELECT
> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> > +REPLACE(col1,'-', '000'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '00'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '0'),7)
> > END
> > FROM #test
> >
> > Thanks
> > GYK
> >
> > "Jake" wrote:
> >
> >> Hello,
> >>
> >> I have a column of varchar that has data of 4-5 I need to manipulate
> >> the
> >> data in a query to get it into another table to where the value would be
> >> 0040005. How could I accomplish this by parsing out the -? Other data
> >> could
> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
> >> be
> >> appreciated. Thanks.
> >>
> >> Jake
> >>
> >>
> >>
>
>|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
>> GYK,
>> This is great thank you. If possible could you walk me through this
>> so I
>> could learn how it worked? I got the charindex but and confused with teh
>> reverse and then right +replace idea. I truly appreciate it.
>> Jake
>>
>> "GYK" <GYK@.discussions.microsoft.com> wrote in message
>> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>> > Hope this helps
>> >
>> > CREATE TABLE #test
>> > (col1 VARCHAR(5))
>> >
>> > INSERT INTO #test VALUES('4-5')
>> > INSERT INTO #test VALUES('12-99')
>> > INSERT INTO #test VALUES('1-234')
>> >
>> >
>> > SELECT
>> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
>> > +REPLACE(col1,'-', '000'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '00'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '0'),7)
>> > END
>> > FROM #test
>> >
>> > Thanks
>> > GYK
>> >
>> > "Jake" wrote:
>> >
>> >> Hello,
>> >>
>> >> I have a column of varchar that has data of 4-5 I need to
>> >> manipulate
>> >> the
>> >> data in a query to get it into another table to where the value would
>> >> be
>> >> 0040005. How could I accomplish this by parsing out the -? Other data
>> >> could
>> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help
>> >> would
>> >> be
>> >> appreciated. Thanks.
>> >>
>> >> Jake
>> >>
>> >>
>> >>
>>|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
Jake
Hope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:

> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
> appreciated. Thanks.
> Jake
>
>
|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...[vbcol=seagreen]
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from the
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:

> GYK,
> This is great thank you. If possible could you walk me through this so I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>
>
|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...[vbcol=seagreen]
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>