Stored Procedure To List Values From Different Tables
Jan 19, 2006
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.
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 empty employee table and employee_details table. The temp table which i created say it has 10 columns of which 6 are from employees and 4 from employee_details. I have loaded some data into temp table say 10 rows.
Now the stored procedure using cursor should be created such that, it should fetch the rows one by one from temp table and insert the values into employee table(6 columns) and the rest in employee_details table(4 columns). This is the scenario.
Here is the column names of my temp table
CREATE TABLE [dbo].[temp]( [employee_id] [char](7) NOT NULL, [first_name] [char](50) NOT NULL, [middle_name] [char](50) NOT NULL, [last_name] [char](50) NOT NULL, [title] [char](5) NOT NULL,
[Code] ....
Here the last 4 columns belong to the employee_details table. The stored procedure should fetch record by record from temp split and insert into employee and employee_details table.
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.
I have the following, which loads up a product search i now want to be able to sort it based on criteria such as price. This is what i have so far; String str = Session["subCategoryID"].ToString(); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_CategoryResults", conn); command.Parameters.Add("@subCategoryID", SqlDbType.Int).Value = str;command.CommandType = CommandType.StoredProcedure; conn.Open();SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); dgProducts.DataSource = reader; dgProducts.DataBind(); conn.Close(); My question is would i need to have different stored procedures, for when loading it up, and when sorting it by price. This is what my stored procedure looks like;CREATE PROCEDURE stream_CategoryResultsByPrice @subCategoryID INT AS SELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
Hello, I'd greatly appreciated if someone can help me out on this problem: I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure: @strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB?? Your help is greatly appreciated~
How can I send a list(s) of data into a stored procedure for processing?
For instance, I have a table: GroupContacts(groupname, userid, contactid). At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user.
How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records?
I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like....
SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))
The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?
I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.
I need to create a script which iterates over a list of about 40,000 id's. As the list goes through each id other stored procedures are called which do something. Because of access rights on the server im running the script i need to hard code the 40,000 records in my script. The id's are currently in an excel file
What way can i store them in the script, should i use a cursor, and if so how can i create a cursor to hold so many hard coded values
Any help or alternative ideas would be much appreciated
Is there a way to retrieve the parameter list for a given stored procedure?
I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.
I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.I am very new to this and would really appreciate any knowledge anyone could share.
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
I am trying to pass a comma delimited llist of names to a stored procedure. I am getting a syntax error and I can't seem to figure out why. When i plug the names by hand into my select statement in query analyzer it all works fine. Just a little background so i don't have to put all the code in... the list of name is built dynamically. Below are all the code snippets. Thanks for your help in advance. The is the list of names and the call to the stored procedure:
SELECT ww.ORACLE_USER_NAME, ww.LAST_NAME + ', ' + ww.FIRST_NAME as employeeName, ww1.DIVISION + ' - ' + ww1.COST_CENTER + ' - ' + ww1.COST_CENTER_DESC as department FROM WHOS_WHO.dbo.HR_PEOPLE_V ww LEFT JOIN WHOS_WHO.dbo.HR_DEPARTMENTS_V ww1 ON ww.DEPARTMENT_ID = ww1.ORG_ID WHERE ww.SEARCHABLE = 1 AND ww.ORACLE_USER_NAME IS NOT NULL AND ww.PERSON_TYPE = 'Employee' AND (ww.ORACLE_USER_NAME IN (@EMPLOYEE_LIST) OR ww.DEPARTMENT_ID IN (SELECT ud.department_id FROM USER_DEPARTMENT ud WHERE ud.nt_id = @CURRENT_USER)) ORDER BY ww.LAST_NAME, ww.FIRST_NAME
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list. Example: name1 = value1, value2, value3, etc. name2 = value1, value2, value3, etc. name3 = value1, value2, value3, etc.
The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.
Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
I saved a stored procedure (see below) and I'm not seeing listed within the 'Databases' / 'Remote_Serials' (DB name) / 'Programmability' / 'Stored Procedures' folder within the 'Object Explorer' window.
I'm trying to perform a simple add-info-from-form-into-DB process.
Is it because of the way I put together the procedure? I have a feeling that it is (was going off a video demo, which created it within VB.NET 2008 Express, but I'm not able to connect to the DB from there; it says the file is open, when I completely closed out of SQL Management Studio Express).
Code Snippet ALTER PROCEDURE dbo.spInsertSerialInfo @EmployeeID as nchar(10), @First_Name as nvarchar(50), @Last_Name as nvarchar(50), @HAddress as nvarchar(50), @City as nvarchar(30), @HState as nvarchar(2), @Zip as nvarchar(10), @Phone_Home as nchar(15), @Phone_Cell as nchar(15), @Monitor1 as nvarchar(50), @Monitor2 as nvarchar(50), @PIX_ASA_Box as nvarchar(50), @System_Case as nvarchar(50), @Batt_APC as nvarchar(50), @current_count as int OUTPUT
If I change the 'CommandType.StoreProcedure' to 'CommandType.Text' it seems to find it, but then I get another error saying that the @EmployeeID variable has already been delcared and that I need to have a unique variable.
Hi, I m using sql 2005 as a back end in my application... I am useing Store procedure..for my data in grid..
ALTER PROCEDURE [dbo].[ProductZoneSearct] ( @Productid char(8),@Proname char(8),@radius int,@mode varchar(5) = 'M',@Zone nvarchar(1000),) ASSET NOCOUNT ON;Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15)) Insert #Product Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment ,Miles, UserID, Daily, PhoneFrom [tblproduct] Where city IN (@Zone) Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment ,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone From #Product As L Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID Order By DateAvailable
if i pass value in "where city in (@Zone)" and @Zone ='CA','AD','MH' then it can not get any result..but if write where city in ('CA','AD','MH') then it give me perfact result.. I tried to below syntax also but in no any user Where city IN ('+@Zone+') In short if i pass value through varibale (@Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ? Please Hel[p me !!! Thank you !!!
I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
Contacts ------------- UserId - int, the UserId of the user who has the contact ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc: exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8' Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs) CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150) ASBEGIN DECLARE @FileID INT SET NOCOUNT ON /*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate) Select @FileID=@@Identity /*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000) SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
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
