Stored Procedure Loop Not Working, Please Advise, Code Attached
Apr 13, 2008
This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ?
Thanks so much.
CREATE PROCEDURE Table_1TT_1T
(@PartNo varchar(20),
@Wkorder varchar(10),
@Setup datetime,
@Line smallint,
@TT integer,
@Tester smallint,
@LT1 integer,
@LT2 integer,
@LT3 integer,
@LT4 integer,
@LT5 integer,
@LT6 integer,
@LT7 integer,
@LT8 integer,
@LT9 integer,
@LT10 integer,
@LT11 integer,
@LT12 integer,
@LT13 integer,
@LT14 integer,
@LT15 integer,
@LT16 integer,
@LT17 integer,
@LT18 integer,
@LT19 integer,
@LT20 integer,
@LT21 integer,
@LT22 integer,
@LT23 integer,
@LT24 integer,
@LT25 integer,
@LT26 integer,
@LT27 integer,
@LT28 integer,
@LT29 integer,
@LT30 integer,
@LT31 integer,
@LT32 integer,
@LT33 integer,
@LT34 integer,
@LT35 integer,
@LT36 integer,
@UnitFound integer OUT,
@parameters_LamType varchar(50) OUT,
@parameters_Shunt real OUT,
@parameters_ShuType varchar(50) OUT,
@parameters_Stack real OUT,
@parameters_Steel varchar(50) OUT,
@Partno11 varchar(20) OUT,
@Wkorder11 varchar(10) OUT,
@Partno12 varchar(20) OUT,
@Wkorder12 varchar(10) OUT,
@Partno24 varchar(20) OUT,
@Wkorder24 varchar(10) OUT,
@Partno29 varchar(20) OUT,
@Wkorder29 varchar(10) OUT,
@Partno34 varchar(20) OUT,
@Wkorder34 varchar(10) OUT,
--@DL1 integer OUT,
--@DL2 integer OUT,
--@DL3 integer OUT,
--@DL4 integer OUT,
--@DL5 integer OUT,
--@DL6 integer OUT,
--@DL7 integer OUT,
--@DL8 integer OUT,
--@DL9 integer OUT,
--@DL10 integer OUT,
@DL11 integer OUT,
@DL12 integer OUT,
--@DL13 integer OUT,
--@DL14 integer OUT,
--@DL15 integer OUT,
--@DL16 integer OUT,
--@DL17 integer OUT,
--@DL18 integer OUT,
--@DL19 integer OUT,
--@DL20 integer OUT,
--@DL21 integer OUT,
--@DL22 integer OUT,
--@DL23 integer OUT,
@DL24 integer OUT,
--@DL25 integer OUT,
--@DL26 integer OUT,
--@DL27 integer OUT,
--@DL28 integer OUT,
@DL29 integer OUT,
--@DL30 integer OUT,
--@DL31 integer OUT,
--@DL32 integer OUT,
--@DL33 integer OUT,
@DL34 integer OUT)
--@DL35 integer OUT,
--@DL36 integer OUT)
AS
SET @Tester = 1
WHILE @Tester < 36
BEGIN
Set @Line = (Select Line from dbo.location where Tester = @Tester)
IF @Line = 453
BEGIN
If @Tester = 1 BEGIN SET @LT1 = 453 END
If @Tester = 2 BEGIN SET @LT2 = 453 END
If @Tester = 3 BEGIN SET @LT3 = 453 END
If @Tester = 4 BEGIN SET @LT4 = 453 END
If @Tester = 5 BEGIN SET @LT5 = 453 END
If @Tester = 6 BEGIN SET @LT6 = 453 END
If @Tester = 7 BEGIN SET @LT7 = 453 END
If @Tester = 8 BEGIN SET @LT8 = 453 END
If @Tester = 9 BEGIN SET @LT9 = 453 END
If @Tester = 10 BEGIN SET @LT10 = 453 END
If @Tester = 11 BEGIN SET @LT11 = 453 END
If @Tester = 12 BEGIN SET @LT12 = 453 END
If @Tester = 13 BEGIN SET @LT13 = 453 END
If @Tester = 14 BEGIN SET @LT14 = 453 END
If @Tester = 15 BEGIN SET @LT15 = 453 END
If @Tester = 16 BEGIN SET @LT16 = 453 END
If @Tester = 17 BEGIN SET @LT17 = 453 END
If @Tester = 18 BEGIN SET @LT18 = 453 END
If @Tester = 19 BEGIN SET @LT19 = 453 END
If @Tester = 20 BEGIN SET @LT20 = 453 END
If @Tester = 21 BEGIN SET @LT21 = 453 END
If @Tester = 22 BEGIN SET @LT22 = 453 END
If @Tester = 23 BEGIN SET @LT23 = 453 END
If @Tester = 24 BEGIN SET @LT24 = 453 END
If @Tester = 25 BEGIN SET @LT25 = 453 END
If @Tester = 26 BEGIN SET @LT26 = 453 END
If @Tester = 27 BEGIN SET @LT27 = 453 END
If @Tester = 28 BEGIN SET @LT28 = 453 END
If @Tester = 29 BEGIN SET @LT29 = 453 END
If @Tester = 30 BEGIN SET @LT30 = 453 END
If @Tester = 31 BEGIN SET @LT31 = 453 END
If @Tester = 32 BEGIN SET @LT32 = 453 END
If @Tester = 33 BEGIN SET @LT33 = 453 END
If @Tester = 34 BEGIN SET @LT34 = 453 END
If @Tester = 35 BEGIN SET @LT35 = 453 END
END
SET @Tester = @Tester + 1
END
SELECT
@parameters_LAMTYPE = LAMTYPE,
@parameters_SHUNT = SHUNT,
@parameters_SHUTYPE = SHUTYPE,
@parameters_STACK = STACK,
@parameters_STEEL = STEEL
FROM DBO.PARAMETERS A
INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF
WHERE B.PARTNO = @PARTNO
SET @UnitFound = @@rowcount
IF @UnitFound = 0
BEGIN
SELECT
@parameters_LAMTYPE = LAMTYPE,
@parameters_SHUNT = SHUNT,
@parameters_SHUTYPE = SHUTYPE,
@parameters_STACK = STACK,
@parameters_STEEL = STEEL
FROM DBO.PARAMETERS
WHERE PARTNO = @PARTNO
SET @UnitFound = @@rowcount
END
--IF @LT1 = @Line BEGIN SET @DL1 = 1 END
--IF @LT2 = @Line BEGIN SET @DL2 = 1 END
--IF @LT3 = @Line BEGIN SET @DL3 = 1 END
--IF @LT4 = @Line BEGIN SET @DL4 = 1 END
--IF @LT5 = @Line BEGIN SET @DL5 = 1 END
--IF @LT6 = @Line BEGIN SET @DL6 = 1 END
--IF @LT7 = @Line BEGIN SET @DL7 = 1 END
--IF @LT8 = @Line BEGIN SET @DL8 = 1 END
--IF @LT9 = @Line BEGIN SET @DL9 = 1 END
--IF @LT10 = @Line BEGIN SET @DL10 = 1 END
IF @LT11 = 453 BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END
--IF @LT11 = @Line BEGIN SET @DL11 = 1 END
IF @LT12 = 453 BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END
--IF @LT13 = @Line BEGIN SET @DL13 = 1 END
--IF @LT14 = @Line BEGIN SET @DL14 = 1 END
--IF @LT15 = @Line BEGIN SET @DL15 = 1 END
--IF @LT16 = @Line BEGIN SET @DL16 = 1 END
--IF @LT17 = @Line BEGIN SET @DL17 = 1 END
--IF @LT18 = @Line BEGIN SET @DL18 = 1 END
--IF @LT19 = @Line BEGIN SET @DL19 = 1 END
--IF @LT20 = @Line BEGIN SET @DL20 = 1 END
--IF @LT21 = @Line BEGIN SET @DL21 = 1 END
--IF @LT22 = @Line BEGIN SET @DL22 = 1 END
--IF @LT23 = @Line BEGIN SET @DL23 = 1 END
IF @LT24 = 453 BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END
--IF @LT25 = @Line BEGIN SET @DL25 = 1 END
--IF @LT26 = @Line BEGIN SET @DL26 = 1 END
--IF @LT27 = @Line BEGIN SET @DL27 = 1 END
--IF @LT28 = @Line BEGIN SET @DL28 = 1 END
IF @LT29 = 453 BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END
--IF @LT30 = @Line BEGIN SET @DL30 = 1 END
--IF @LT31 = @Line BEGIN SET @DL31 = 1 END
--IF @LT32 = @Line BEGIN SET @DL32 = 1 END
--IF @LT33 = @Line BEGIN SET @DL33 = 1 END
IF @LT34 = 453 BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END
--IF @LT35 = @Line BEGIN SET @DL35 = 1 END
--IF @LT36 = @Line BEGIN SET @DL36 = 1 END
GO
View 1 Replies
ADVERTISEMENT
Jul 27, 2005
Little rusty on my SQL but here is what I need. Currently have a stored procedure that returns numerical data for 8 different element readings. ( Carbon, Silicon, Sulfur, etc ). This data is then put into a datagrid on an asp.net page and shows the readings for each test.On my asp .net page... they only want data that is out of the specified ranges to be shown ( EX: > 1.6 AND < 2.0 ). Currently it is setup on the page to change the font color to red of data of its range but now they don't want data to be shown at all if for that one test everything is in its range.So how could I write my Where clause to check the 8 different element ranges and only need one of them to be true in order to show.Could I use an If / Then statement?Any help is appreciated.... Sorry this is quickly done but got to have it done soon.
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Feb 24, 2008
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
View 2 Replies
View Related
Aug 8, 2007
hi,
i have installed SQLEXPRESS (SQL Server 9.0.3042) on my windows ultimate laptop. of late am facing this issue of database. this database is from SQl 7.0 Desktop edition(the one that i use in my office machine with Xp SP2). As such it works fine in SQL 2000 server after attachment (for test).
i have run SQLEXPRESS as administrator to make/attach new database. i can attach the database easily and can even view tables and other stuffs inside database in administrator mode. the issue pops up when i run SQLEXPRESS in normal mode. when i try to open and view the same DB's content, error pops up stating
"The database XXX is not accessible. (Micorosoft.sqlserver.express.objectExplorer)"
i cannot access this DB via VB 6 form as it dosnt show up at all in database list when tried to connect via ADODC.
i want to make this DB accessibility as normal operation, not previliged only to administrator as application run on normal mode only.
the issue dosnt arise when i locally make a new database as it can be accessed when run in normal mode also. why does this happen to database that is attached and not to ones locally made.
pls guide me as i usually do my works at home and making whole database again will surely take lots of pain. why not it just attach then start working, not asking for preveliged mode.
thanks in adv
Nyaken
View 3 Replies
View Related
May 19, 2006
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
View 6 Replies
View Related
Jan 18, 2008
I have a table that is basically set up so there is an employee_id, field_id, and a value... it looks sort of like this (in reality there are over 450 different employee ID's)
employee_id field_id value
100 1 Brian
100 2 617-555-5555
100 3 Boston Office
101 1 Mary
102 2 617-666-6666
101 3 New york office
I want to loop thru this table so "for each" distinct employee ID, I can do an insert statement into another table where it is setup a little cleaner
I know how to declare the variables, and set each variable = to the correct value, and how to do the INSERT once, but I am not 100% sure how to set up the loop so it will do each individual employee_ID.
Any suggestions?
View 9 Replies
View Related
Feb 21, 2008
How do I write a stored procedure to loop through all the records to see if duedate is passed today's date then send an email to those users
Dim duedateDim @emailDim ocdoEmail As New Object
while duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101') (sending the email)ocdoEmail = Server.CreateObject("CDO.Message")
ocdoEmail.To = @email
ocdoEmail.From =
ocdoEmail.CC =
ocdoEmail.Subject = "Your Item is passed due"ocdoEmail.HTMLBody = " was shipped to you on " & ShipDateTxt.Text & " and due on " & DueDateTxt.Text
ocdoEmail.send()
Don't know which loop I should use and how to set it up.
Thanks!
dim @duedatedim @email
while
View 4 Replies
View Related
Jul 31, 2014
I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data.
Example:
table1 data
101/03/2014
201/10/2014
301/17/2014
401/24/2014
501/31/2014
602/07/2014
702/14/2014
802/21/2014
table2 data should look after insert compelete.
col1 col2 col3
01/03/2014 01/10/2014 01/17/2014
01/24/2014 01/31/2014 02/07/2014
02/14/2014 02/21/2014
View 2 Replies
View Related
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Oct 26, 2006
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn =
new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn);
commChk.CommandType = System.Data.CommandType.StoredProcedure;
commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString());
commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text);
commChk.Connection.Open();
commChk.ExecuteNonQuery();
conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
View 2 Replies
View Related
May 17, 2008
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
View 4 Replies
View Related
Jun 8, 2004
What is wrong with this stored procedure? This should work right?
Create PROCEDURE UpdRequestRecordFwd
@oldITIDint ,
@newITID int
AS
Declare @RRID int
Declare @APID int
Declare crReqRec cursor for
select RRID from RequestRecords where ITID = @oldITID
open crReqRec
fetch next from crReqRec
into
@RRID
while @@fetch_status = 0
Begin
Update RequestRecords
set ITID = @newITID
where RRID = @RRID
FETCH NEXT FROM crReqRec
into
@RRID
end
close crReqRec
deallocate crReqRec
GO
View 4 Replies
View Related
Jan 17, 2006
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciatedThanks
ALTER PROCEDURE dbo.OPA_GetMenuItemsASDeclare @i tinyint ,@tc tinyintSet @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sortFROM mainNavORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
beginSet @i = (@i + 1)
/* Select for submenu itemsSELECT id, label, url, sort, mainNavIdFROM SubNavWHERE (mainNavId = @i)ORDER BY mainNavId, sortend
RETURN
View 7 Replies
View Related
Jan 17, 2006
Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause uses not the loop iterator, but rather, the ids from the first Select statement.
Alternatively, is there a more elgant approach that will return the same set of recordsets?
Any help would be much appreciated
Thanks
ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @i tinyint ,
@tc tinyint
Set @i = 1
/* Select for top level menu items*/
SELECT id, label, url, sort
FROM mainNav
ORDER BY sort
Set @tc = @@rowcount
while @i <= @tc
begin
Set @i = (@i + 1)
/* Select for submenu items*/
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @i)
ORDER BY mainNavId, sort
end
RETURN
View 6 Replies
View Related
Mar 15, 2006
Dear All,
I’m working on a stored procedure that meant to mail out users some of their action items daily.
The procedure has a double loop, first the user ids and user email addresses are selected into a table, then the outer loop cycles through the user ids and selects relevant action items to another table. The inner loop then cycles through these action items and at the end of each outer loop a string is mailed out.
Problem is that as the outer loop selects the relevant items for a user, the table holding the action items basically gets filled with more and more records and the inner loop then adds every item in the table to the string that gets mailed out, ending up with more and more items going to all the users.
I have tried to delete all records from the actionItems table at the end of each outer loop after the content of the action Items are mailed out, however this seems to keep the actionItems table empty at all times.
Not sure if this description is clear enough but I can’t see where I’m going wrong in terms of approach.
Any ideas?
View 5 Replies
View Related
Mar 16, 2015
We're running SQL Server 2008 and have run into a bit of a situation. We have 5 databases all with the same tables and we are trying to create a query that will loop through the different databases and output the results per company database. I originally did a cursor, but my boss wants the query to be in a more readable format:
His ideal wish would be the query in a stored procedure and the cursor to create the input parameter for the stored procedure for the different databases.I've tried looking through some forums and googling some possibilities but can't seem to make any sense of them.
declare @dbname varchar(100)
,@sql varchar(max)
createtable #TempDBs (
dbname nvarchar(100)
, Orig_Jnl int
, BaseRef int
, Posting_Date date
[code]....
View 7 Replies
View Related
Oct 13, 2007
I would like to know what are the ways to call a parameterized stored procedure within a loop in ASP.NET.
Scenario:
I have a loop through a dataSet and for each record I am going to execute a stored procedure to insert data in many tables.
I can not use the following syntax:
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.storedprocedurename"
With cmd
.Parameters.Add(New SqlClient.SqlParameter("@param1", paramValue1))
.Parameters.Add(New SqlClient.SqlParameter("@param2", paramValue2))
End With
What are the other ways to execute the parameterized stored procedures within a loop in ASP.NET?
Thanks,
Carlos
View 4 Replies
View Related
Aug 19, 2014
I would like to write a store prodecure to return a month:
My output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug
and so on..
then i create list of array like below:
The counter for insert the week one by one
DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar
SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53'
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53
SET @counter = 1
[Code] ....
View 8 Replies
View Related
Sep 18, 2015
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Â Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]Â
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:Â
-- Â *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
    @Numeric  int,     -- Numeric Grade
    @Result   int     -- Procedure Status (0 = OK)Â
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,Â
[Code] ....
This is resulting in an output of:Â
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
Â
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Â Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
View 6 Replies
View Related
Apr 27, 2004
Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.
This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.
Region FullName SHARP Year Ann Goal YTD Goal YTDActual
Region1 Doe10, John X 2003 20400 5100 0 Select
Region1 Doe10, John X 2003 0 0 3987 Select
Region1 Doe11, John X 2003 29645 7411.25 0 Select
Region1 Doe11, John X 2003 0 0 5377 Select
Here's my stored procedure:
CREATE PROCEDURE spFilterRegion
@RIDsent As Integer,
@StatusSent As Integer,
@SelectedRegion As NVARCHAR (50) Output
AS
SELECT Region.CountryID,
Employee.RegionID,
Employee.StatusID,
Employee.SHARP,
CASE
When Employee.SHARP = 1 THEN "X"
ELSE ""
END AS SHARPresult,
Employee.LastName,
Employee.FirstName,
Employee.LastName + ', ' + FirstName AS FullName,
Employee.EmployeeID,
Region.RegionName,
ProducerRevenue.RevenueTypeID,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS AnnGoal,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())
ELSE 0
END AS YTDGoal,
CASE
When ProducerRevenue.RevenueTypeID = 2 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS Actual,
ProducerRevenue.YearID
FROM Employee
LEFT OUTER JOIN ProducerRevenue
ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND
ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND
ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND
ProducerRevenue.StatusID = 1 AND
ProducerRevenue.RevenueTypeID <= 2
LEFT OUTER JOIN Region
ON Employee.RegionID = Region.RegionID
WHERE Employee.StatusID = @StatusSent AND
Employee.RegionID = @RIDsent AND
Employee.RoleID = 1
GROUP BY Region.CountryID,
Employee.RegionID,
Region.RegionName,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
ORDER BY Region.CountryID,
Employee.RegionID,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
View 1 Replies
View Related
Jul 31, 2006
Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.
I am calling a stored procedure from asp.net and there is a
cursor in the stored procedure that does some processing on serval
tables.
if i run the stored procedure on Query Analyzer it works and does what
it is suppose to do but if i run it from my asp.net/module control it
goes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where is
called programmatically from the asp.net/module control page.plus it
does not throw any errors
This is the code from my control
System.Data.SqlClient.SqlParameter [] param={new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new
System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new
System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ "
"+UserInfo.Username)};
string
str=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=new
System.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();
SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);
------------------------THis is the code for my storedprocedure-------------
CREATE PROCEDURE [dbo].[generatePaylistTuned]
@periodStart datetime,
@periodEnd datetime,
@addedby varchar(40)
AS
begin transaction generatePayList
DECLARE @pensioner_id int, @dateadded datetime,
@amountpaid float,
@currentMonthlypension float,@actionType varchar(50),
@isAlive bit,@isActive bit,@message varchar(80),@NoOfLoadedPensioners int,
@NoOfDeadPensioners int,@NoOfEnrolledPensioners int,@DeactivatedPensioners int,
@reportSummary varchar(500)
set @NoOfLoadedPensioners =0
set @NoOfDeadPensioners=0
set @NoOfEnrolledPensioners=0
set @DeactivatedPensioners=0
set @actionType ="PayList Generation"
DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId
where p.isActive = 1
OPEN paylist_cursor
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
WHILE @@FETCH_STATUS = 0
BEGIN
set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1
if(@isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@pensioner_id
set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners
set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension)
set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners
end
-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
END
CLOSE paylist_cursor
DEALLOCATE paylist_cursor
set @reportSummary ="The No. of Pensioners Loaded:
"+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of
Deactivated Pensioners:
"+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of
Enrolled Pensioners:
"+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead
Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')
if (@@ERROR <> 0)
BEGIN
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION generatePayList
END
commit Transaction generatePayList
GO
View 5 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
Jan 31, 2008
Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
View 5 Replies
View Related
Jun 23, 2005
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId
BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
View 6 Replies
View Related
Jul 14, 2005
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
what are mine doing wrong ?
Please help
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = password
FROM CandidatesAccount
WHERE username = @username and Activate = 1
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO
View 5 Replies
View Related
Jun 1, 1999
I have a stored procedure which does a simple select joining 3 tables.
This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.
After re-compiling the procedure, it worked fine as before.
Does anyone know of any reason why a procedure would need recompiling like this?
We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?
View 3 Replies
View Related
Oct 24, 2007
Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table
USE [dw_data]
GO
/****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_address]
(
@TableType INT = null
)
AS
IF @TableType is NULL OR @TableType = 1
BEGIN
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')
CREATE TABLE [dw_build].[dbo].[address] (
[_id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address_2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[category_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[created] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[forename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[other_inv_link] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out_of_district] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[packed_address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_ignore] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paf_valid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[patient_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pct_of_res] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postcode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[real_end_date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[relationship_userno] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[surname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[telephone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[town] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[updator] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
END
IF @TableType is NULL OR @TableType = 2
BEGIN
CREATE TABLE [dw_build].[dbo].[address_cl] (
[_id] [int] NULL,
[address_1] [varchar](40) NULL,
[address_2] [varchar](40) NULL,
[category] [varchar](7) NULL,
[category_userno] [int] NULL,
[county] [varchar](40) NULL,
[created] [datetime] NULL,
[creator] [int] NULL,
[end_date] [datetime] NULL,
[forename] [varchar](40) NULL,
[notes] [varchar](max) NULL,
[other_inv_link] [int] NULL,
[out_of_district] [bit] NOT NULL,
[packed_address] [varchar](220) NULL,
[paf_ignore] [bit] NOT NULL,
[paf_valid] [bit] NOT NULL,
[patient] [int] NULL,
[patient_date] [datetime] NULL,
[pct_of_res] [int] NULL,
[postcode] [varchar](40) NULL,
[real_end_date] [datetime] NULL,
[relationship] [varchar](7) NULL,
[relationship_userno] [int] NULL,
[surname] [varchar](40) NULL,
[telephone] [varchar](40) NULL,
[title] [varchar](40) NULL,
[town] [varchar](40) NULL,
[updated] [datetime] NULL,
[updator] [int] NULL
) ON [PRIMARY]
END
View 13 Replies
View Related
Dec 22, 2006
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
View 2 Replies
View Related
Jul 2, 2004
I have the following stored procedure in sql server 2000:
(@nsn varchar 15 int,
@item_nam varchar 255 ouput
)
AS
declare @stockquantity int
select @stockquantity = stockquantity
from inventory
where stockquantity = 0
select @item_nam = item_nam
from inventory where nsn = @nsn
delete from inventory
where nsn = @nsn
and stockquantity = 0
GO
what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equal to zero. Right now it returns the item_nam even if the record wsa not deleted.
ANy help is much appreciated.
View 1 Replies
View Related
Jul 23, 2005
I don't know if this is possible. However, what i am attempting to do isusing C#'s window forms. I open up an excell sheet stored in my windowsform. The excel sheet stores names of the stored procedures in thatdatabase. I want to know if it's possible to click on that storedprocedure to open up a link to display the code of that stored procedure ofcourse in a read only mode.any suggestions....
View 2 Replies
View Related
Sep 22, 2007
hi all,i need to convert these simple PHP code into stored procedure :<?php$result=mssql_query( "SELECT whid, whcode FROM warehouse" );while( $wh = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT plid, nopl FROM packlist WHERE whid ='" . $wh->whid . "'";while( $pl = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT qty FROM packlistnmat WHERE plid ='" . $pl->plid . "'";while( $pln = mssql_fetch_object( $result ) ){echo "Stock from " . $wh->whcode . " AND Packing List number " .$pl->plid . " = " . $pln->qty;}}}?>my focus is in nested query, then i can call each field from the query(SELECT whid, whcode...) in sub query.thanks,aCe
View 4 Replies
View Related
Jan 8, 2008
help BUG in my sql code
i can not see this employee 111111 whan i run this code
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
TNX
Code Block
------------------------
-- create mod cycle shift pattern
------------------------
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
SELECT 222222,2,'29/01/2008','30/01/2008' UNION ALL
SELECT 333333,3 ,'27/01/2008','30/01/2008' UNION ALL
SELECT 444444,5 ,'26/01/2008','30/01/2008'
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
--SET
----@StartDate = StartDate
---- last day of next month
--SET
--@EndDate = EndDate
--@StartDate = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1 )
---- last day of next month
--SET
--@EndDate = DATEADD(m ,3,GETDATE()-DAY (GETDATE()) + 1)- 1
--set
--@current = @StartDate
----
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
View 1 Replies
View Related
