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

Looping through stored procedure inside another stored procedure and displaying the catego

I used to do this with classic asp but I'm not sure how to do it with .net.

Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category.

I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.

Just for an example..

Category Table:

ID Category
1 Saints
2 Falcons
3 Bucaneers
4 Chargers
5 Falcons

Player Table:

ID CategoryID Player News Player Last Updated
1 1 Reggie Bush Poetry in motion 9/21/2006
2 1 Drew Brees What shoulder injury? 9/18/2006
3 5 Michael Vick Break a leg, seriously. 9/20/2006

Basically I would need to display on a page:

Saints
Reggie Bush
Poetry in Motion

Falcons
Michael Vick
Break a leg, seriously.

So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.

I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

try building a query with sub-queries based on a join within the store procedure. test it in the query manager first then before making it a SP

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 through a recordser in a stored procedure

I have a table [myOrders] with three columns. One of the columns
contains text output data [myText] nvarchar(500), one of them contains
a filename [myFileName] nvarchar(50), one of the columns is a bit to
record if it has been output yet[isOutput] bit default value = 0.

I am creating a SQL Agent job that needs to look at a recordset of
[myOrders] where [isOutput] = 0 and create a seperate text file for
each row using [myFileName] as the filename.

Then I need to mark [isOutput] of each record in [myOrders] as 1.

Ok, so that's the task...

What I'm thinking is I construct a stored procedure that starts with a
select statement:

Create PROCEDURE JustDoIt
AS
set nocount on

SELECT
myText, myFileName
FROM
myOrders
WHERE
(isOutput = 0)

THEN I USE BCP to create the file looping through the recordset above.
THIS IS THE PART I AM CLUELESS ABOUT!

/* NEED TO LOOP HERE */

DECLARE @.ReturnCode int
DECLARE @.ExportCommand varchar(255)
DECLARE @.FileName nvarchar(50)
DECLARE @.FileText nvarchar (500)

SELECT @.FileName = myFileName

/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK:)

SET @.ExportCommand =
'BCP @.FileText queryout "c:\' +
@.FileName +
'" -T -c -S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

/* NEED TO EXIT LOOP HERE */

Then I update all records in [myOrders] to 1

BEGIN TRANSACTION

UPDATE
myOrders
SET isOutput = 1
WHERE
(isOutput = 0)

/* err checking here */

COMMIT TRANSACTION

I'm hoping someone can help me construct this.
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a table [myOrders] with three columns. One of the columns
> contains text output data [myText] nvarchar(500), one of them contains
> a filename [myFileName] nvarchar(50), one of the columns is a bit to
> record if it has been output yet[isOutput] bit default value = 0.
> I am creating a SQL Agent job that needs to look at a recordset of
> [myOrders] where [isOutput] = 0 and create a seperate text file for
> each row using [myFileName] as the filename.

I'm glad to see that you are exploring Agent!

Did you ever consider of making it an Active-X job step? You could then
use VBscript for the task, and it may be easier to write files from
VBscript. (Then again, I have never used VB-script myself.) You could
also write a command-line program in whatever language you fancy, and
run the step as as CmdExec.

You could do this in T-SQL, by setting up a cursor, but since you
would have to fork out with xp_cmdshell for BCP for each file, there
may be a performance cost. VBscript (or whatever language) would be
more effective.

The cusror solution is fairly straightforward:

DECLARE your_cur INSENSITIVE CURSOR FOR
> SELECT
> myText, myFileName
, OrderID
> FROM
> myOrders
> WHERE
> (isOutput = 0)

OPEN your_cur

WHILE 1 = 1
BEGIN
FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
IF @.@.fetch_status <> 0
BREAK

> SET @.ExportCommand =
> 'BCP @.FileText queryout "c:\' +
> @.FileName +
> '" -T -c -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

IF @.ReturnCode = 0
UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

END

DEALLOCATE your_cur

Now, as it written above, it assumes that @.FileText is a query, but
from your narrative, I believe it is just a file. You could make it
a query by

SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks very much for this code. Hopefully I can construct this from
this foundation.
Question though, can I avoid using cursors by taking advantage of BCP
parameters firstrow, lastrow and batchsize parameters so that I output
one row at a time of a variable row recordset? This would be my
first option.
Thanks,
LQ

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||Erland, Thanks again for your time.
I want to do this without using a cursor, instead use select top 1 of
the recordset and loop through BCP until there are no more records.

Looks something like

SELECT top1 OrderID, myText, myFileName from tblOrders where isOutput
= 0

>>WHatI need here is to figure out how to extract the value of
myFileName and myText and pass it to the BCP Utility<<

Do until there's no more records in select statement above:

SET @.ExportCommand = 'BCP myText queryout "c:\' + myFileName+ '" -T -c
-S ' + @.@.SERVERNAME
EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand

UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID

Loop

Sorry for being so dumb about this. I have never used this sort of
construction before.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > I have a table [myOrders] with three columns. One of the columns
> > contains text output data [myText] nvarchar(500), one of them contains
> > a filename [myFileName] nvarchar(50), one of the columns is a bit to
> > record if it has been output yet[isOutput] bit default value = 0.
> > I am creating a SQL Agent job that needs to look at a recordset of
> > [myOrders] where [isOutput] = 0 and create a seperate text file for
> > each row using [myFileName] as the filename.
> I'm glad to see that you are exploring Agent!
> Did you ever consider of making it an Active-X job step? You could then
> use VBscript for the task, and it may be easier to write files from
> VBscript. (Then again, I have never used VB-script myself.) You could
> also write a command-line program in whatever language you fancy, and
> run the step as as CmdExec.
> You could do this in T-SQL, by setting up a cursor, but since you
> would have to fork out with xp_cmdshell for BCP for each file, there
> may be a performance cost. VBscript (or whatever language) would be
> more effective.
> The cusror solution is fairly straightforward:
> DECLARE your_cur INSENSITIVE CURSOR FOR
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> IF @.ReturnCode = 0
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
> END
> DEALLOCATE your_cur
> Now, as it written above, it assumes that @.FileText is a query, but
> from your narrative, I believe it is just a file. You could make it
> a query by
> SELECT @.filetext = 'SELECT ''' + replace(@.filetext, '''', ''') + ''''|||I realize there is a cost to using cursors, but since you're going
to launch the command shell for BCP on every record, the cost of the
cursor is probably insignificant. But if you insist...

> Erland, Thanks again for your time.
> I want to do this without using a cursor, instead use select top 1 of
> the recordset and loop through BCP until there are no more records.
> Looks something like

WHILE 1=1 BEGIN
SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
from tblOrders where isOutput = 0

IF @.@.ROWCOUNT = 0 BREAK
> SET @.ExportCommand = 'BCP '+@.myText+' queryout "c:\' + @.myFileName+ '" -T -c
> -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> UPDATE myOrders SET isOutput = 1 WHERE orderID= @.orderID
END -- end of loop|||[I'm answering to Jim's post, since Lauren's has not made it here yet. My
ISP reconfigured the news server and it took them two days to realize
that it was no longer working.]

Jim Geissman (jim_geissman@.countrywide.com) writes:
> I realize there is a cost to using cursors, but since you're going
> to launch the command shell for BCP on every record, the cost of the
> cursor is probably insignificant. But if you insist...

Why Laruen does not want to use a cursor I don't know, but since he
has to iterate anyway, cursor is the best solution for iteration anyway.

Say that you instead do:

> SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> from tblOrders where isOutput = 0

If there is no index on isOutput (and one would not expect that),
and the table is huge, this can be very expensive. I have no benchmarks,
but I would suggest that for an iteration a cursor is the best way to
go, although it depends on the cursor type. FAST_FORWARD may be the
fastest, but I always use INSENSITIVE.

Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
or similar as I suggested in my previous post. It will be easier to
program and execute faster.

Also, it occurred to me that if tblOrders.myText is the text that is
to be written to the file, the QueryOut thing will not work, since the
newlines in myText causes problem. Then again if the query for queryout is

'SELECT myText FROM tblOrders = ' + str(@.orderid)

that will work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland (and Jim)
You guys have gone above and beyond the call of duty in your response
to my problem and because of your repsonses I have been able to roll
this out. Thanks a million, and I have only one correction for
Erland's post - substitute "she" for "he" and it's 100% correct!
Thanks,
lq

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9534EF744A28AYazorman@.127.0.0.1>...
> [I'm answering to Jim's post, since Lauren's has not made it here yet. My
> ISP reconfigured the news server and it took them two days to realize
> that it was no longer working.]
> Jim Geissman (jim_geissman@.countrywide.com) writes:
> > I realize there is a cost to using cursors, but since you're going
> > to launch the command shell for BCP on every record, the cost of the
> > cursor is probably insignificant. But if you insist...
> Why Laruen does not want to use a cursor I don't know, but since he
> has to iterate anyway, cursor is the best solution for iteration anyway.
> Say that you instead do:
> > SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> > from tblOrders where isOutput = 0
> If there is no index on isOutput (and one would not expect that),
> and the table is huge, this can be very expensive. I have no benchmarks,
> but I would suggest that for an iteration a cursor is the best way to
> go, although it depends on the cursor type. FAST_FORWARD may be the
> fastest, but I always use INSENSITIVE.
> Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
> or similar as I suggested in my previous post. It will be easier to
> program and execute faster.
> Also, it occurred to me that if tblOrders.myText is the text that is
> to be written to the file, the QueryOut thing will not work, since the
> newlines in myText causes problem. Then again if the query for queryout is
> 'SELECT myText FROM tblOrders = ' + str(@.orderid)
> that will work.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> You guys have gone above and beyond the call of duty in your response
> to my problem and because of your repsonses I have been able to roll
> this out. Thanks a million, and I have only one correction for
> Erland's post - substitute "she" for "he" and it's 100% correct!

Glad to hear that you got it working! And my cheeks blossom in embarrassment
for calling you a man. I remember it to next time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
Thanks
This should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thanks

Looping over an Incoming Comma Delimited List

There are two separe issues I have I would like help with. In both cases I'm
bringing into a stored procedure a comma delimited list of numeric values.
The length of the list varies.
1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
list).
Currently the IN statement does not want to regonized the list as a valid
list.
2. I need to loop over an incomming list of ids and select the name, and
insert it into a temp table. The problem I'm having is looping over the list
itself and getting the value in the next position to use in the select
statement. I can not use cursors or any other method that is resource heavy,
I have too many users using the same server and database at the same time.
ThanksThis should work for you. (I forgot where I got this from, but I am not the
original author)..
Create the function below first.
Create procedure myProc
@.inputValues varchar(100)
,@.SplitChar char(1)
as
Update tablex
set col1 = something
where col2 in (select col2 from fnIntSplitter(@.inputValues,@.SplitChar)
CREATE Function fnIntSplitter (@.IDs Varchar(100),@.SplitChar char(1) )
Returns @.Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @.IDs = @.IDs + @.SplitChar
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int
-- Start from first character
Set @.Pos1=1
Set @.Pos2=1
While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(@.SplitChar,@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
"Alyx" <Alyx@.discussions.microsoft.com> wrote in message
news:BB8F2164-17CB-4E24-87EB-0B83AD647F10@.microsoft.com...
> There are two separe issues I have I would like help with. In both cases
> I'm
> bringing into a stored procedure a comma delimited list of numeric values.
> The length of the list varies.
> 1. I need to a UPDATE tablename SET cnt=cnt+1 WHERE id IN (the incomming
> list).
> Currently the IN statement does not want to regonized the list as a valid
> list.
> 2. I need to loop over an incomming list of ids and select the name, and
> insert it into a temp table. The problem I'm having is looping over the
> list
> itself and getting the value in the next position to use in the select
> statement. I can not use cursors or any other method that is resource
> heavy,
> I have too many users using the same server and database at the same time.
> Thankssql

Looping a stored procedure in a dts package

Hello,

I have a stored procedure that processes an individual file from a
directory and archives it in a subdirectory.Now, the problem is, when i
execute it , it will only process one file. What i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an activex
script that would get the filecount in the folder, and if there were more
than 0 files in the folder, then DTS-sUCCESS and on "success" workflow , it
would run the stored procedure, and thus it woould process one file, then
"on completion" the workflow connected it back to the activeX script(thus
looping), which would count the files again. Now if there were 0 files, it
would report DTS_FAILIURE, and I had it set up ,"on failiure" to go to the
next step in the package, but it wouldn't run.

Someone mind showing me a ray of light?What you can do is create another SP (Parent SP)which is a wrapper on
the current SP .
Read the files one by one in the main SP and call your SP .
In DTS flow replace the current SP with Parent SP.

Srinivas
Alex wrote:

Quote:

Originally Posted by

Hello,
>
I have a stored procedure that processes an individual file from a
directory and archives it in a subdirectory.Now, the problem is, when i
execute it , it will only process one file. What i want to do is to check
to see if there are any files in the folder, and if there are , process
them all, and once done, go to the next part in a DTS package, if there are
no files, simply go to the next part in the DTS package. I tried an activex
script that would get the filecount in the folder, and if there were more
than 0 files in the folder, then DTS-sUCCESS and on "success" workflow , it
would run the stored procedure, and thus it woould process one file, then
"on completion" the workflow connected it back to the activeX script(thus
looping), which would count the files again. Now if there were 0 files, it
would report DTS_FAILIURE, and I had it set up ,"on failiure" to go to the
next step in the package, but it wouldn't run.
>
Someone mind showing me a ray of light?

looping a query

Can anyone give me a hand on how to loop this query until there are no results left, this is going to be part of a stored procedure by the way.

Code Snippet

declare @.strcharid varchar(21)

set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid

Ken:

Are you using SQL Server 2000 or SQL Server 2005?

|||sql 2000 now, the programs i need to run do not work with 2005 unfortuantely.
|||

My first pass at this might be something like this:

Code Snippet

update tb_user
set strauthority = '255',
BanReason = 'Master Skill Hack',
TermDate = current_timestamp
from tb_user a
join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b
on a.strAccountId = b.strAccountId

update userdata
set loyalty = '0'
where class = '108'
and authority = '1'

I don't feel real good about this solution; will somebody please check me?

|||it returns the error "Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'b'."

my current thought is this, i just need a way to make it rerun until @.count = 0

Code Snippet

CREATE PROCEDURE [MasterHack]
as

begin tran
declare @.strcharid varchar(21)
set @.strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')
declare @.count int
set @.count = (select count(1) struserid from userdata where class = '108' and authority = '1')

if @.count > 0
begin

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @.strcharid)
update userdata set loyalty = '0' where struserid = @.strcharid
end

rollback tran
GO

|||

Thank you, Ken.

Change this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
)
from userData p
where p.class = '108'
and p.authority = '1'
) b

to this:

Code Snippet

join ( select p.strUserId,
( select top 1 q.strAccountId
from ip_tracker q
where p.strUserId = q.strCharId
) as strAccountId
from userData p
where p.class = '108'
and p.authority = '1'
) b

Also, I understand that in your original post that this is not the style of code that you sought; however, Transact SQL is in general a set-based language and works far better if you devise set-based processes rather than record-based processes. My response is an attempt to steer your solution to a set-based solution rather than a record-based solution.

Also, if you are going to be writing stored procedures or functions it is good to get into the habbit of writing set-based or set-oriented solutions rather than record-oriented solutions. Set-oriented / set-based solitions are the preferred choice frequently discussed in the MSDN Transact SQL forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Hopefully, other memembers will come forwared with other forum recommendations.

|||Thank you, is there any site you would suggest for this style?
sql

Loop within a stored procedure

Hi,

Can I have a FOR loop within a stored procedure. Basically I want 2 loop through each user and send them an automatic email using Server Agent.

thanksThere's no FOR loop, but there is a WHILE loop. Look in BOL for full documentation on its use (I don't think I've ever actually used it, except in one or two rare cases where I broke down and used a cursor. Ah, foolish youth!)|||If you want to loop though a dataset, use a cursor:

declare NextRow cursor local for
select EmailAddress
from Table
where ...

open NextRow

and then you can get the rows one by one using fetch:

fetch next from NextRow into @.EmailAddress

Remember to check @.@.fetch_status to see if you're done.

if @.@.fetch_status = -1 -- you're done!
if @.@.fetch_status = -2 -- This row has been deleted.

Does this help?

loop through all stored procedures in database and modify.

I have a need to loop through all stored procedures within a database and modify the procedure if it meets certain criteria. Using .NET I was able to develop a project to loop through the stored procedures, but that's as far as I got. What I need to do is open the stored procedure, search for a certain text string, and change it. Does anybody have a suggestion as to how I can accomplish this? Thanks.

Karen

create the script of all the sp to a single file (which is possible in EM or SSMS) . then replace the string by FIND & REPLACE method .. then run the script in the database, remember to script the permission too...

Madhu

|||

Hmm.

I have two servers with about 100 databases total. Each database uses a single stored procedure that may be different from the ones in the other databases. I wanted to be able to open the stored procedure, search for the string, then modify the text and save the script in an automated way. And I was wondering if SQLDMO can be used through .NET.

|||

You can get the script of this storedprocedure to a file using SQLCMD. but replacing the text u will have to do by .NET program.

read about SQLCMD

SQLCMD -q"SP_HelpText 'Your SP Name'" -o "Outputfile.sql"

Madhu

|||If you are using SQL Server 2000, all of this is available via SQLDMO. You can get a collection of the databases, loop across all of the databases, get a list of procedures, loop across the procedures and extract the definition, then use the string functions in .NET to find and replace. If this is SQL Server 2005, the same capabilities are available with SMO. The samples that ship with SQL Server pretty much have all of the code that you would need.

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.
Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
--
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

loop in stored procedure?

I have a situation where there is one table with parent and child nodes
i.e.
catID, ParentID
i would like to make a query to find out if an entry resides in a certain
category.
for instance, say i have
Root
--Category 1
--Child 1A
--Child 1B
--Child 1C
--Child 1Ca
if i know the CatID for the Child 1Ca node, how can i write a query that is
able to tell me that it's ultimtely a child of the root nde (Category 1)?
Keep in mind that Category 1 is not always the root node in the tree.Rob,
thanks for your help. this is exactly what i needed.
best wishes.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>

loop in stored procedure?

The thing to bear in mind here is that you should start from the bottom and
work up. That way, you won't be checking lots of paths, just the one that th
e
child is in.
If a child node can only exist in one place in the tree, then just use a
variable to hold the current parent, updating it each time with its parent.
Stop when you either get to the root, or the category you're looking for.
If a child node can exist in multiple categories, then use a temporary
table. Populate the table with all the parents of the current node, and then
populate it with the parents of the nodes in the temporary table (but only
the ones you haven't processed yet). Stop either when the populate function
doesn't put any more rows in, or else when you find the one you're after.
Rob
"Fabuloussites" wrote:

> I have a situation where there is one table with parent and child nodes
> i.e.
> catID, ParentID
> i would like to make a query to find out if an entry resides in a certain
> category.
> for instance, say i have
> Root
> --Category 1
> --Child 1A
> --Child 1B
> --Child 1C
> --Child 1Ca
>
> if i know the CatID for the Child 1Ca node, how can i write a query that i
s
> able to tell me that it's ultimtely a child of the root nde (Category 1)?
> Keep in mind that Category 1 is not always the root node in the tree.
>
>
>Whether you choose a stored procedure or a function depends on how you're
using it. If you have a piece of C# code which wants to find out if a
particular item is in a particular category, then use a stored procedure. If
you want to be able to use it in queries, then a function. Here's a function
which returns a bit (either 0 or 1) to indicate if an item is an ancestor in
the tree.
--First create the table and populate it with some samples.
create table fab_tree (parentid int, childid int)
insert into fab_tree values (0,1)
insert into fab_tree values (0,2)
insert into fab_tree values (0,3)
insert into fab_tree values (1,10)
insert into fab_tree values (1,11)
insert into fab_tree values (1,12)
insert into fab_tree values (2,20)
insert into fab_tree values (2,21)
insert into fab_tree values (2,22)
insert into fab_tree values (3,30)
insert into fab_tree values (3,31)
insert into fab_tree values (3,32)
insert into fab_tree values (30,300)
insert into fab_tree values (30,301)
insert into fab_tree values (30,302)
insert into fab_tree values (50,500)
go
create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
begin
declare @.res bit
set @.res = 0
declare @.node int
set @.node = @.childid
--Keep looking until we get to the end, or the parent
while (@.node not in (0, @.parentid))
begin
select @.node = parentid
from fab_tree
where childid = @.node
--If the current child has no parent, then pretend we got to the top of
the tree
if (@.@.rowcount = 0)
set @.node = 0
end
--Check the success condition
if (@.node = @.parentid)
set @.res = 1
return @.res
end
go
select 1,10,dbo.isancestor(1,10)
select 1,20,dbo.isancestor(1,20)
select 3,300,dbo.isancestor(3,300)
select 5,500,dbo.isancestor(5,500)
select 9,700,dbo.isancestor(9,700)
But I'm guessing you probably want something that will find the category of
a particular item. In which case, how about :
create function dbo.getCategory(@.childid int) returns int as
begin
declare @.node int
set @.node = @.childid
declare @.nodeparent int
set @.nodeparent = @.node
--Keep looking until we get to the top of the tree
while (@.nodeparent > 0)
begin
--We need to now check the parent of the current parent
set @.node = @.nodeparent
select @.nodeparent = parentid
from fab_tree
where childid = @.node
--If the current child has no parent, then pretend we got to the top of
the tree
if (@.@.rowcount = 0)
begin
set @.node = 0
set @.nodeparent = 0
end
end
return @.node
end
go
select dbo.getcategory(childid), *
from fab_tree
That way, you can ask within a query for the category of an item in the tree
.
Hope this helps,
Rob
"Fabuloussites" wrote:
> Thanks for the comments. That's my thought process. If i were programmin
g
> in .net of c i would know how to do it :). In SQL, i'm a newbie. Could
you
> give me some direction? should i do it completely in a stored procedure o
f
> should i use a user defined function? IN my case, each node can only have
> one parent.
>
> "Rob Farley" wrote:
>|||Rob,
Thanks for all of the work. I really appreciate it. i'll update you once I
have some time to work with it.
"Rob Farley" wrote:
> Whether you choose a stored procedure or a function depends on how you're
> using it. If you have a piece of C# code which wants to find out if a
> particular item is in a particular category, then use a stored procedure.
If
> you want to be able to use it in queries, then a function. Here's a functi
on
> which returns a bit (either 0 or 1) to indicate if an item is an ancestor
in
> the tree.
> --First create the table and populate it with some samples.
> create table fab_tree (parentid int, childid int)
> insert into fab_tree values (0,1)
> insert into fab_tree values (0,2)
> insert into fab_tree values (0,3)
> insert into fab_tree values (1,10)
> insert into fab_tree values (1,11)
> insert into fab_tree values (1,12)
> insert into fab_tree values (2,20)
> insert into fab_tree values (2,21)
> insert into fab_tree values (2,22)
> insert into fab_tree values (3,30)
> insert into fab_tree values (3,31)
> insert into fab_tree values (3,32)
> insert into fab_tree values (30,300)
> insert into fab_tree values (30,301)
> insert into fab_tree values (30,302)
> insert into fab_tree values (50,500)
> go
> create function [dbo].[isancestor](@.parentid int, @.childid int) returns bit as
> begin
> declare @.res bit
> set @.res = 0
> declare @.node int
> set @.node = @.childid
> --Keep looking until we get to the end, or the parent
> while (@.node not in (0, @.parentid))
> begin
> select @.node = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> set @.node = 0
> end
> --Check the success condition
> if (@.node = @.parentid)
> set @.res = 1
> return @.res
> end
> go
> select 1,10,dbo.isancestor(1,10)
> select 1,20,dbo.isancestor(1,20)
> select 3,300,dbo.isancestor(3,300)
> select 5,500,dbo.isancestor(5,500)
> select 9,700,dbo.isancestor(9,700)
>
> But I'm guessing you probably want something that will find the category o
f
> a particular item. In which case, how about :
> create function dbo.getCategory(@.childid int) returns int as
> begin
> declare @.node int
> set @.node = @.childid
> declare @.nodeparent int
> set @.nodeparent = @.node
> --Keep looking until we get to the top of the tree
> while (@.nodeparent > 0)
> begin
> --We need to now check the parent of the current parent
> set @.node = @.nodeparent
> select @.nodeparent = parentid
> from fab_tree
> where childid = @.node
> --If the current child has no parent, then pretend we got to the top of
> the tree
> if (@.@.rowcount = 0)
> begin
> set @.node = 0
> set @.nodeparent = 0
> end
> end
> return @.node
> end
> go
> select dbo.getcategory(childid), *
> from fab_tree
> That way, you can ask within a query for the category of an item in the tr
ee.
> Hope this helps,
> Rob
>
> "Fabuloussites" wrote:
>

lookup task running very slow when deal w/big tables

I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.

Thanks

Are you aware of the different caching types of the Lookup Tranformation? by default the lookup caches the whole data set from your lookup table/query; if you are not using a query to limit the number of columns/rows to only the ones the lookup needs, so the cached data is limited to what you really need, this would be a good point to start. Try, if possible, to re-arrange the tasks in your data flow so your lookup transforms are based on the tables with the lesser rows.

You can see more information about lookup tranformation here: http://msdn2.microsoft.com/en-us/library/ms141821.aspx

Lookup transforms may not be the best approach for all cases; if the performace you are getting for having your dataset out of a single query/SP is better than the lookup tasks approcah why would you want to change it?

Thanks

Rafael Salas

|||

Yes, I am setting 500mb Cache size and select 3 integer and 2 varchar(50) fields only, I also try to re-arrange the lookup tables, but since all of them are big, it's limited what I can do by re-arranging them. The reason that I am doing this is to evaluate if it pay to convert our current procedure or not, the main thing that I try to test is performance, it's only make sense if SSIS tasks can out performance stored procedure during our data pull. I wish that SSIS allows input parameters mapping in the reference table TAB. this will allows me to limit number of lookup rows that get load into SSIS. also I found that the OLE DB Source task is very picky w/what kind of SQL script it can run, if you have a sub query that contains parameter, it won't take it, even it's perfectly ok to run in TSQL, this is so bad since sometime you get better performance by putting parms into sub query to limit number of rows before you join to another huge table.

Anyway I think I found a work around, I am currently switched over to use Merge Join task, and it seems to work better. But it still had not out performance current stored procedure. I just hope that some one out there w/similar issue, so I can compare note.

Thanks

|||

by using Merge Join trasnformation , I think you may actually be walking away from any performace gain. By definition Merge Join is an asynchonous trasnformation, so it would use more resources(memory) than the lookup transformation and more likely to have poorer performance; it actually requieres the inputs to be sorted . I think there are good reasons for not having a parameter mapping ability in the reference table tab of the lookup and I just can see that it would requiere to execute a query for every row comming to the lookup input. Actually if you want to test it, you can go to advanced tab and edit the query to include and map your parameters; the performance will hit the floor.

BTW, 500Mb in cache size may no be enough for the volume of data in your lookups.

You can review an interesting paper about SSIS performance at:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Sorry if my post were not helpfull at all

Rafael Salas

|||

Thanks for you responses, it does help. I am just a big disappointed, since all the seminars that I attended seems to advise people to convert their SQL script into SSIS tasks for better maintenance and performance, but I have yet to find a work around that can outperform current stored procedure, since our source schema is fairly complex and required a lot of joins in order to pull data out, I guess you were right that SSIS tasks may not be the best choice for every situation in term of performance. Also, you are right about the Merge Join task, it actually performs 2-3 times worse than current stored procedure, and I guess I will stay w /my stored procedures for now.

Thanks a lot your help

Look-UP

Can someone help with the follow --
I have a table in SQL and I would like to write a procedure to the following
Date 2001 2003 2006 2012
20060131 0.0455 0.0455 0.0446 0.0422
20060130 0.0566 0.0566 0.0758 0.0436
20060129 0.783 0.5642 0.3548 0.2165
in the pocedure you enter the Date (20060130) and then the Index (2006) the
result should be 0.0758
Please feel free to call me with if you have any questions
847-323-7731
ThanksChrismkr wrote:
> Can someone help with the follow --
> I have a table in SQL and I would like to write a procedure to the
> following
>
> Date 2001 2003 2006 2012
> 20060131 0.0455 0.0455 0.0446 0.0422
> 20060130 0.0566 0.0566 0.0758 0.0436
> 20060129 0.783 0.5642 0.3548 0.2165
> in the pocedure you enter the Date (20060130) and then the Index
> (2006) the result should be 0.0758
>
A better table design would be:
Date Index Value
20060131 2001 .0455
20060131 2003 .0455
20060131 2006 .0446
20060131 2012 .0422
20060130 2001 .0566
20060130 2003 .0566
20060130 2006 .0758
etc.
That makes the procedure easy:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from tablename
where date= @.date and Index= @.index
My recommendation would be to change the database design to what I suggested
above. if you cannot do that for some reason, then you can "fold" your
existing table using a union query in a view, like this:
create view folded_data as
select date, 2001 as Index, 2001 from tablename
union all
select date, 2003 as Index, 2003 from tablename
union all
select date, 2006 as Index, 2006 from tablename
union all
select date, 2012 as Index, 2012 from tablename
Then use the folded_data view in your procedure instead of the base table:
create procedure GetIndexValue (
@.date datetime, --it IS a datetime column, right?
@.index int) as
select value from folded_data
where date= @.date and Index= @.index
However, this may not perform well. You may get better performance via
dynamic sql. See Erland's dynamic sql articles here:
http://www.sommarskog.se/index.html
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob Barrows [MVP] wrote:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
>
Sorry, this should read:
create view folded_data as
select date, 2001 as ValueIndex, [2001] as Value from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Also acceptable would be:
create view folded_data (
Date, ValueIndex, Value
) as
select date, 2001 , [2001] from tablename
union all
select date, 2003, [2003] from tablename
union all
select date, 2006, [2006] from tablename
union all
select date, 2012, [2012] from tablename
Why "ValueIndex"? "Index" is a reserved keyword that should be avoided when
naming database objects.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,
Can I call you or can you call me about this,
We are willing to pay the right price to the right person to help us with
this
Thanks
Chris
"Bob Barrows [MVP]" wrote:

> Chrismkr wrote:
> A better table design would be:
> Date Index Value
> 20060131 2001 .0455
> 20060131 2003 .0455
> 20060131 2006 .0446
> 20060131 2012 .0422
> 20060130 2001 .0566
> 20060130 2003 .0566
> 20060130 2006 .0758
> etc.
> That makes the procedure easy:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from tablename
> where date= @.date and Index= @.index
> My recommendation would be to change the database design to what I suggest
ed
> above. if you cannot do that for some reason, then you can "fold" your
> existing table using a union query in a view, like this:
> create view folded_data as
> select date, 2001 as Index, 2001 from tablename
> union all
> select date, 2003 as Index, 2003 from tablename
> union all
> select date, 2006 as Index, 2006 from tablename
> union all
> select date, 2012 as Index, 2012 from tablename
> Then use the folded_data view in your procedure instead of the base table:
> create procedure GetIndexValue (
> @.date datetime, --it IS a datetime column, right?
> @.index int) as
> select value from folded_data
> where date= @.date and Index= @.index
> However, this may not perform well. You may get better performance via
> dynamic sql. See Erland's dynamic sql articles here:
> http://www.sommarskog.se/index.html
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||To claim your prize, just call them up and give them your credit card
number. ;-)
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:upBlcg7OGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Bob Barrows [MVP] wrote:
> Sorry, this should read:
> create view folded_data as
> select date, 2001 as ValueIndex, [2001] as Value from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Also acceptable would be:
> create view folded_data (
> Date, ValueIndex, Value
> ) as
> select date, 2001 , [2001] from tablename
> union all
> select date, 2003, [2003] from tablename
> union all
> select date, 2006, [2006] from tablename
> union all
> select date, 2012, [2012] from tablename
> Why "ValueIndex"? "Index" is a reserved keyword that should be avoided
> when
> naming database objects.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Ah! This explains JT's cryptic message.
Sorry but no. I only work through the newsgroups. I've already got a
full-time job :-)
Bob Barrows
Chrismkr wrote:
> Bob,
> Can I call you or can you call me about this,
> We are willing to pay the right price to the right person to help us
> with this
>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Looking For Technique To Default Execute Permission on any new SPr

Hi,
I'm looking for a way, for when any procedure is created or updated in a
user database, that a particular DB role be granted execute permissions on
the proc.
This role is a: datareader,datawriter, and ddladmin, but can not be
db_owner. I know that I can grant execute en-masse using a variety of T-SQL
techniques, but I would have to do this periodically. Also, adding a GRANT
on the tail-end of the CREATE PROCEDURE would not work for me.
I was beginning to create a trigger on sysobjects for CREATE,UPDATE when the
object type is 'P' (for procedure) to then issue the GRANT, but soon realize
d
that triggers on system tables are not permitted!
Does anyone have any suggestions?
Thanks very much,
SteveTIf you are on SQL Server 2005, you could grant execute
permissions on the schema to the role -
GRANT EXECUTE ON SCHEMA::YourSchema TO YourRole
Otherwise, not much you can do if you can't add a grant
statement to the create procedure script.
-Sue
On Thu, 2 Nov 2006 13:49:02 -0800, SteveT
<SteveT@.discussions.microsoft.com> wrote:

>Hi,
>I'm looking for a way, for when any procedure is created or updated in a
>user database, that a particular DB role be granted execute permissions on
>the proc.
>This role is a: datareader,datawriter, and ddladmin, but can not be
>db_owner. I know that I can grant execute en-masse using a variety of T-SQ
L
>techniques, but I would have to do this periodically. Also, adding a GRANT
>on the tail-end of the CREATE PROCEDURE would not work for me.
>I was beginning to create a trigger on sysobjects for CREATE,UPDATE when th
e
>object type is 'P' (for procedure) to then issue the GRANT, but soon realiz
ed
>that triggers on system tables are not permitted!
>Does anyone have any suggestions?

looking for some hints on SP performance

I'm trying to figure out why a SQL statement will run faster in a query
window then as a stored procedure. In a query window the SQL runs in 2
seconds. As a SP, it runs 5 minutes. This is a bit of a large query with
cross a database select, so I'm not sure of posting it here in the group.
I've looked at Procedure cache seems to be more then enough but how do I
check it to be sure?
I've updates statistics but, that hasn't made any difference.
There are indexes that are being used, so I think that is ok. Unless indexes
have different affects on a interactive query vs. a SP?
I'm open to any other options that I can look at that may help me with this.
Thanks,
JD
does it use variables for it's where clause ?
Show us the sproc
Greg Jackson
PDX, Oregon
|||I suggest you first read up on the difference between constants, parameters and variables. In short:
Constant:
WHERE col = 25
Optimizer know the value is 25 and can determine selectivity.
Parameter to a stored procedure:
WHERE col = @.parm
Optimizer sniffes the value or the parm based on execution when plan is created and estimates
selectivity. Plan is created based on that and re-used (even if not optimal for subsequent
executions). Known as parameter sniffing.
Variable:
DECLARE @.var int
WHERE col = @.var
Optimizer doesn't know value. Can possibly use density ("we have an average of x rows with the same
value") or worst case just hard-wired estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
I suggest you Google on Parameter sniffing as a start.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe D" <jkdriscoll@.qg.com> wrote in message news:d96mem$2ame$1@.sxnews1.qg.com...
> I'm trying to figure out why a SQL statement will run faster in a query window then as a stored
> procedure. In a query window the SQL runs in 2 seconds. As a SP, it runs 5 minutes. This is a bit
> of a large query with cross a database select, so I'm not sure of posting it here in the group.
> I've looked at Procedure cache seems to be more then enough but how do I check it to be sure?
> I've updates statistics but, that hasn't made any difference.
> There are indexes that are being used, so I think that is ok. Unless indexes have different
> affects on a interactive query vs. a SP?
> I'm open to any other options that I can look at that may help me with this.
> Thanks,
> JD
>
|||Ok - here is the sproc:
SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.QG_ScalableUsageDetail
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
END
go
CREATE PROCEDURE dbo.QG_ScalableUsageDetail
(
@.CATALOGID INT,
@.START_DATE INT,
@.END_DATE INT,
@.DAYSINREPORT INT = 0,
@.SHOWWEBAPPS INT = 1,
@.USECATEGORIES INT = 0,
@.CATEGORYID INT = -999,
@.MAXDAYOFWEEK INT = 7,
@.BUSINESS_GROUP VARCHAR(50) = '',
@.DEPT_NM VARCHAR(30) = '',
@.LOCATION_NM VARCHAR(30) = '',
@.USERS VARCHAR(8000) = ''
)
AS
SET NOCOUNT ON
SET @.BUSINESS_GROUP = @.BUSINESS_GROUP + '%'
SET @.LOCATION_NM = @.LOCATION_NM + '%'
SET @.DEPT_NM = @.DEPT_NM + '%'
SET @.DAYSINREPORT =
DATEDIFF(d,CONVERT(DATETIME,CONVERT(VARCHAR(8),@.ST ART_DATE),101),CONVERT(DATETIME,CONVERT(VARCHAR(8) ,@.END_DATE),101))
+1
-- BUILD A TABLE OF VAXNAMES BASED ON END-USERS SELECTION OF REPORT
FILTERING CHOICES
DECLARE @.TEMP1 TABLE (VAXNAME VARCHAR(255))
BEGIN
IF (LEN(@.USERS) > 0)
BEGIN
WHILE (CHARINDEX(',', @.USERS) <>0)
BEGIN
INSERT INTO @.TEMP1
VALUES
(CONVERT(VARCHAR(255),SUBSTRING(@.USERS,1,CHARINDEX (',',@.USERS)-1)))
SET @.USERS = SUBSTRING(@.USERS,CHARINDEX(',',@.USERS)+1,LEN(@.USER S))
END
END
ELSE
BEGIN
INSERT INTO @.TEMP1
SELECT E.USRNM
FROM QUAD0022.dbo.EMPLOYEE_VW2 AS E
INNER JOIN QUAD0022.dbo.LOCATION AS L
ON E.LOC_NBR=L.LOCATION_NUMBER
INNER JOIN QUAD0022.dbo.DEPARTMENT AS D
ON E.DEPT_NBR=D.DEPT_NBR
INNER JOIN QUAD0022.dbo.BUSINESS_GROUP AS BG
ON D.BUS_GRP_ID=BG.BUS_GRP_ID
WHERE BG.BUS_GRP_NM LIKE LTRIM(RTRIM(@.BUSINESS_GROUP))
AND D.DEPT_NM LIKE LTRIM(RTRIM(@.DEPT_NM))
AND L.[NAME] LIKE LTRIM(RTRIM(@.LOCATION_NM))
AND ((E.USRNM != 'NULL') OR (E.USRNM IS NOT NULL) OR (E.USRNM != ''))
END
END
SELECT Resources.ResourceID ResourceID
, Resources.ResourceName ResourceName
,
SUBSTRING(Resources.LogonName,(CHARINDEX('\',Resou rces.LogonName)+1),LEN(Resources.LogonName)-CHARINDEX('\',Resources.LogonName))
Username
, Apps.AppID AppID, Apps.AppName AppName
, GetUsageData.TotalUsageTime TotalUsageTime
, GetUsageData.LastUsageDate LastUsageDate
, GetUsageData.TotalUsageDays TotalUsageDays
, GetUsageData.TotalUsageTime / case when @.DAYSINREPORT = 0 then -1 else
convert(float, @.DAYSINREPORT) end AverageHrsPerDay
, case when (GetUsageData.TotalUsageTime is null and
ResourceGetUsageData.ResourceTotalUsageTime is not null) then
ResourceGetUsageData.ResourceTotalUsageTime
else GetUsageData.TotalUsageTime /
ResourceGetUsageData.ResourceTotalUsageTime end PercentActiveTime
From
(SELECT UA.UserID ResourceID
, case when convert(float, SUM(UA.ActiveDay)) = 0 then 1
else convert(float, SUM(UA.ActiveDay)) end ResourceTotalUsageTime
FROM SSISurvey.dbo.UserAggregate UA
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UA.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UA.LogonDate BETWEEN @.START_DATE AND @.END_DATE
AND UA.DayofWeek <= @.MAXDAYOFWEEK
GROUP BY UA.UserID) AS ResourceGetUsageData
Left Join
(SELECT UU.UserID ResourceID
, UU.ProgramGroupID AppID
, convert(float, SUM(UU.ActiveDay)) TotalUsageTime
, MAX(UU.UsageDate) LastUsageDate
, COUNT(distinct UU.UsageDate) TotalUsageDays
FROM SSISurvey.dbo.UserUsageProgramGroup UU
INNER JOIN SSISurvey.dbo.SSIUser AS SSIU
ON UU.UserId=SSIU.UserId
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(SSIU.LogonName,(CHARINDEX('\',SSIU.Logon Name)+1),LEN(SSIU.LogonName)-CHARINDEX('\',SSIU.LogonName))=T1.VAXNAME
WHERE UU.Usagedate BETWEEN @.START_DATE AND @.END_DATE
AND UU.DayofWeek <= @.MAXDAYOFWEEK
AND UU.ActiveDay > 0
AND UU.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
GROUP BY UU.UserID, UU.ProgramGroupID
) AS GetUsageData
ON GetUsageData.ResourceID = ResourceGetUsageData.ResourceID
Right Join
(SELECT DISTINCT U.UserID ResourceID, U.UserName ResourceName, U.LogonName
FROM SSISurvey.dbo.SSIUser AS U
INNER JOIN @.TEMP1 AS T1
ON
SUBSTRING(U.LogonName,(CHARINDEX('\',U.LogonName)+ 1),LEN(U.LogonName)-CHARINDEX('\',U.LogonName))=T1.VAXNAME)
AS Resources
ON ResourceGetUsageData.ResourceID = Resources.ResourceID
Left Join
(SELECT DISTINCT PG.ProgramGroupID AppID
, PG.Name AppName FROM SSISurvey.dbo.ProgramGroup PG
WHERE PG.ProgramGroupID in
(select distinct PG.ProgramGroupID
from SSISurvey.dbo.ProgramGroup PG
left join SSISurvey.dbo.SWCategoryMembership SWCM on PG.ProgramGroupID =
SWCM.ProgramGroupID
where (isnull(SWCM.ProgramGroupID, -666) = case when @.CATALOGID = 6 then
isnull(SWCM.ProgramGroupID, -666) else -666 end
and isnull(SWCM.CategoryID, -666) = case when @.USECATEGORIES = 1 then
@.CATEGORYID else isnull(SWCM.CategoryID, -666) end
and PG.ProgramGroupType = @.CATALOGID)
or PG.ProgramGroupType = case when @.SHOWWEBAPPS = 1 then 1 else -1 end)
) AS Apps
ON GetUsageData.AppID = Apps.AppID
WHERE TotalUsagetime > 0
ORDER BY ResourcesAndApps.ResourceName, apps.appname,
ResourcesAndApps.ResourceID,TotalUsageTime DESC
go
IF OBJECT_ID('dbo.QG_ScalableUsageDetail') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.QG_ScalableUsageDetail >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.QG_ScalableUsageDetail >>>'
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OAKSb2adFHA.2556@.TK2MSFTNGP10.phx.gbl...
> does it use variables for it's where clause ?
> Show us the sproc
>
> Greg Jackson
> PDX, Oregon
>
|||Thank you, I will.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEipG4adFHA.3376@.TK2MSFTNGP10.phx.gbl...
>I suggest you first read up on the difference between constants, parameters
>and variables. In short:
> Constant:
> WHERE col = 25
> Optimizer know the value is 25 and can determine selectivity.
> Parameter to a stored procedure:
> WHERE col = @.parm
> Optimizer sniffes the value or the parm based on execution when plan is
> created and estimates selectivity. Plan is created based on that and
> re-used (even if not optimal for subsequent executions). Known as
> parameter sniffing.
> Variable:
> DECLARE @.var int
> WHERE col = @.var
> Optimizer doesn't know value. Can possibly use density ("we have an
> average of x rows with the same value") or worst case just hard-wired
> estimates ("BETWEEN returns 25 %, equals returns 10%" etc).
> I suggest you Google on Parameter sniffing as a start.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:d96mem$2ame$1@.sxnews1.qg.com...
>