Help with a return value stored procedure that will be used in a calculation

Hi, here is my dilema. I am trying to create an application for overtime recruitment. Basically, I will assign the total hours needed in a variable for each individual time block. What I want to do is show the agents how much time is left on each block based on a selection from a drop down. What I've done so far is create a procedure that sums up the total hours signed up for based on a parameter which will be provided by the drop down menu. I'm very green to asp.net still so I'm having problems using the value from the procedure in a calculation that will subtract the hours taken from the alloted hours. Here is what I've done so far. If anyone has any idea on how I can do this or can point me in the right direction. Maybe I'm taking the wrong approach? Any help would be appreciated. Thanks!
---------------------- VB.Net Code

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

lblNameV.Text = (User.Identity.Name)

':::::Declare Department Variables

Dim HoursAsInteger

Dim Sunday2AsInteger

Sunday2 = 10

':::::Fill Supervisor Drop Down

IfNot Page.IsPostBackThen

'Supervisor

Dim cmdSupervisorAs SqlCommand =New SqlCommand("SUPERVISOR", SqlConnection1)

cmdSupervisor.CommandType = CommandType.StoredProcedure

SqlConnection1.Open()

Dim drSupervisorAs SqlDataReader

drSupervisor = cmdSupervisor.ExecuteReader()

lstSupervisor.DataSource = drSupervisor

lstSupervisor.DataTextField = "Supervisor"

lstSupervisor.DataBind()

drSupervisor.Close()

SqlConnection1.Close()

EndIf

':::::Find Agent Name

Dim dsAsNew DataSet

Dim workparamAsNew SqlParameter("@.KMSID", System.Data.SqlDbType.Char)

workparam.Direction = ParameterDirection.Input

workparam.Value = (User.Identity.Name)

Dim danameAsNew SqlDataAdapter

daname.SelectCommand =New SqlCommand

daname.SelectCommand.Connection = SqlConnection1

daname.SelectCommand.CommandText = "NTAGENTNAME"

daname.SelectCommand.CommandType = CommandType.StoredProcedure

daname.SelectCommand.Parameters.Add(workparam)

ds =New DataSet

daname.Fill(ds)

If ds.Tables(0).Rows.Count = "0"Then

lblFName.Text = ""

Else

lblFName.Text = ds.Tables(0).Rows(0)("name")

EndIf

':::::Fill Drop Down With Available Shifts

If dlDept.SelectedValue = "Select a Department"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Video"Then

Dim cmdVideoAs SqlCommand =New SqlCommand("AVAILABLEOTVIDEO", SqlConnection2)

cmdVideo.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drVideoAs SqlDataReader

drVideo = cmdVideo.ExecuteReader()

dlShift.DataSource = drVideo

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drVideo.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Sales"Then

Dim cmdSalesAs SqlCommand =New SqlCommand("AVAILABLEOTSALES", SqlConnection2)

cmdSales.CommandType = CommandType.StoredProcedure

SqlConnection2.Open()

Dim drSalesAs SqlDataReader

drSales = cmdSales.ExecuteReader()

dlShift.DataSource = drSales

dlShift.DataTextField = "Shift"

dlShift.DataBind()

drSales.Close()

SqlConnection2.Close()

ElseIf dlDept.SelectedValue = "Retention"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 1"Then

dlShift.Items.Clear()

ElseIf dlDept.SelectedValue = "Tier 2"Then

dlShift.Items.Clear()

EndIf

EndSub

PrivateSub dlShift_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles dlShift.SelectedIndexChanged

Dim AvailAsNew SqlCommand("AVAILABLEOT", SqlConnection2)

Avail.CommandType = CommandType.StoredProcedure

Dim workparam1AsNew SqlParameter("@.Shift", System.Data.SqlDbType.Char)

workparam1.Direction = ParameterDirection.Input

workparam1.Value = (dlShift.SelectedValue)

Dim TimeAsNew SqlParameter("@.Return_Value", SqlDbType.Int)

Time.Direction = ParameterDirection.ReturnValue

Avail.Parameters.Add(workparam1)

Avail.Parameters.Add(Time)

SqlConnection2.Open()

Dim readerAs SqlDataReader = Avail.ExecuteReader()

SqlConnection2.Close()

Dim retValParamAsInteger = Convert.ToInt32(Time.Value)

Label1.Text = retValParam

EndSub
---------------------- Stored Proc
CREATE PROCEDURE AVAILABLEOT

(
@.Shift [varchar](250)
)

AS

SELECT SUM(HoursRequested) AS Hours
FROM [TBL: OT]
WHERE (ShiftRequested = @.Shift)
GO

In VB most of employee time related code is in the link below including stored procedures. Hope this helps.
http://asp.net/TimeTrackerStarterKit/Docs/Docs.htm

Help with a list variable

I have made the following test stored proc that all it does right now
is accept a string variable that will be a list ie. :5,9,6,13. Right
now all the SP is doing is creating a temp table to store the values in
the list, then looping through the list and inserting the values into
the temp table, then selecting all the records from teh temp table.
It's WORKING however i must have done something wrong because it's not
inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
will get inserted, not the 13. if I pass in the list like this
"5,9,6,13," with an extra comma at the end, they all get inserted fine
but thats not how the strings will be coming in. Here's what I have so
far:

CREATE PROCEDURE sp_searchTaskTest

-- in params
@.strAssignedTovarchar (200) = NULL

AS

CREATE TABLE #tblAssignedTo (
strAssignedToIDvarchar(10)
)

-- initialize variables
DECLARE @.lengthOfString int
DECLARE @.startingPosition int
DECLARE @.parseString1 int
DECLARE @.strAssignedToID varchar(10)

SET @.startingPosition = 0
SELECT @.parseString1 = CHARINDEX (',', @.strAssignedTo,1)
WHILE ( @.parseString1 > 0 )
BEGIN
SELECT @.parseString1 = CHARINDEX (',',
@.strAssignedTo,@.startingPosition)
SET @.lengthOfString = @.parseString1 - @.startingPosition
IF @.lengthOfString > 0
BEGIN
SET @.strAssignedToID = SUBSTRING(@.strAssignedTo, @.startingPosition,
@.lengthOfString)
SET @.startingPosition = @.parseString1 + 1
END
ELSE
BEGIN
SET @.parseString1 = 0
SET @.strAssignedToID = ''
END
IF @.strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAssignedToID)
VALUES(@.strAssignedToID)
END
END

SELECT * FROM #tblAssignedTo
GOhi
looks like u need to make some changes in the code.

else what u can do is,

@.strAssignedTo = @.strAssignedTo + ','

in the first line of the SP. This is not correct but a quick fix

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On 3 Aug 2005 10:38:43 -0700, Erich93063 wrote:

>I have made the following test stored proc that all it does right now
>is accept a string variable that will be a list ie. :5,9,6,13. Right
>now all the SP is doing is creating a temp table to store the values in
>the list, then looping through the list and inserting the values into
>the temp table, then selecting all the records from teh temp table.
>It's WORKING however i must have done something wrong because it's not
>inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
>will get inserted, not the 13. if I pass in the list like this
>"5,9,6,13," with an extra comma at the end, they all get inserted fine
>but thats not how the strings will be coming in. Here's what I have so
>far:

Hi Erich,

http://www.sommarskog.se/arrays-in-sql.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with "IN" in stored procedure.

I have a stored procedure which is performing a search against a "task"
table. I would like to pass in a variable called @.strAssignedTo which
will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
without this variable would be something like:

WHERE intAssignedTo IN (5,6,10)

but when I try to do:

WHERE intAssignedTo IN (@.strAssignedTo)

I get an error saying "cannot convert strAssignedTo to an integer"

What's the proper way to do this? THANKS[posted and mailed, please reply in news]

(erich93063@.gmail.com) writes:
> I have a stored procedure which is performing a search against a "task"
> table. I would like to pass in a variable called @.strAssignedTo which
> will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
> without this variable would be something like:
> WHERE intAssignedTo IN (5,6,10)
> but when I try to do:
> WHERE intAssignedTo IN (@.strAssignedTo)
> I get an error saying "cannot convert strAssignedTo to an integer"

Which is because there is no macro expansion going on here. Keep in
mind that you can say things like

WHERE x IN (@.a, @.b, @.c)

Hm, there is actually a macro expansion going on here, since the above
is internally rewritten to

WHERE x = @.a OR x = @.b OR x = @.c

Anyway, for how to this, have a look at
http://www.sommarskog.se/arrays-in-...ist-of-integers.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply but I couldnt make heads or tails of that
article. It looked like I needed to make a user defined function maybe?
(iter_intlist_to_table )? which I did but then when I tried to do:

SELECT ...
FROM tblTask
WHERE ...
AND intAssignedTo IN
(iter_intlist_to_table(@.strAssignedTo))

I got an error saying that the function "iter_intlist_to_table" did not
exist. I'm fairly new to stored procedures and SQL in general so that
article was a bit over my head.|||Erich93063 (erich93063@.gmail.com) writes:
> Thanks for your reply but I couldnt make heads or tails of that
> article. It looked like I needed to make a user defined function maybe?

Yes, that is what the gist of. (There are other methods, but using a
UDF is the best.)

> (iter_intlist_to_table )? which I did but then when I tried to do:
> SELECT ...
> FROM tblTask
> WHERE ...
> AND intAssignedTo IN
> (iter_intlist_to_table(@.strAssignedTo))
> I got an error saying that the function "iter_intlist_to_table" did not
> exist. I'm fairly new to stored procedures and SQL in general so that
> article was a bit over my head.

Well, it is easier, if you mimick the example in the article, rather
than trying your own syntax. That's a table-valued function, and
you use a table-valued function just like you use a table. The example
uses JOIN, although in retrospect, I should probably have used EXISTS
instead:

CREATE PROCEDURE get_product_names_iter @.ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
WHERE EXISTS (SELECT *
FROM iter_intlist_to_table(@.ids) i
WHERE P.ProductID = i.number)
go

But that's a matter of style only.

Some parts of the article, for instance all the performance tests, are
certainly above novice level. But that is also why I gave a direct
link to a function and example on how to use it.

I suggest that you copy the function, and play with the example in
the article. No way is better to learn, than getting your hands on it
yourself. May take little longer for the actual task at hand, but next
time you need it, you know it better.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
week of any language class.

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter;
it is not code. This is not just an SQL problem; this is a basic
misunderstanding of programming of principles.

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice.

3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):

CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions>)
AND <more predicates>;
<more code>;
END;

3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.

Help variable in SP

Dear All

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

( error INSERT INTO @.tableName, Do not find @.tableName, must be declare.)

help thanks

William

It appears that you first create @.TableName as a scalar variable, and then you attempt to insert into it as though it were a table variable.

If that properly reflects your intentions, I suggest that you first declare @.TableName as a table variable, with the columns properly defined -then your insert should work.

|||

You are trying to insert the values on the table which is given on the runtime. Typically it is a dynamic insert query.

Use the following query..

Code Snippet

CREATE PROCEDURE test AS

Declare @.tableName varchar(100)

Set @.tableName='ABCD'

Exec ('INSERT INTO ' + @.tableName + '(A, B, C, E, F,G,H)

SELECT

A, B, C, D,

COUNT(*) AS G,

CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J

FROM

dbo.EFG

GROUP BY XXXX

ORDER BY XXXX');

|||How to do?|||Thanks all|||

CREATE PROCEDURE test AS
declare @.tableName varchar(100)
set @.tableName='ABCD'

INSERT INTO @.tableName
(A, B, C, E, F,G,H)
SELECT
A, B, C, D,
COUNT(*) AS G,
CONVERT(varchar(10), RecDate, 121), Left(TransID, 2) AS J
FROM
dbo.EFG
GROUP BY

ORDER BY
XXXX

NOTE: You MUST supply the correct datatypes below. This is a suggested format to DECLARE a table variable and then populate that table variable.

Code Snippet

CREATE PROCEDURE dbo.MyTestProcedure
AS
BEGIN
DECLARE @.MyTable table
( ColA datatype,
ColB datatype,
ColC datatype,
ColD datatype,
ColE datatype,
ColF datatype,
ColG datatype
)

INSERT INTO @.MyTable
SELECT Col1,
Col2,
Col3,
Col4,
COUNT(*),
CONVERT(varchar(10), RecDate, 121),
Left(TransID, 2)
FROM dbo.EFG
GROUP BY
ORDER BY

SELECT *
FROM @.MyTable

END

Help required for Splitting up string variable using comma separator

I need a help in SQL Server 2000.

I am having a string variable in the format like -- (1,23,445,5,12)

I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.

Can anyone help me out in splitting the variable using the comma separator...

You can just use the split command e.g.

Dim sAs String ="1,23,445,5,12"Dim splitAs String() = s.Split(",")For Each itemAs String In split Response.Write("Item: " & item &"<br>")Next

|||

I doesnt want this in VB.NET.

I want the same using SQL query in SQL Server 2000.

|||You'll have to create a function to do this. Here's a starting point:http://www.madprops.org/cs/blogs/mabster/archive/2005/12/05/T_2D00_SQL-to-Split-a-varchar-into-Words.aspx|||

Oh, sorry. If you want to do this in SQL, it's a bit harder as it doesn't really have built in string manipulation functions. If you were using a later version of SQL Server you could have registered that .NET code as a CLR function but as you ar using SQL Server 2000, you will have to do something like this:

CREATE PROCEDURE SplitString
@.yourStringvarchar(100)
AS
BEGIN
DECLARE @.StringCountint, @.mycountint, @.mystrlenint
DECLARE @.myvalvarchar(100)
set @.StringCount=Len(@.yourString)
set @.mycount=1
if (CHARINDEX(',',@.yourString,1)=0)
print @.yourString
WHILE (CHARINDEX(',',@.yourString,1)<>0)
BEGIN
if @.mycount=1
set @.myval=substring(@.yourString,@.mycount,CHARINDEX('^',@.yourString,1)-1)
print @.myval
set @.yourString =substring(@.yourString,Len(@.myval)+2,Len(@.yourString))
set @.StringCount= @.StringCount -1
if (CHARINDEX(',',@.yourString,1)=0)
print ,@.yourString
END
end
GO

Help reg Watch Window

hi frnds,

i want to view the runtime variable value in the watch window while the package is running,

how i will get that watch window. i searched all the menus but i didnt get it.

im using Microsoft Visual Studio 2005 for SSIS package.

To do this you, first need to be stopped on a breakpoint. The Watch window is only valid during a break point, so whilst it may be visible whilst running, the values are not updated in real-time.

So set a breakpoint. When stopped, got to the Variables window, click to select and then drag the variable to the watch window. The variables will be shown in the watch window with the current value. The variables will stay in the watch window for the duration of your working session, so you don't have to keep dragging them in everytime youi hit a breakpoint.