Can someone please let me know if there is a way I can get a list tables and the stored procedures
that reference that table?
Is there someway I can pull this from a script?
Thanks for your help.
Dianne
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'
How do I get a list of the stored procedures currently in the database? Is there a quick way to edit them?
Thank you!
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~
Thank you
Hello
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?
Thanks
jenn
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?
Any help much appreciated!
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.
Thanks,
Larry
Hi
This is kind of a visual studio question, but pertinent to db' s - due to many changes of our application I know that there are many stored procs in the database that are no longer used. Since I cannot get a list from the programmers, is there any way to get a list out of visual studio, of every SQL stored procedure called in that project? (Going through each form manually will just be too time consuming for me or the programmers)
Anyone know any tricks??
thx
Des
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
Hi All,
For listing login details we have sp_helplogins 'LOGIN NAME'
In the same way i want ti know for particular procedure.
can any one help in this.
Thanks in advance.
malathi
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.
View 3 Replies View Related
hii
I have a problem in when i pass xml as input to stored procedure. The problem i m facing is that :
Say i have a table which has id as primary key in it.Now in my sp i want to delete some rows from that table and in xml i am passin more than 1 id ( it can be four even).
But before deleteion i want to check that delete the with that ID iff the ID exists else raise error for the id w/c does not exist like 'This Id does not exist.'
what i want is to loop between the id's which are being passed as i/p to sp..
and delete the row if it exists elso diaplsy message this id does not exist..
somewhat like this
If Exists(Select ProfileName From Area52 Where Area52ID in
( Select T.Item.value( '@Area52ID', 'uniqueidentifier')
FROM @XMLString.nodes('Area52') AS T(Item) ))
Begin
Select * From Area52Docs
WHERE Area52ID in
( Select T.Item.value( '@Area52ID', 'uniqueidentifier')
FROM @XMLString.nodes('Area52') AS T(Item) )
DELETE FROM Area52Docs
WHERE Area52ID in
( Select T.Item.value( '@Area52ID', 'uniqueidentifier')
FROM @XMLString.nodes('Area52') AS T(Item) )
DELETE FROM Area52
FROM @XMLString.nodes('Area52') AS T(Item)
where Area52id=T.Item.value( '@Area52ID', 'uniqueidentifier')
select T.Item.value( '@Area52ID', 'uniqueidentifier') FROM @XMLString.nodes('Area52') AS T(Item)
Select 12
End
Else RaisError('This Record Does Not Exist',11,1)
the thing i want to do is as above..but its not workin..it works only for the first id int he list of id's
plzz do help...
How can I list all stored procedure in a SQL 2005 database with C# ?
Thanks
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.
Thanks,
Allen K.
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.
View 1 Replies View RelatedI 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
View 8 Replies View RelatedI would like to write a store prodecure to return a month:
My output:
Wk1 = July
Wk2 = July
Wk3 = July
Wk4 = July
Wk5 = Aug
and so on..
then i create list of array like below:
The counter for insert the week one by one
DECLARE @TotalWeek INT, @counter INT
DECLARE @WeekNo varchar, @Month varchar
SET @WeekNo = '4,9,14,18,22,27,31,35,40,44,48,53'
--this is weekno,if less than 4, month is july, lf less than 9, month is august and so on
SET @TotalWeek = 53
SET @counter = 1
[Code] ....
Is there a built in stored procedure that would allow me to list outthe database permissions assigned to a particular user or role?
View 2 Replies View RelatedHi,
I am writing one program in vb.Net where I need to bind a treeview with all the SSIS packages stored in SQL server MSDB database.
Is it possible to get all the SSIS packages by using T-SQL query or Object model ?
Thanks
Hi All,
I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.
Thanks
Chris
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:
Code:
employeeList = 'name1','name2',name3','name4','name5'
SQL="sp_REVIEW @ACTION='lde', @CURRENT_USER='" & currentUser & "', " &_
"@EMPLOYEE_LIST='" & employeeList & "'"
Here is the stored procedure
Code:
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.
Here is how I wrote my stored procedure:
CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@abrID int,
@ddo varchar(50),
@ay varchar(50),
@strategy varchar(10),
@budgacct varchar(10),
@budgobj varchar(10),
@origamt
varchar(50),
@incrdecr varchar(50),
@review char(10),
@abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @review
WHERE ABR_ID = @abrID
UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay,
ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct,
ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr)
WHERE
ABR_DETLS_ID = @abrdetlsID
GO
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?
Thanks for any help.
-D-
How can I get the none system(user defined) stored prcedure list and its content by sql script?
View 3 Replies View RelatedI 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?
Thanks!
Hi,
newbie to sql2005. I am trying to write a VB.NET program to list all ssis packages we have stored in a file folder (not in MSDB) where both SqlServer and ssis servers are located. These packages currently running on daily basis by automated jobs with no problem.
I have found the following link on the web that includes VB.Net program. If you scroll down under Example (SSIS Package Store) , you'll see a VB.NET program that I am trying to get it to work for me but keep getting error message like "Cannot find folder......"
http://msdn2.microsoft.com/en-us/library/ms403343.aspx
My goal is to:
1. List all ssis packages stored in the file systems (.dstx)
2. List DB source and distination providers(tables in/out) in a ssis package
3. List properties for DB providers
4. List FlatFile & Excel file provider properties in a ssis package.
My assumption, but not sure, is that I may need to register this file folder (where packages are located) to the SqlServer if that is not done already. Any command or sys table in Sql2005 to figure this out?
I would appreciate any help on this in advance.
Thank You.
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
AS
INSERT RemSerials (EmployeeID,
First_Name,
Last_Name,
HAddress,
City,
HState,
Zip,
Phone_Home,
Phone_Cell,
Monitor1,
Monitor2,
PIX_ASA_Box,
System_Case,
Batt_APC)
VALUES (@EmployeeID,
@First_Name,
@Last_Name,
@HAddress,
@City,
@HState,
@Zip,
@Phone_Home,
@Phone_Cell,
@Monitor1,
@Monitor2,
@PIX_ASA_Box,
@System_Case,
@Batt_APC)
SELECT @current_count = Count(*) From RemSerials
When trying to access the stored procedure from VB.NET 2008 Express, it says, "Could not find stored procedure 'spInsertSerialInfo'."
I'm listing the code as below.
Code Snippet
cmd.CommandText = "spInsertSerialInfo"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.Add("@EmployeeID", SqlDbType.NChar, 10).Value = txtEmployeeID.Text
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.
Any help would be greatly apreciated
I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?
Thanks for tips
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 the following ASP code that builds part of the example SQL statement below (it's the same SQL as in my earlier thread here (http://www.dbforums.com/showthread.php?t=1214044) but a very different question):
if sFindTicketEventId > 0 then sSQL = sSQL & " AND [tblEvents].[id]=" & sFindTicketEventId
if sFindTicketStandId > 0 then sSQL = sSQL & " AND [tblStands].[id]=" & sFindTicketStandId
SELECT
[tblC].[id] AS CombinationID,
[tblC].[availability],
[tblC].[description],
[tblC].[price] AS combinationPrice,
[tblC].[combination_open],
[tblT].[TicketID] AS TicketID,
[tblT].[price] AS ticketPrice,
[tblT].[availability],
[tblT].[ticket_open],
[tblT].[quantity],
[tblT].[event_name],
[tblT].[event_open],
[tblT].[stand_name],
[tblT].[stand_open],
[tblT].[admission_start_date],
[tblT].[admission_end_date],
[tblT].[date_open],
[tblT].,
[tblT].,
[tblT2].[description],
[tblT2].[admin_description]
FROM(
SELECT
[tblCombinations].[id],
[tblTickets].[id] As TicketID, [tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
FROM [tblCombinations]
LEFT JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblCombinations].[id]
LEFT JOIN [tblTickets] ON [tblCombinations_Tickets].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMinQuantities] ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS [tblBookingMaxQuantities] ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
[B]AND [tblEvents].[id]=2
[B]AND [tblStands].[id]=3
--AND [tblAdmissionDates].[id]=@admissionDateId
--AND [tblBookingDates].[id]=@bookingDateId
--AND [tblTicketConcessions].[id]=@concessionId
--AND [tblBookingMinQuantities].[id]=@bookingMinQuantityId
--AND [tblBookingMaxQuantities].[id]=@bookingMaxQuantityId
--AND [tblMemberships].[id]=@membershipId
GROUP BY
[tblCombinations].[id],
[tblTickets].[id],
[tblTickets].[price], [tblTickets].[availability], [tblTickets].[ticket_open],
[tblCombinations_Tickets].[quantity],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date]
) as [tblT]
JOIN [tblCombinations] as [tblC] on [tblT].[id]=[tblC].[id]
LEFT JOIN [tblTickets] as [tblT2] on [tblT].[TicketID]=[tblT2].[id]
I want to turn this SQL into a stored proc; there are currently about 8 parameters that I want to pass into it. The field value for each will be either NULL or a positive integer, and the paramater will be passed in as an integer.
If the passed parameter value is a positive integer then it should return all records where the corresponding field value matches that integer. If the passed parameter is 0, it should return all rows regardless of whether the field value is an integer or NULL.
And I can't for the life of me figure out how to do it. Do I need an IF statement in there or something?
:confused:
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.
UserIds
----------
UserId - int, primary key
Username etc
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.
Nate Hekman
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies View RelatedHi..
I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.
I was doing something like..
Create Procedure proc
(
@Items varchar(100) --- List of numbers
)
AS Begin
Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)
This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."
Can any1 please help me out with this ASAP??