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
ADVERTISEMENT
Apr 3, 2007
only the tables and views are shown in the wizard (BIDS) thanks...
View 1 Replies
View Related
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
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
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
View Related
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
Apr 2, 2004
Hi,
We use stored procedures to populate the properties of data layer objects. Is there any advantage or disadvantage (performance wise) of using "SELECT * FROM Table1" Vs
SELECT COLUMN1, COLUMN2, COLUMN3, ETC FROM Table1.
Using individual column names in the select store procedure is getting difficult to maintain for large number of columns or when columns are added or removed.
Thanks,
Suparna
View 2 Replies
View Related
Jul 5, 2005
Ok, I currently have a query which is basically doing the following:
Code:
SELECT ...
WHERE x IN (101,102...)
I have a couple of questions:
Firstly would making this to a stored procedure really make much of a difference to performance considering the WHERE IN clause.
Secondly how can I pass this list of values to use in the WHERE IN clause to the stored procedure. I tried nVarChar but really wasn't surprised when it didn't work... and can't really think of another clear way.
Thanks in advance,
-D
View 4 Replies
View Related
Jun 17, 2004
Hi,
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it.
2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
Thanks,
Pete
View 4 Replies
View Related
Nov 5, 2004
i want use stored procedures in select query.
for example :
select * from table where (... run a stored procedure ....)
or update from table set a=@a where (... run a stored procedure ....)
how can i do it ?
thx
View 3 Replies
View Related
Sep 17, 2007
Hi there,
I have a database table with a field to specify the username of records that particular user is allowed to access (read only). I want to be certain the user does not have access to any other records. So I'm using a stored procedure to query the table with "WHERE dbuser = USER".
My problem is that the stored procedure is returning an error that the table I'm querying cannot be found, and I suspect it is because the user does not have proper permissions to see those tables, even through the stored procedure.
So my question is, how do I grant the user sufficient access for this stored procedure to work without outright granting them select permission on the tables?
Much appreciated!
View 3 Replies
View Related
Feb 26, 2004
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View 4 Replies
View Related
Mar 21, 2008
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
View 1 Replies
View Related
May 7, 2008
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable
(like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns.
(I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
View 13 Replies
View Related
Jul 20, 2005
Hi,Has any one ever compared the performance of calling a DataTable'sSelect method with a stored procedure doing the same thing?My point is:dataRows = DataTable.Select(filter) is better orPassing paramters to stored procedure?The datatable holds about 500-700 rows at any given time.If I select one of the approaches the business logic will go intorespective layers.With dotnet in picture what would be a good approach- Have the data in Datatable and do a filter on the data or callstored procedures which has been the convention.Can some one pl. suggest?
View 3 Replies
View Related
Jun 8, 2006
Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?
I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:
CREATE TABLE < t > FROM SELECT id, name FROM < w > ...
Thank you!
View 6 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Jun 9, 2013
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies
View Related
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
May 21, 2008
Can anyone tell me how to access data from Stored Procedures using data adapters? My task is to select a row which is valid with data particular value. Suppose i had to get all values of particular user after validating username and password. Can anyone give me some hint regarding store procedure and retriving data from stored procedure using data adapters ? How can i bind data to dropdownbox of one field in the table using datasets and data adapters? How can i insert data in database using data adapters?Can any one solve this?
View 1 Replies
View Related
Oct 16, 2007
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies
View Related
May 26, 2015
How can I select data from a table and row counts from multiple tables in a view. For example:
Select * from Settings -- it gets 1 row only
Select count(*) from NewApps where Status = 'False'
Select count(*) from myUsers where Status = 'Pending'
I just want to get them all in 1 view...
View 1 Replies
View Related
Aug 18, 2004
Hello,
How can I get the name of the fields along with datatypes of a stored procedures.
Thanks in advance,
Uday.
View 1 Replies
View Related
May 2, 2007
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.
View 2 Replies
View Related
Aug 4, 2015
How do you run a stored procedure on PDW via SSIS? I've tried Execute SQL Task and Execute T-SQL Task but in both cases the task will run and complete almost immediately. Task shows success, no errors, but nothing happens in PDW. PDW admin console does not even register the query. Procedures run fine manually from SQL Server Object Explorer connection.
View 3 Replies
View Related
Jul 23, 2015
How to use Stored Procedures in SSIS?
View 2 Replies
View Related
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
Mar 21, 2008
I have my database: "RequestTrack"
My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button:
txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text
**After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
View 4 Replies
View Related
Jul 4, 2005
Hi all,
I am in the position where I have to transfer data from an old database
schema to a new database schema. During the transfer process alot of
logic has to be performed so that the old data gets inserted into the
new tables and are efficiently done so that all foreign keys are
remained and newly created keys (as the new schema is Normalised alot
more) are correct.
Is it best if I perform all this logic in a Stored Procedure or in C# code (where the queries will also be run)?
Tryst
View 12 Replies
View Related
Mar 13, 2001
I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO
Dave
View 3 Replies
View Related
Aug 17, 2000
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins
View 6 Replies
View Related
Mar 20, 2006
Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:
CREATE PROCEDURE KPISU_F_TotalByF
@inputT_From varchar(10),
@inputT_To varchar(10),
@TableName varchar(1000)
AS
-----------------------------------------------------
--------input variable-------------------------------
DECLARE @inputTerm_From varchar(10),
@inputTerm_To varchar(10),
@sql_empty varchar(2000),
@sql_refresh varchar(2000)
----------------------------------------------------
IF EXISTS (select * from tempdb.dbo.sysobjects
where id LIKE object_id('tempdb..#tmpOTLTotalByF'))
DROP TABLE #tmpOTLTotalByF
CREATE TABLE #tmpOTLTotalByF (Faculty varchar(50),Term_From varchar(10), Total_G12 int, Total_G3 int, Total_G4 int, Total_Faculty int)
DECLARE @iFaculty varchar(50),
@iTerm_From varchar(10),
@iTotal_G12 int,
@iTotal_G3 int,
@iTotal_G4 int,
@iTotal_Faculty int
SET @iTotal_Faculty = 0
SET @iTotal_G12 = 0
SET @iTotal_G3 = 0
SET @iTotal_G4 = 0
DECLARE su_OTL_F_cursor CURSOR
FOR
SELECT Faculty, Term_From, SUM(Grades_12), SUM(Grades_3), SUM(Grades_4)
FROM #tmpOTLTotalByFaculty
GROUP BY Faculty, Term_From
OPEN su_OTL_F_cursor
FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql_refresh = 'INSERT '
SELECT @sql_refresh = @sql_refresh + @TableName
SELECT @sql_refresh = @sql_refresh + ' VALUES (' + @iFaculty + ', ' + @iTerm_From + ', ' + @iTotal_G12 + ', ' + @iTarget_12 + ', ' + @iTotal_G3 + ', ' + @iTarget_3 + ', ' + @iTotal_G4 + ', ' + @iTarget_4 + ', ' + @iTotal_Faculty + ')'
SET @iTotal_Faculty = 0
SET @iTotal_Faculty = @iTotal_G12 + @iTotal_G3 + @iTotal_G4
INSERT #tmpOTLTotalByF VALUES (@iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4, @iTotal_Faculty)
Exec ( @sql_refresh)
FETCH NEXT FROM su_OTL_F_cursor INTO @iFaculty, @iTerm_From, @iTotal_G12, @iTotal_G3, @iTotal_G4
END
CLOSE su_OTL_F_cursor -----line 222
DEALLOCATE su_OTL_F_cursor
CLOSE su_OTL_T_cursor -----line 63
DEALLOCATE su_OTL_T_cursor
SELECT * FROM #tmpOTLTotalByF ORDER BY Faculty
SELECT * FROM KPISU_F_OTLTotalByF05 ORDER BY Faculty
GO
EXECUTE KPISU_F_TotalByF '2005', '2006', 'KPISU_F_OTLTotalByF05'
GO
----------------------------------------------------------
I got the following error message:
Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256
Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int.
-----------------------------------------------------------
I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?
Many thanks.
Su"
View 1 Replies
View Related
Mar 28, 2008
Hi,
I am trying to write a stored procedure, which does a couple of things.
First thing is it looks up a persons Location based on an ID number, which is passed from an external Script. This will return four Values from a table.
I want to insert those four values into another table, along with another ID passed to the procedure from the same script.
My question is, what do I do to the script below to get the four values out of the first look up, into the insert?
The Field names returned from the SELECT are, AREA1, AREA2, AREA3, AREA4
Thanks
David
CREATE PROCEDURE UserAssign_Location
@UserID Int, @AreaID Int
AS
BEGIN
SET NOCOUNT ON;
SELECT * From Locations Where EntryID = @AreaID
INSERT INTO Members_Locations (UserID, Location1, Location2, Location3, Location4, Active)
VALUES (@UserID, 'AREA1', 'AREA2', 'AREA3', 'AREA4', 1)
END
GO
View 1 Replies
View Related