Stored Procedure Checking The Values In Another Table
Mar 26, 2008
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values
-------------------------------------------
PoId Podate Approved
2 2008-07-07 No
4 2007-05-05 No
5 2005-08-06 Yes
6 2006-07-07 Yes
Table B contains the following columns and values
-------------------------------------------------
TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No
3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in Table A. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)Here are the collumns i have in table_a and table_bDescription | FundID (this is not my PK) | Money I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )If anyone has this answer please let me know.Thanks,RB
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'
[Code] .....
My tabel is HST_MASTER.Control.
I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.
I have two tables called A and B and C. Where A and C has the same schema
A contains the following columns and values ------------------------------------------- PoId Podate Approved
22008-07-07 No 42007-05-05 No 52005-08-06 Yes 62006-07-07 Yes
Table B contains the following columns and values ------------------------------------------------- TaskId TableName Fromdate Approved_Status
1 A 7/7/2007 No 3 B 2/4/2006 Yes
Now i need to create a stored procedure that should accept the values (Yes/No) from the Approved_Status column in Table B and should look for the same values in the Approved column in the Table which is specified in the table name column in Table B. If both values match then the corresponding rows in Table A should be archived in table C which has the same schema as that of Table A. That is the matching columns should get deleted from Table A and shoud be inserted into Table C.
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
I would like to know if the following sql can be used to obtain specific columns from calling a stored procedure with parameters:
/* Create TempTable */ CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT) GO /* Run SP and Insert Value in TempTable */ INSERT INTO #tempTable (MyDate, IntValue) EXEC TestSP @parm1, @parm2
If the above does not work or there is a better way to accomplish this goal, how to change the sql?
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
Hi i have a stored procedure which deletes a row from a table. Sometimes the proc will break the constraints for the table. How can i check if the constraint is going to be broken before I try to delete so that I can avoid a nasty error message coming onto my asp.net page?
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/ if NOT EXISTS (SELECT object_id('procedureName','p')) CREATE PROCEDURE [dbo].[procedureName] @location char(2) AS ...Procedure...
GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p')) SELECT 'FALSE' ELSE SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'PROCEDURE'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@location'.
Hi,Say I have a stored procedure which does two INSERT operation.How can I check if the first INSERT succeeded in order to know if Ishould carry on and do the second one ?Regards
I am stumped on the error reporting with sql server. I was told ineed to return @SQLCode(code showing if successful or not) and@ErrMsg(and the message returned). I am clueless on this.I wrote this procedure: Code: ( text )
I'm using a stored procedure from sqlserver 2005 to get columns for my report. But, I don't know how to capture the returned values from the procedure and show it on the report.
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I have a stored procedure that selects * from my table, and it seems to be working fine: USE myDB GO IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL DROP PROCEDURE GetAll GO CREATE PROCEDURE GetAll AS DECLARE ref_cur Cursor FOR SELECT * FROM myTable Open ref_cur FETCH NEXT FROM ref_cur DEALLOCATE ref_cur
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values. Can anyone help me finish this?public class dbGet_base { public dbGet_base() { _requestId = 0; }
public dbGet_base(Int64 RequestId) { this._requestId = RequestId; getDbValues(RequestId); } public void getDbValues(Int64 RequestId) { getDbValues(RequestId, "GetAll"); } public void getDbValues(Int64 RequestId, string SP_Name) { using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"])) using (SqlCommand Command = new SqlCommand(SP_Name, Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId; Command.Parameters.Add(?? } }
Hi, In stored procedure. I have to select a row, and I need many columns from the row, how do I do it in one select statement? for example set @field1 = SELECT TOP 1 field1 FROM table will select the first column from the table. set @field2 = SELECT TOP 1 field2 FROM table - this select the 2nd field. How do I select multiple columns and assign to multiple different values?
Hello Group I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page. Thank you Michael
FROM [tbUsers] Where fldUsername = @fldUsername IF @actualPassword IS NOT NULL IF @fldPassword = @actualPassword RETURN 1 ELSE RETURN -2 ELSE RETURN -1 GO
code
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs) If IsValid Then If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False) End If End If End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer Dim strFullName As String ' Variable Declaration Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim myReturn As SqlParameter Dim intResult As Integer Dim sqlConn As String
' Set conn equal to the conn. string we setup in the web.config sqlConn = ConfigurationSettings.AppSettings("sqlDbConn") myConn = New SqlConnection(sqlConn)
' We are going to use the stored procedure setup earlier myCmd = New SqlCommand("stpMyAuthentication", myConn) myCmd.CommandType = CommandType.StoredProcedure
' Set the default return parameter myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int) myReturn.Direction = ParameterDirection.ReturnValue
' Open SQL and Execute the query ' Then set intResult equal to the default return parameter ' Close the SQL connection myConn.Open() myCmd.ExecuteNonQuery() intResult = myCmd.Parameters("RETURN_VALUE").Value Session("strFullName") = strFullName myConn.Close()
Response.Write(strFullName) ' If..then..else to check the userid. ' If the intResult is less than 0 then there is an error If intResult < 0 Then If intResult = -1 Then lblMessage.Text = "Username Not Registered!<br><br>" Else lblMessage.Text = "Invalid Password!<br><br>" End If End If ' Return the userid Return intResult
Hi, How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?
CREATE PROCEDURE testProc AS BEGIN CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT) INSERT INTO #tblTest(Col1) SELECT colA FROM tableA ORDER BY colA
END
This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5..... or is there any chance of ID column having values like 1,2, 4, 6,7,8....
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails @Id string as Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it. How can i solve this problem?
Hi, I am trying to get the combination of results in my stored procedure, I am not getting what I need. Following are the things which I need to return from my stored proc. 1. I need to select distinct categories and their record count 2. I also need to select the records from the table. 3. Need to send both category, record counts and records. First is it possible in stored procedure? Following is helpful information.
Data in tables looks like this. prod id, prod_name, prod_category 1, T shirts, Mens Apparel 2 , Shirts, Mens Apparel 3 , Pants , Mens Apparel 4, Tops , Women Wear 5, Bangles, Women Wear
And in User Interface I need to show like this.
Mens Apparel (3) 1 T Shirts 2 Shirts 3 Pants
Women Wear (2) 4 Tops 5 Bangles
Please help me if there is any way to return the complete data structure using stored procedure. If I do something in java code, I can get this, but I am trying to get directly from stored procedure only.
Before I start a small project I am interested in the best way to do it. I work for a college doing management information and generally finding problems with our data. A regular thing I end up with is a set of student ID's which I need to lookup. Through the front end this takes a while as I have to look them up individually and I often need to compare ID's.
What I have thought about making is a system where I can select a set of ID's and search for all of them. I will probably make this through C# pasting the set of id's into a datagridview and providing the results in another one.
The problem I have is I don't know how to send a set of ID's (so I would probably be using where IN (SET OF ID's). I read briefly a while back about passing a type table but am unfamiliar with how to use it. This is sql 2000 server.
hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table
) As if(@columnname !=' ' and @recordname !=' ') begin select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"' end GO
End select '' as unuseid, substring(ltrim(rtrim(s.Spares_Code)),1,12) as Code, oh.WO_Number AS Claim_Id, ltrim(rtrim(sc.section_code)) AS section_code, ltrim(rtrim(dc.defect_code)) AS defect_code, ltrim(rtrim(at.Action_Taken_Code)) AS Repair_Code, cs.Call_status_code from [32_Trans_Work_Order_Spares_Detail] ws inner join [32_Trans_Work_Order_Header] oh on oh.WO_Number = ws.WO_Number inner join [11_Master_Spares]s on s.Spares_ID = ws.Spares_ID inner join [31_Master_Section_Code] sc on sc.Section_ID = ws.Section_Code_ID inner join [31_Master_Defect_Code] dc on dc.Defect_ID = ws.Defect_Code_ID inner join [10_Master_Equipment_Status] e on e.Equipment_Status_ID = oh.Equipment_Status_ID inner join [00_Master_Country] c on c.Country_ID = mp.Country_ID where e.Equipment_Status_ID in (1,2) and cs.Call_Status_ID in (1,2) and oh.WO_Record_Date between @Fromdt and @ToDt and oh.WO_Status='C'
My Problem is How to pass values to parameters Status Code Consists of values C, V, R which i am passing from the Front End along with Call_Status_ID which can be 1,2.
SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)
If(@V_Facilities=null)
Set @pOutput = @V_Facilities
Return @pOutput;
Else
Set @pOutput = @V_Facilities
Return @pOutput;
GO
Anyone correct this query , I want return the output from this procedure
I have a requirement to get two count values from a stored procedure and use those values in other stored procedure. How can I do that. I'm able to get only 1 value if i use the return key word.
Eg:
create proc test1 as Begin Declare scount int Declare scount2 int -- statements in stored procedure return scount return scount2 End
create proc test2
Declare variables... Exec Test1 // here i want the values (scount and scount2 ), processed in stored procedure Test1 .
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
Hi there!I have a problem I can't get rid of... I hope anyone can help me with it!For a room-booking application I need to select available rooms for a givenperiod. First I do the availability check which is no problem, than I needto find out if there is a tariff available for every night of the givenperiod. If not, I will not show the room as 'available' in the systembecause I can't make a tariff calculation.All tariffs are entered in a table which can hold different night-tariffsfor different periods. The table looks like this:========CREATE TABLE [dbo].[TARIEVEN] ([TARIEF_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[FK_OBJECT_ID] [numeric](18, 0) NOT NULL ,[STARTDATUM] [datetime] NULL ,[EINDDATUM] [datetime] NULL ,[TARIEF_PRIJS] [decimal](10, 2) NULL ,) ON [PRIMARY]GOFK_OBJECT_ID is the Room IDSTARTDATUM is the startdate of a tariff periodEINDDATUM is the enddate of a tariff periodTARIEF_PRIJS is the tariff per night in the tariff period========What I do at the moment is:--------SELECT fk_object_id FROM TARIEVENwhere ('15-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('16-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)AND ('17-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)--------This will work if all nights are in the same tariff period, but it will notproduce the right results if the tariffs are in different periods because nosingle row contains a startdate and enddate in between which all dates are.I can't use an OR statement because I absolutely need a tariff per night.So, what my question is in short:How can I for example select OBJECT 1 from the following table consideringthat I want to return all rooms which have a tariff available for a periodfrom 15th of october to 17th of october?-----------------FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJSFK_OBJECT_ID STARTDATE ENDDATE TARIFF1 10-oct-2004 16-oct-20041001 17-oct-2004 18-oct-2004125In words: object 1 costs 100 per night in the period from 10 to 16 octoberand 125 in the period from 17 to 18 october------------------I hope anyone can help me. Thanks a lot in advance!!Robert
Hi I am new to C# . I have a stored procedure which takes 4 parameters GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null. And i have 5 textboxes from which i send the parameters. I am validating the input like this :System.Nullable<int> Comp_ID; if ((txtsrchCompID.Text).Trim() == "") {Comp_ID = null; }else if ((txtsrchCompID.Text).Trim() == "") { try {int i = int.Parse(txtsrchCompID.Text); Comp_ID =i; catch {mesage += "Complaint ID is not valid"; } }
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID' I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts. How do i pass the null value to the stored procedure? pls help..
I have a stored procedure which returns 3 different kind of values. I am checking whether a certain value entered by user is present in one of the columns of database table. Accordingly the SP returns 1 if present, -1 if not present and third value is SQL server 2005 error.But the problem is that I am only getting -1 everytime even if the value is present.I executed the SP alone to find out if it is the one which is returning the INCORRECT value. I found that that SP is returning the correct value.Therefore I came to the conclusion that it is the Table ADapter which got corrupted.I deleted the TableAdapter and created it again, but then it didn't solve the problem.I have now run out of ideas. The code of the SP is:ALTER PROCEDURE spcheck_ServerName (@Server_Name nvarchar(50)
) ASDECLARE @Result int IF EXISTS ( SELECT NULL FROMServerDetails WITH (UPDLOCK) WHERE
[SERVER NAME] = @Server_Name ) BEGINSELECT @Result = 1 END ELSE BEGIN SELECT @Result = -1 END
IF @@ERROR <> NULL BEGIN SELECT @Result = @@ERROR END RETURN @Result And I am calling the tableAdapter method in the code behind file of the web form in the following manner:private int chkServerName(string sname1) { try {Serverlist1TableAdapters.SERVERDETAILSTableAdapter nwAdapter = new Serverlist1TableAdapters.SERVERDETAILSTableAdapter(); int snval = (int)nwAdapter.spcheck_SName(sname1);return snval; }catch (Exception ex) {return ex.GetHashCode(); } }
