Multiple Table Entry In A Stored Procedure
is there a way when making a stored procedure if i can enter the information in multiple tables?? say the primary key into another table to link the relationship? or should i just pull it out and then put in?
View Complete Forum Thread with Replies
Related Forum Messages:
Could Not Locate Entry (stored Procedure Error)
hello everyone,I am using VS 2005 and sql server 2000. I have a web application which uses a connectionstring with a user id and pwd. Everything is fine, I can execute normal sql statements from the code behind. I cannot execute a stored procedure...I get this error. Could not locate entry in sysdatabases for database 'exec usp_add2DB (my parameters are included here, deleted for simplicity reasons)No entry found with that name. Make sure that the name is entered correctly. I have given execute permission for the user, and the name of the storedproc is correct. the name of the server is also correct....pls pls help me what is wrong here...
View Replies !
Multiple Insert Into Multiple Tables With A Stored Procedure
Hello I am building a survey application. I have 8 questions. Textbox - Call reference Dropdownmenu - choose Support method Radio button lists - Customer satisfaction questions 1-5 Multiline textbox - other comments. I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID. I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score. Please help me! Thanks Andrew
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
MULTIPLE ENTRY FORMS
I WOULD LIKE TO MAKE A MULTIPLE ENTRY FORM IN ACCESS, I HAVE BEEN TOLD THIS IS NOT POSSIBLE. I NEED TO BE BE ABLE TO ENTER A SSN AND EDIT A FIELD IN A TABLE, BUT I NEED TO HAVE IT PROMPT EVERYTIME FOR THE SSN. KELLY HOBBS
View Replies !
Multiple Data Entry
iam new to MS SQL 7 server...i have two tables in my database say Table1 and Table2 having a comman field--- Name String(30). I want that dual data entry should be made for any single entry. That is if a name is entered in Table1, then same entry should be automatically entered in table2
View Replies !
Multiple Stored Procedure...or 1 Dynamic Procedure?
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values... my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters... if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs... this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application... but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array?? Cheers, Justin
View Replies !
Pulling Multiple Entry's With Jscript
I am new to jscript and trying to learn how to pull multiple entry's from a table. I know with php you can use a while loop but that doesn't seem to work with jscript. Here is what I have so far. var sSql = "select nIndex,sDescription from StatisticalDiskIdentification " + "JOIN PivotStatisticalMonitorTypeToDevice ON " + "StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID = " + "PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID " + "where (PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID='2') and " + "PivotStatisticalMonitorTypeToDevice.nDeviceID = " + nDeviceID; while (oRs = oDb.Execute(sSql)){ if ( !oRs.EOF ) { // Display various columns in the debug log (Event Viewer). var sDisplay; nIndex = "" + oRs("nIndex"); Context.LogMessage("nIndex=" + nIndex); sDesc = "" + oRs("sDescription"); Context.LogMessage("Description =" + sDesc); } Context.SetResult( 0, " Ok"); oRs.MoveNext(); } Thanks for any help
View Replies !
Sorting Problem? Most Recent Entry When Selecting Multiple Columns
I'm trying to determine the oldest timestamp that meets given criteria which includes a specific action_type code. That code can legitimately be performed more than once on an item (all actions are recorded in an actions table, so the full history is available). actions table (columns: action_id, request_id, action_type, action_time, action_reason) I'm having trouble getting the most recent timestamp for the given action_type I'm looking for, when that action_type has been performed more than once. My intent with the query below is to get the most recent action_time and it's request_id that meets the criteria SELECT TOP 1 a.action_time, r.request_id FROM requests r JOIN incident i ON r.request_id = i.request_id LEFT JOIN actions a ON r.request_id = a.request_id WHERE i.refund_type = 1 AND (r.status_code = 3 OR r.status_code = 14) AND a.action_type = 3 ORDER BY a.action_time So for example, if the item with request_id 1334 has had action_type 3 performed on it twice, once at 2007-10-15 13:30:00 and then again at 2007-10-17 14:40:00, it's picking up the former, when I want the later. That it had action_type 3 performed on it twice is legitimate. (larger context an item was approved by a manager, then rejected by a processor, and then approved by a manager again after resubmission) Hopefully this has made sense so far. So, how do I make sure I'm getting the most recent action time, when I don't know if the item in particular will have more than one of this action type performed on it?
View Replies !
Multiple Tasks In Stored Procedure
I have put together the stored-procedure below to carry out update, delete and insert queries in one visit. The code has been pieced together from the pages listed at the bottom. I pass the procedure three XML strings and after testing it a few times it seems to work fine. I’m fairly new to stored procedures though, so I was hoping someone would answer these questions: 1. Is this an acceptable way to do this? Can you foresee any problems?2. I want to make this an ‘all-or-nothing’ event, i.e. if any part of the procedure fails, it must all fail. How would I achieve that?3. I want to know in my calling code what the result is. I’ve used output parameters before, but I’m unsure how to combine one with 2 above. Sorry this is a long script, but I’ve removed most of the column names and values to shorten it. Thanks in advance.CREATE PROCEDURE amendPageRecords @PagesToUpdate xml, @PagesToDelete xml, @PagesToInsert xml AS BEGIN -- UPDATING RECORDS ------------------------------------------------------------ DECLARE @UpdateTable TABLE (PageID int, PageType varchar(10)) INSERT INTO @UpdateTable (PageID, PageType) SELECT PageID = UpdatePages.Item.value('@PageID', 'int'), PageType = UpdatePages.Item.value('@PageType', 'varchar(10)') FROM @PagesToUpdate.nodes('Pages/Page') AS UpdatePages(Item) UPDATE page SET page.page_type = UP.PageType FROM page INNER JOIN @UpdateTable UP ON page.page_id = UP.PageID -- DELETING RECORDS ------------------------------------------------------------ DECLARE @DeleteTable TABLE (PageID int) INSERT INTO @DeleteTable (PageID) SELECT PageID = DeletePages.Item.value('@PageID', 'int') FROM @PagesToDelete.nodes('Pages/Page') AS DeletePages(Item) DELETE FROM page FROM page INNER JOIN @DeleteTable DP ON page.page_id = DP.PageID -- INSERTING RECORDS ----------------------------------------------------------- DECLARE @InsertTable TABLE (SiteID int, PageType varchar(10)) INSERT INTO @InsertTable (SiteID, PageType) SELECT SiteID = InsertPages.Item.value('@SiteID', 'int'), PageType = InsertPages.Item.value('@PageType', 'varchar(10)') FROM @PagesToInsert.nodes('Pages/Page') AS InsertPages(Item) INSERT INTO page SELECT SiteID, PageType FROM @InsertTable END GOCode taken from:http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspxhttp://www.eggheadcafe.com/articles/20030627c.asphttp://www.sommarskog.se/arrays-in-sql-2005.html
View Replies !
Stored Procedure Using Multiple Selects?
I need help in figuring out the proper way of writing a stored procedure out correctly to get my desired datasource. In my ocnIdToRatePlanOptions table, I will recieve a parameter via request.querystring @ocnId to filter out my result set for ocnIdToRatePlan table. Based on the ocnId filtered I want it to select the corresponding tables too.So, if a querystring is passed that is 3955 in my ocnIdToRatePlanOptions table, I want it to use it to create a select for RatePlan1. If a querystring is passed that is 1854 in my ocnIdToRatePlanOptions table, I want it to use to create a select for RatePlan2. Is this possible? ocnIdToRatePlanOptions Table [otrpoRefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[FKrpoRefId] [int] NOT NULL,1, 3955, 12, 1854, 2RatePlan1 Table[rp1RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL[fee] [decimal](18, 2) NOT NULL1, 3955, 1.002, 2350, 2.00RatePla2 Table[rp2RefId] [int] IDENTITY(1,1) NOT NULL,[FKocnId] [nvarchar](4) NOT NULL,[q_0_50] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_0_50] DEFAULT ((225)),[q_51_100] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_51_100] DEFAULT ((325)),[q_101_150] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_101_150] DEFAULT ((345)),[q_151_200] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_151_200] DEFAULT ((400)),[q_201_250] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_201_250] DEFAULT ((450)),[q_251_300] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_251_300] DEFAULT ((500)),[q_301_400] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_300_400] DEFAULT ((650)),[q_401_600] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_401_600] DEFAULT ((950)),[q_601] [numeric](18, 2) NOT NULL CONSTRAINT [DF_ratePlan2_q_601] DEFAULT ((1.50)) 1,1854, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.502,8140, 225.00, 325.00, 345.00, 400.00, 450.00, 500.00, 650.00, 950.00, 1.50
View Replies !
Stored Procedure Using Multiple Databases
Ok, this is kind of an odd problem. Back in June we were having problems with our call manager software, and they decided to have it just start usinga new database. Now I'm trying to generate some reports which need to cover both the old call stats and the new, so that means the stored procedure builds a temp table and populates it from both databases.This works perfectly fine in Management Studio, and when being called from Excel.However when I try to call it from an ASP.NET web app using SqlCommand.ExecuteReader(), I only get results from the new database!What on earth could cause that?
View Replies !
Multiple SQL Statements In A Stored Procedure
Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!
View Replies !
Multiple Deletes With A Stored Procedure
Just wondering if this is good form:Alter Procedure "mySPName"@UniqueID intASset nocount onset xact_abort offDELETE FROM tblNameOneWHERE(tblNameOne.UniqueID = @UniqueID)DELETE FROM tblNameTwoWHERE(tblNameTwo.UniqueID = @UniqueID)Is it a good idea to run multiple detele statements within one SP?thanks,lq
View Replies !
Multiple Parameters To A Stored Procedure
Hi All, I have a database with very heavy volume of data. I need to write a stored procedure with 20 parameters as input and it searches in a table . Most of the parameters or NULL , how do I write this procedure without using any dynamic queries. Ex : To find a customer I have a proc which can accept 20 parameters like CustName, City, State , Phone , Street etc. Im passing only Custname as parameters and other 19 parameters are NULL.How do I write the WHERE clause ? Thanks in advance, HHA
View Replies !
Multiple Keywords In Stored Procedure
Hi, I'm trying to move an asp site from an Access DB to SQL Server. The site features a search system which allows multiple keywords to be used in up to three fields. The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701): "So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business." The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case? If it isn't possible, how can I go about this? Cheers all. __________________________________________________ ___ Code: ASP: item=request(item) '****item = Replace(item, " ","%' AND item like '%")**** - needs attention rs.Open "Exec getlist "& item &"" objConn, 3 SP: CREATE PROCEDURE getlist @item nvarchar(255) SELECT item FROM publications WHERE item LIKE '%' + @item + '%' GO
View Replies !
Stored Procedure With Multiple Inputs
Hello. I'm trying to write a generic Stored Procedure will select records with the following options. 1. Select XXX from theTable Where ID = 1 2. Select XXX from theTable Where ID = 1 or ID = 3 or ID = 4 The issues is that at run time the WHERE CLAUSE COULD CHANGE (it could have multiple OR-d options) Is there a way to pass a varChar argument like "Where ID=1 or ID =2"? Thanks in advance! -andy
View Replies !
Multiple Database Stored Procedure
I am trying to write a stored procedure that has an inner join between two tables from two different databases on the same sql server. Something like... Select * FROM DB1-table1 INNER JOIN DB2-table1 ON DB1-table1.ID = DB2-table1.ID yada yada yada....... Does anyone know how to do this or is it possible? If so, what database should I put the stored procedure in or does it matter?
View Replies !
Multiple Results From One Stored Procedure
If I have multiple selects statements in one stored procedure. How do I access the different results in a report in Reporting Services? Where the first select is the detail lines of my report and the second select is just a field for a my header? Or am I going about this wrong with putting it all the results I want into one stored procedure for one report? Example stored procedure: ALTER PROCEDURE [dbo].[proc_DepositsByOfficer] As SELECT MASTER_DSC.APP , MASTER_DSC.BRANCH , qlkpOfficer.strName , MASTER_DSC.DSC_OFFICER_49 , qlkpBranchName.strDescrS , MASTER_DSC.DSC_CUR_BAL_21 , Case MASTER_DSC.APP WHEN 1 Then DSC_CUR_BAL_21 End AS DDA_BAL , Case MASTER_DSC.APP WHEN 2 Then DSC_CUR_BAL_21 End AS SAV_BAL , Case MASTER_DSC.APP WHEN 3 Then DSC_CUR_BAL_21 End AS CD_BAL , MASTER_DSC.DSC_INT_RATE_45 , Case When [DSC_CUR_BAL_21]>0 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End As ANN_EXP , Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=1 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_DDA , Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=2 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_SAV , Case When [DSC_CUR_BAL_21]>0 And MASTER_DSC.APP=3 Then ([DSC_CUR_BAL_21]*[DSC_INT_RATE_45])/100 Else 0 End AS ANN_EXP_CD , Case MASTER_DSC.APP WHEN 1 Then 1 End AS DDA_Count , Case MASTER_DSC.APP WHEN 2 Then 1 End AS SAV_Count , Case MASTER_DSC.APP WHEN 3 Then 1 End AS CD_Count , qlkpApplicationCode.strDescrS AS strApplCode FROM MASTER_DSC INNER JOIN qlkpApplicationCode ON MASTER_DSC.APP=qlkpApplicationCode.dblReference LEFT JOIN qlkpOfficer ON MASTER_DSC.DSC_OFFICER_49=qlkpOfficer.intID LEFT JOIN qlkpBranchName ON MASTER_DSC.BRANCH=qlkpBranchName.dblReference WHERE MASTER_DSC.DSC_CUR_BAL_21<>0 ORDER BY MASTER_DSC.BRANCH; SELECT dbo.fBankName() AS BankName;
View Replies !
Stored Procedure For Multiple Inserts
Hello all, I'm working on a project for fun with some friends and have run into an issue with stored procedures. I've dealt with SQL quite a bit at my current job, but always from the perspective of somebody querying the database. The database was always managed by someone else and I never had to worry about the underlying code. Now, with my own project at home, I'm trying to deal with a situation and would like to use one, but I'm not sure if it is the best option and if so, exactly how to go about it. Imagine a site that tracks movies. I have 3 tables: Movies ( MovieID, Title, DirectorID, ActorID ) Actors ( ActorID, Name ) Director (DirectorID, Name) This is an overly simple example, but it gets to the heart of my problem. Okay, now what I'm wanting to do is to be able to write a procedure that would let me create my entries from just one call -- for instance create_movie( 'Super Movie', 'directorJoe', 'actorJohn' ) that would do the following things: -Look and see if the given director and actor already exist (from previous films) -If they do, grab their ID values and use those in the new movie entry -If they do not, create new entries and get THOSE ID values to use in the new movie entry Can this be done in a stored procedure (I'm pretty sure it can be) and what sort of commands should I look into -- I'm not looking for a complete solution, cause I want to learn, but I am having trouble finding examples that fit my scenario. Thanks.
View Replies !
Multiple Statements In Stored Procedure
I believe we can you multiple statements in stored procedures? Suppose I have a stored procedure and I pass parameters to this SP. What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below: CREATE sp_temp_proc @order_id int, @order_position int, @temp_order_id OUTPUT @temp_order_position OUTPUT, SELECT order_id AS temp_order_id FROM <TABLE> WHERE order_position < @order_position @temp_order_id = temp_order_id UPDATE <TABLE> SET order_position = @order_position WHERE order_id = @temp_order_id UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id
View Replies !
Pass Multiple Parameters To Stored Procedure
Hi, I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample, CREATE PROCEDURE [dbo].[display_customer] @agentID varchar(20), @customer1 varchar(20), @customer2 varchar(20), ..... -- Here I do know how many customers for each agent AS SELECT name, city, state, zip FROM rep_customer WHERE agent = @agentID and (name = @customer1 or name = @customer2) Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure? Thanks a lot!
View Replies !
Return Multiple Resultsets From Stored Procedure
I have read a lot in favor and recommendation of returning multiple resultsets. But now I have to implement it with a scenario. I have a Parent Table named "Books" and a Child one named "Volumes". A book can have multiple volumes. Now I want to display the list of Books with their Volumes pagewise. How can I implement that procdure.
View Replies !
Stored Procedure With Multiple Update Statements
I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. " The procedure takes in three parameters and retrieves 23 values from the DB to display on my form. Any ideas?
View Replies !
Handling Multiple Values From A Stored Procedure In ASP.NET.
Hi all, I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have: ExecuteNonQuery(cmd); return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower(); Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so: return (int)ExecuteNonQuery(cmd); Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help. Thanks
View Replies !
Running Stored Procedure Multiple Times
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this? newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam) ownrParam.Value = OwnerID colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close() colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close() and so on for 9 columns…
View Replies !
Multiple Filtering On The Same Field Using A Stored Procedure
Hello, I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below: Input field: Product Description So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned. SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill" I guess there'll need to be a nested Select and loop to parse the space separated input field. Any pointers would be appreciated. Thank you Lee
View Replies !
Multiple Keyword Search In A Stored Procedure
Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+ N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+ N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%''' EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren
View Replies !
Need Help In Understanding Multiple Parameters Sent To A Stored Procedure
OK, 1st, I have looked at every article that has come back on a "Stored Procedures" Search on this site, and am more confused than when I started looking for my answer. This is what I need to do: I need to pass a search sentence to a stored procedure, have the stored procedure break up the space delimited string and then do a "like" and "contains" in the WHERE statement, on what was sent to the stored procedure, and then return the results to a gridview for the person to select which item best answers their search. I am just totally lost with using a stored procedure. I have done this in webmatrix when I coded it all into the aspx page, or into the codepage, but I have never done it with a stored procedure on the sql server, never sent a varible to a stored procedure... and am totlaly lost, or just do not understand how to do it. Any help would be great. Thanks in advance. D4D
View Replies !
Bind To Multiple Tables From Stored Procedure
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?
View Replies !
Multiple Words To Search In Stored Procedure
My scenario is I have a web form with a textbox and a button.Once I enter a string and hit submit button, my stored procedure will have to return the result set.So if my search string is "text book title", then I have to execute the query like :select * from tab1 where col1 like '%text%" or col1 like '%book%" or col1 like '%title%"The problem here is I will never know how many words will be entered to search. So I have to make the statement dynamic.How can I do this in a stored procedure? Any help will be appreciated.Thanks.
View Replies !
Stored Procedure With Multiple Joins Problem
I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs. SELECT Articles.date_published, Articles.headline, Publication.press_contact, Publication.pub_name, Articles.body FROM Articles LEFT OUTER JOIN PressContact ON PressContact.press_id = Articles.press_id LEFT OUTER JOIN Publication ON Publication.publication_id = Articles.publication_id WHERE (Articles.id = @ID) Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that. I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work. Can anyone give me any suggestions? Thanks in advance.
View Replies !
Stored Procedure For Multiple Data Sources
Is there a best way of writing stored procedure to access data from multiple databases. we have an archive database and a live database. I need to retrieve the data from both and merge together, perform calculations and then display the data. Thanks for any inputs.
View Replies !
Stored Procedure And Multiple Keys Which Could Be NULL
I have a table which has 10 columns which make up the secondary key. 1or more of these columns can be set but the remaining columns in thesecondary key will be null. For example :id k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 data-------------------------------------------------0 1 1 - - - - - - - - test01 1 1 1 - - - - - - - test12 1 1 2 - - - - - - - test23 1 1 3 - - - - - - - test34 1 1 3 1 - - - - - - test45 1 2 1 - - - - - - - test56 1 2 2 - - - - - - - test6Each row represents a node in a tree structure, the secondary keycolumns point to which node in the tree. The above example has onenode in tree branch 1, three nodes in tree branch 1-1, one node inbranch 1-1-3 and two nodes in branch 1-2.My question is how can I write a single stored procedure to returnonly the nodes in a given tree branch without needing logic based onlooking for NULL parameters. I have written the stored procedurebelow. If I want to retrieve all nodes in branch 1-1 (ie; test1, test2and test3) then I want be able to call :execute "mysp Test" 1, 1But this only returns a single record, test0. The reason for this isobvious if you look at the stored procedure. What I really need is wayof sayingexecute "mysp Test" 1, 1, NOT NULLso that it returns all records in branch 1-1 where Key3 is NOT NULLbut all subsequent keys are null, ie; Key4-Key10 are NULL. The storedprocedure must work with any number of secondary keys though, so Icould use it to call :execute "mysp Test" 1, 1, 3, NOT NULLwhich would retrieve test4.Can anybody help me out here?Thanks for reading.Here's the stored procedure as it stands :CREATE PROCEDURE [dbo].[mysp Test]( @key1 smallint,@key2 smallint=NULL,@key3 smallint=NULL,@key4 smallint=NULL,@key5 smallint=NULL,@key6 smallint=NULL,@key7 smallint=NULL,@key8 smallint=NULL,@key9 smallint=NULL,@key10 smallint=NULL ) ASselect * from TreePositionTestwherekey1=@key1 andkey2=@key2 andkey3=@key3 andkey4=@key4 andkey5=@key5 andkey6=@key6 andkey7=@key7 andkey8=@key8 andkey9=@key9 andkey10=@key10GO
View Replies !
Returning Multiple Rows From A Stored Procedure
Hi,I have the following stored procedure that does some processing andputs the result in a temporary table. I tried several things thatprocedure to display output that I can access with ADO.Net, but itdoesn't work. It doesn't even display the result in the query analyzerunless I add SELECT @ReturnFullNameAny help?The stored procedure:CREATE PROCEDURE sp_SEARCH_MULTIPLE_NAMES @search4fatherOfvarchar(255), @maximum_fathers int = 100, @ReturnFullName varchar(255)Output....SELECT @ReturnFullName = name FROM #FULLNAME------------------------------------------------To Execute the stored procedure:DECLARE @test varchar(255)EXEC sp_SEARCH_MULTIPLE_NAMES @search4fatherof='مريم',@returnfullname=@testPRINT CONVERT(varchar(255), @test)
View Replies !
Insert Multiple Rows Using A Stored Procedure
I'm writing a Intranet web application to allow users to add presentation files to a web site for others to download. The presentations are to be grouped by categories, however I want them to be able to create additional categories if needed. I have created two tables. Table 1 - PresentationCategories Table 1 Fields - ID, Category Table 2 - PresentationFiles Table 2 Fields - ID, Name, Description, Filename, Filesize, CategoryID On my web page I want to call a stored procedure to insert records into the PresentationFiles table. I have check boxes on the web form for all the possible categories that exist. A user can check each category that this presentation applies too. In my stored procedure, how do I accomplish inserting a record for each category that is selected on the web form? I'm guessing that I'll need to pass the categoryID's parameter into the procedure as a delimited string and then process this string for each categoryID and insert records into the PresentationFiles table using a While loop. I'm just not clear on how this is accomplished. Any advice on how to do it differently or other resources that you can point me to is very much appreciated.
View Replies !
Calling A Stored Procedure On Multiple Records
I have a stored procedure on an SQL Server database which displays statistical data for a single record. The example below selects a user ID as a parameter and displays the user's name and the total amount of transactions he has made: CREATE PROCEDURE dbo.GetUserStats @UserID BIGINT AS DECLARE @TempTable TABLE ( UserID BIGINT, UserName VARCHAR(60), TotalAmt FLOAT ) INSERT INTO @TempTable (UserID, UserName, TotalAmt) SELECT u.RecID, u.LastName + ', ' + u.FirstName, (SELECT SUM(t.Amount) FROM Transactions t WHERE t.UserID = @UserID) FROM Users u WHERE u.RecID = @UserID SELECT * FROM @TempTable GO So if I execute this amount entering a single ID, it returns a single row for that user: UserID UserName TotalAmt -------------------------- 1 Doe, John 100.00 What I would like to do is create another stored procedure which calls this one for every user returned in a query, thus returning the same data for several users: UserID UserName TotalAmt -------------------------- 1 Doe, John 100.00 2 Smith, Bob 123.45 3 Blow, Joe 150.55 Is there a way to re-use a stored procedure within another, based on the results of a query?
View Replies !
Stored Procedure Problem Using Multiple Processor
Hi everybody, When we execute some stored procedure with the multiprocessing enable, the stored procedure isn't working correctly. The stored procedure is starting but won't finish it's execution. We start the same stored procedure with just a single processor enable and it's working. I check to code into the stored procedure and everything seems ok. We run a SQL Server 7.0 with Service Pack 2 on a WinNT 4 Service Pack 5. Someone have an idea for this problem or a point to check. Mathieu Noël CIQ
View Replies !
Multiple Result Sets From A Stored Procedure
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet ' CREATE PROCEDURE [dbo].[SomeSP] @SomeParam int AS BEGIN SET NOCOUNT ON; declare @SomeScalar int exec @SomeScalar = SomeSPThatReturnsAnIntAndAResultSet @SomeParam if @SomeScalar = 0 BEGIN SELECT SomeField FROM SomeTable END END
View Replies !
Stored Procedure With Multiple Results Sets
Can a report be designed to use a stored procedure that returns more than one result set? I tried it expecting to see both results sets in the designer, but I get a table of data from the first resultset, and the field name refresh picks up only fields from the second resultset. Thanks
View Replies !
Stored Procedure Returning Multiple Rows
I have a stored procedure which return a single value and one which return multiple rows between two colums. In my code for the procedure which returns a single value i use (executescalar) which works fine. I am not sure what command to use in my code when i am calling the stored procedure that returns multiple rows between colums. Any help would be appreciated. Thanks.
View Replies !
Stored Procedure - INSERT With Multiple SELECTs
Here's my stored procedure: ALTER PROCEDURE sproc_InsertOrder ( @CustID tinyint, @Size varchar(50), @Crust varchar(50), @total smallmoney ) AS INSERT INTO tblOrders (CustID, SizeID, CrustID, Total) VALUES(@CustID, SELECT SizeID FROM tblSizes WHERE @Size = SizeName, SELECT CrustID FROM tblCrusts WHERE @Crust = Crust, @total) SELECT SCOPE_IDENTITY() RETURN And my tables: tblOrders ------------------------------------------- OrderID | CustID | SizeID | CrustID | Total ------------------------------------------- tblSizes -------------------------- SizeID | SizeName | Price -------------------------- tblCrusts ------------------------ CrustID | Crust | Price ------------------------ My stored procedure is giving me an error, it says "Unable to parse query text", and I'm certain the problem is with the SELECT statements. What am I doing wrong?
View Replies !
Trigger Firing Multiple Stored Procedure
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure. CREATE TRIGGER tr_new_file ON FTPLog AFTER INSERT AS SET NOCOUNT ON DECLARE @filename varchar(50), @logtime datetime DECLARE c1 CURSOR FOR SELECT filename, logtime FROM inserted OPEN c1 FETCH NEXT FROM c1 INTO @filename, @logtime WHILE @@fetch_status = 0 BEGIN EXEC sp1 @filename, @logtime FETCH NEXT FROM c_inserted INTO @filename, @logtime END CLOSE c1 DEALLOCATE c1 END PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late. PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine. SOLUTIONS and SUGGESTIONS highly appreciated.
View Replies !
Returning Multiple Values From A Stored Procedure.
my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column. Is there a way to return the content of these two tables? Is there a way to return a table from the stored procedure? Thanks in advance waamax
View Replies !
How Do I Enter NULL In A Table Cell In The Enterprise Manager UI For Table Data Entry?
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View Replies !
|
TRACKER
