View Stored Procedures With Transact SQL

May 25, 1999

Hi

Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.

I would appreciate any reply !

Thanks you for your time.

View 1 Replies


ADVERTISEMENT

Way To View All Stored Procedures

Jul 20, 2005

I have MS SQL Server 7 and over the years have built quite a fewStored Procedures. It would be extremely convenient if I could print outor view ALL the text of ALL the stored proceudres at once. Is there a way ican do this? Is tehre a way I can make a quick print out?

View 4 Replies View Related

View Or Stored Procedures

Jul 20, 2005

I have a very complex view which we've been working with for sometime. As we have progressed with this view, we have reached the stagewhere the data is generated into a table which is then used in ourreporting application. This has improved reporting performanceconsiderably.There is a stored procedure which copies the data from the view into atable. Very simple select into statement.However, I was wondering if I actually held the query code in thestored procedure, would this improve performance ?The only user for this would be the server for getting to the data inthe view and generating into a table.I know it's a very general question, but would I see any performancedifference doing this as an SP instead of an SP calling a view ?Would appreciate any constructive comments. Not posting the view asit'll be too long, but FWIW, using SQL 7.

View 1 Replies View Related

SELECT Data: Stored Procedures Or View?

May 10, 2007

Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot! 

View 3 Replies View Related

How To Grant Permission To View Stored Procedures?

May 21, 2007

On our production SQL 2005 servers I want to give developers readonly access to each user database and also give them the ability to see stored procedures. Readonly is handled through db_datareader, but how do I give them the ability to see stored procedures without granting permission to execute them?

Thanks, Dave

View 4 Replies View Related

Stored Procedures Not Possible As Data Source View?

Apr 3, 2007

only the tables and views are shown in the wizard (BIDS) thanks...

View 1 Replies View Related

Transact SQL :: Script Out Many Stored Procedures At Once?

Mar 10, 2010

I found lots of methods for individually scripting out stored procedures, but I'd like to know if there's a way to do many (like 100 or so) at once. Ideally, I'd like to execute something that would save all the procedures that start with 'dev_' to the file system somewhere, then delete the dev_ SP's from the database and leave the other SPs that actually get used. Is that possible in one step? 

View 10 Replies View Related

Transact SQL :: Executing Stored Procedures And Threading

Jul 10, 2015

If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?

The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems. 

If I group all stroed procs in one that simply calls all of them in the needed sequence:

exec stproc1
exec stproc2
.....
exec stprocn

Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?

View 2 Replies View Related

Transact SQL :: Executing Stored Procedures Asynchronously / In Parallel

May 30, 2015

I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.

There are two ways I can approach it

1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.

2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.

View 8 Replies View Related

Transact SQL :: Only Functions And Some Extended Stored Procedures Can Be Executed From Within A Function

Sep 11, 2013

I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.

When I run it in my SQL2008R2 environment I get the error:

Only functions and some extended stored procedures can be executed from within a function.

The SQL2008R2 server is new. What can I look for?

Here's the code for the function:

BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END

View 4 Replies View Related

Transact SQL :: Can Search All Databases And All Stored Procedures With A Certain Column Alias?

Oct 14, 2015

I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]

View 3 Replies View Related

Transact SQL :: How To Find Stored Procedures Without NOLOCK Statements In Database

Jun 15, 2015

I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.

View 8 Replies View Related

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

Aug 10, 2015

One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?

View 2 Replies View Related

Database Stored Procedures Not Showing Up In Database View

Jan 31, 2008

 Hi I have an application that I have started to develop.  I have successfully set the connection to open the SQL Server database that I created.  When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process.  At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again.  I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger 

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

Ow To Use View In Storesd Procedures

Dec 11, 2006

hi,i have created view and i want to use that view in stored procedures.is it possible to use in spc? if means please show some example that how to use,please

View 9 Replies View Related

Creating Transact Procedures

Mar 22, 2008

i need to create procedures with if statements
i am confused since i did not learn much about it

i need to do the following
a)If the employee is not a manager the procedure should print “Name is Individual Contributor”
b)If the employee is a manager, the procedure should list all the employees that report to that manager.
c)If the supplied EmployeeID does not exist, the procedure should print “Employee with ID NNN does not exist.”

This is the table
CREATE TABLE Employee
(EmployeeID int NOT NULL
CONSTRAINT Employee_EmployeeID_PK PRIMARY KEY
, Name varchar(50) NOT NULL
, SIN char(9) NOT NULL
, LoginID varchar(256) NOT NULL
, ManagerID int NULL
, BirthDate smalldatetime NOT NULL
, MaritalStatus char(1) NOT NULL
, Gender char(1) NOT NULL
, HireDate datetime NOT NULL
, VacationHours smallint NOT NULL
CONSTRAINT Employee_VacationHours_DF DEFAULT ((0))
, SickLeaveHours smallint NOT NULL
CONSTRAINT Employee_SickLeaveHours_DF DEFAULT ((0))
, IsManager bit
, NumReports int
, ModifiedDate datetime NOT NULL
CONSTRAINT Employee_ModifiedDate_DF DEFAULT (getdate())

I am thinking of using IsManager as TRUE or FALSE
but i dont know how to put it together

this is my sad try for now
CREATE PROCEDURE ListOrganizationMC
@employeeID int,
@managerID int,
@isManager bit
AS
select name
from employeemc

if @ismanager = 0
Begin
Print 'Name is Individual Contributor'
end
SET @employeeID = @managerID;
go

im sure the top part of the parameters are wrong
i just want to enter one parameter

View 11 Replies View Related

Transact SQL :: Primary Key For A View?

Apr 30, 2015

I am wondering if there is such thing as primary key for a view? Assuming, that my view is based on a single table and it's just a subset of columns (some of them renamed) including PK of the table it's based on.

The reason for my question is this - we're using Reverse POCO generator which automatically generates C# Model class and Configuration file for our tables and views. For the view is lists all the columns as a key and therefore I obviously can not use normal way of updating that view. I posted that as an issue here [URL] .... but I am thinking there is no such thing as the "primary key" for a view.

The query used to generate the classes is extremely complex already but may be it can be modified to get the PK ?

SELECT [Extent1].[SchemaName],
[Extent1].[Name] AS TableName,
[Extent1].[TABLE_TYPE] AS TableType,
[UnionAll1].[Ordinal],
[UnionAll1].[Name] AS ColumnName,
[UnionAll1].[IsNullable],
[UnionAll1].[TypeName],
ISNULL([UnionAll1].[MaxLength],0) AS MaxLength,

[Code] ......

I made a quick Google search and found this [URL] ....

It sounds as an interesting idea to try although I am not sure it will work with POCO Generator. But I'm going to try it now anyway.

View 7 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

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'

View 1 Replies View Related

Transact SQL :: How To Optimize A View That Can't Be Indexed

Jul 17, 2015

I have a view that takes 14 mins to return 3000 rows.  At some points in the execution plan, there are over 4 million rows.  The query for the View has a Distinct, a couple columns are populated by functions, there is a cross join, there is a join on a table with no keys or index, joins on non-key varchar columns and so forth.  It looks a bit like this:

SELECT DISTINCT
TOP 100 PERCENT N.Id, N.CountyName, N.OperationName,
N_LO.Name, N_LO.LastName,N_LO.Address1,
N_TO.Name, N_TO.LastName,
dbo.fn_Sections(N.Shape, s.Points) AS Sections

[code]...

The DISTINCT is in there to get rid of the extra Bob rows, because Bob has many sizes.  I need to keep the multiple sizes on Bob's row, but I don't want multiple rows of Bob.  Is there a more efficient way to do that other than using DISTINCT?

Second, word on the street is that functions are best avoided if you are going for speed.  I don't see a way to use "FOR XML PATH" outside the function, because I can't get it to work on just one column.  So I am looking for a way to produce the multiple values on one row.

View 12 Replies View Related

Transact SQL :: Size On Disk For A View?

Sep 17, 2015

I'm using MS SQL Server 2008R2, Is there a way to find size on disk for view(s) ?

View 3 Replies View Related

Transact SQL :: How To Retrieve All Tables From A View

Jun 29, 2015

We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?

View 6 Replies View Related

Transact SQL :: Running Totals In A View

Sep 14, 2015

I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.

There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.

I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.

Table: deposit    This table will hold all deposits

customerId    (string)
balanceType    (string)
transactionDate   (date) 
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)

The data set for the deposit table could be:

1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100

Table: withdrawal  This table will hold all withdrawals

customerId    (string)
balanceType    (string)
transactionDate   (date) 
amount     (decimal)
transactionText   (string)

The data set for the withdrawal table could be:

2 Bonus2 01-04-2015 -100  Needed 100
2 Bonus2 02-04-2015 -100  Needed 100
2 Bonus2 03-01-2015 -100  Needed 100
3 Bonus2 10-04-2015 -50   Took out 50
3 Bonus2 11-04-2015 -100  Took out 100

[Code] .....

Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate

customerId
balanceType
transactionDate
amount
Total
Text

The view must show this, when selecting all records:

1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100

[Code] ....

And a last view that will show distinct totals for each customer and balanceType

customerId
balanceType
totalBalance

1 Bonus -200
2 Bonus -300
2 Bonus2 -200
3 Bonus2 -550

View 8 Replies View Related

Transact SQL :: How To Encrypt Procedure In The View Itself Using RSA

Oct 27, 2015

How to encrypt the procedure in the view itself using RSA?

View 10 Replies View Related

Transact SQL :: Dynamically Create A View

Jun 25, 2015

Log-in access web-page.When page is accessed a stored procedure is executed that will provide a list of all the RA(Problem -- when a new building is added, this stored procedure must be opened and syntax added)..Results are stored in a drop down list on the page.Create a table holds list of all buildings something similar to this

Create Table [dbo].[BuildingInformation] (
ID INT IDENTITY NOT NULL,
BuildingName varchar(50),
RAName varchar(50)

[code]....

Alter the stored procedure so that when it is run it will iterate the BuildingInformation table and compare the value in BuildingInformation to the values in the field BuildingName of the view [RA].  If all buildings contained in the table [BuildingInformation] exist in the view [RA] nothing will change.  If their are values in the [BuildingInformation] table that do not exist in the view [RA], the view [RA] will be dropped and recreate with a 'Union ALL' for each building in the table

View 6 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Transact SQL :: Backup Primary File Groups With No Procedures?

Aug 20, 2015

I would have to handover my DB with only Primary tables to client as part of SLA. 

I am planning to keep these primary tables on a secondary file-group but how-ever, I will still have my procedures on primary file-group. 

How can I accomplish this with client having no exposure to my stored procedures. 

View 2 Replies View Related

Transact SQL :: Cursor To Create Multiple Dynamic Procedures

Jun 29, 2015

The requirement is create a sql script(1 proc or cursor) which will create multiple procedures dynamically.

Table A
Col1 
Col2

A
Alpha

For Example: If table A has 3 rows(distinct) so 3 procedures will be created dynamically.

Result: 
1 PROC_A_ALPHA
2 PROC_B_BETA
3 PROC_C_charlie

View 6 Replies View Related

Transact SQL :: Error And Transaction Handling For Nested Procedures

Sep 16, 2015

We have a required to run multiple procedures in Single Go . And Error Occurred in any Procedure the it will rollback all the changes( Either all Proc run or None)

DECLARE @StartTime DATETIME=getdate(), @EndTime DATETIME=getdate()-1 , @Message VARCHAR(400)  

BEGIN TRY 
SET XACT_ABORT ON
BEGIN TRANSACTION

EXEC PROC1 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]
EXEC PROC2 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]

[Code] ....

Problem Statement is its not capturing the Error Message from Either Proc1 or Proc 2., its Capturing the Flat Message (The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction). How do i capture the Error Occurred in Proc 1 or Proc 2  into Log Tables.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved