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
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.
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 No3 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. Pls provide me with full stored procedure code. It is very urgent.
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 want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething. i came up with this but when it's doing the second if i get error on the page. Dim ReturnVal As Integer Dim ReturnVal2 As IntegerDim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)", conn) 'To check if the # is registered.Dim cmd2 As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @ReportNumber)", conn) cmd.Parameters.AddWithValue("@ReportNumber", txtReport.Text)cmd2.Parameters.AddWithValue("@ReportNumber", txtReport.Text) conn.Open() ReturnVal = Convert.ToInt32(cmd.ExecuteScalar()) conn.Close() 'This checks if the # is correct. If ReturnVal > 0 Then 'This checks if its registered with someone else. conn.Open() ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar()) conn.Close() If ReturnVal2 <> 0 ThenServer.Transfer("regccinfo.aspx") Else lblError.Text = "the # is registered with someone else." End If Else lblError.Text = "the # does not exists." txtReport.Focus() End If
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table. When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure. However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work. Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
Hello, Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2. Is it possible to do it by using SQL Stored Procedure?? Thank you
Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
In the olden days when I used databases that kept each table in a separate file, I used to be able to tell when a specific table was modified by the date stamp on the file.
Is there a method in SQL server to check the "last modifed date" at the table level?
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.
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are: tbltest
Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far.
From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter.
Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?!
any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo
What I am trying to do now is combine multiple complex queries into one table and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are pretty complex so I created each query as a Stored Procedure. These queries are dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the current user's username as a parameter. Within the master SP (Stored Procedure) it creates a temporary table inserts and executes multiple stored procedures and inserts into the temporary directory. Each of the sub stored procedures all have the same columns. After the insert to the temp tables, I then query the temp table and return it to the function it was executed in code and fill it as a System.Data.DataTable. I then bind the DataTable to a Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the data is a disconnected datasource? Perhaps the session in the SQL Server when the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT] varchar(200), [LINK] varchar(200) ) INSERT INTO #MyAlertsEXEC TM_getAlertsNewTasks @Username INSERT INTO #MyAlertsEXEC TM_getAlertsLateTasks @Username SELECT [DATE] AS 'DATE', [TEXT] AS 'TEXT', [LINK] AS LINK FROM #MyAlerts GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the the DataTable is filled. I tried just executing one of the sub SP without creating temporary tables and it works flawlessly. But the query in one of the sub SP is a normal but complex select.
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?
I am totally confused, please help me. I am new to this
I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are
1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.
2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)
I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6
I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.
Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'
Of course there are different names, just changed for an example.
I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp] @EmpName nvarchar(100), @Nationality nvarchar(30), @Passport nvarchar(20), @ContractDate date, @HealthDate date
I have an orchestration that recieves a pricelist flat file and it must insert into 2 tables - article, articlePrice(for each LotSize) into sql server using a sql adapter. I also want it to check if a record exists for that lotSize and update it if it does. The records are indentified with a uniqueidentifier. So I have 3 if statements: 1 - if (<the article does not exist(checks SupplierArticleNumber)>) <insert a record into article table> <insert a record into articlePrice table>
2 - else if(<the article exists but not for that lotSize>) <insert a new record in articlePrice table>)
3 - else update the corresponding ArticlePrice row.
My problem is in writing the sp, first it checks if the article exists, if it does then it creates a row in the article table and then one into the articlePrice table. My question is how do I get a hold on the "articleGUID"(pk - uniqueidentifier) that was created on the first table on insertion in order to create the row in the second table(fk)?
And is this possible with one stored procedure? If not does anyone know in Biztalk what other way to do this?
I am new to both Biztalk and sql, so I don't really know what I am doing? I would really appreciate any suggestions!
I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.
