Effect Of A SELECT Stored Procedure On Tables Or Database
Jul 25, 2007
Hi all,
I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.
For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?
View 4 Replies
ADVERTISEMENT
Feb 27, 2007
How do I correctly query from a different database table in a stored procedure?
Here is the procedure:
CREATE PROCEDURE spInsertRegistration
@firstname varchar(25),
@lastname varchar(25),
@job_title varchar(50),
@manager varchar(50),
@cost_center char(3),
@address varchar(50),
@city varchar(50),
@state_id INT,
@zip char(5),
@email varchar(50),
@phone char(10),
@roommate varchar(50),
@arrival datetime,
@departure datetime,
@special_needs varchar(500),
@result INT OUTPUT
AS
DECLARE @stateabr VARCHAR(2)
SET @result=0
SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id
INSERT INTO registration_form
VALUES(@firstname,@lastname,@job_title,@manager,@cost_center,@address,@city,@stateabr,@zip,@email,@phone,@roommate,@arrival,@departure,@special_needs)
IF @@rowcount>0
SET @result=1
GO
This is the specific line where I'm trying to access a different database:
SELECT @stateabr=state_abr FROM Public.dbo.state WHERE state_id=@state_id
Thank you.
View 3 Replies
View Related
Feb 28, 2007
I want my application to create a new database/tables when run for thefirst time. I have created a stored procedure to create the newdatabase named "budget". When I run the stored procedure, it createsthe budget database but the tables are created in the "master"database. Please help.
View 6 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Jul 20, 2005
Is it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View 1 Replies
View Related
May 1, 2008
Hello all, thanks in advance for any advice here.
My question is, what's the effect of the Enterprise Manager > Tasks > Shrink Database function? It seems to reduce the used space on the device. Testing it on some dev machines, I've seem to have gotten back as much as 20g of space. I know it should grow back to that, but the time it took was minimal, and it didn't seem to affect my developers. They tend to add alot and delete alot during testing. What negative effects of running this should I look out for? Will it affect the DB long term? Is is preferable to schedule once a month or so? Is this done on Production DB's?
Thanks for any guidence on the usage of this.
Bob
Not a downstroke, fistpicker
View 6 Replies
View Related
Jun 13, 2001
Hi
I had run a stored procedure in my server that update statistics against all user defined tables in my database (MSSQL 7.0).
Since then I am getting errors in my ASP application where I am reffering to adovbs.inc.
Here is an example of errors I get.
Microsoft VBScript runtime error '800a0411'
Name redefined: 'adOpenForwardOnly'
/Essai/adovbs.inc, line 14
Below is the stored procedure I have run against the database.
Can anybody help tank you guys.
CREATE PROCEDURE update_all_stats
AS
/*
This PROCEDURE will run UPDATE STATISTICS against
ALL user-defined tables within this database.
*/
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @tablename_header = "Updating " +
RTRIM(UPPER(@tablename))
PRINT @tablename_header
EXEC ("UPDATE STATISTICS " + @tablename )
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "************* NO MORE TABLES" +
" *************"
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated FOR ALL tables."
DEALLOCATE tnames_cursor
View 1 Replies
View Related
Mar 28, 2007
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
View 9 Replies
View Related
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
May 25, 2008
Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly?
DECLARE @Value1 DECIMAL(7,2)
DECLARE @Value2 DECIMAL(7,2)
SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date
View 9 Replies
View Related
May 4, 2006
I have the stored procedure snipet below and it does not return anything.
What am I doing wrong here? I works ok if I need all the records for the dataset and don't use the WHERE and LIKE.
CREATE PROCEDURE dbo.search
@Field VARCHAR (20), @KeyWord VARCHAR (200), @errCode INT OUTPUT
ASBEGIN
DECLARE @guid uniqueidentifier DECLARE @subject NVARCHAR SELECT @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord
SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
Thanks,
Zath
View 9 Replies
View Related
Jan 12, 2001
In Informix you can use a stored procedure as part of a SELECT statement:
SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date)
FROM order_table
business_day_diff is a stored procedure that uses a custom calendar to compute
business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.
Does anyone know of a straightforward way to accomplish the same purpose?
View 2 Replies
View Related
Aug 12, 2007
Say I have the following stored procedure that takes in 1 parameter, and to call it I would do this: exec stored_procedure_name 'param'
And it returns a result with 3 fields: column1, column2, column3
Now I'm only interested in getting the results for column1, is there a way to call the stored procedure to only return column1?
I tried this: select column1 from (exec stored_procedure_name 'param')
It doesnt' work...
View 2 Replies
View Related
Dec 21, 2004
Hello!
How do you use the value of one select statement within another select statement inside the same stored proc?
Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!
I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.
Any suggestions?
View 7 Replies
View Related
Oct 13, 2005
How could I use SELECT TOP in stored procedure
For ex I want convert this line to Stored procedure
"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"
I dont want use ROWCOUNT
because its not working good it will not show some data of some fields
for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:
this is my current code
CREATE PROCEDURE [dbo].[SP_Articles]
(
@Articles int
)
AS
SET ROWCOUNT @Articles
SELECT tblArticles.*
FROM tblArticles
WHERE published = 1 ORDER BY Adate DESC;
SET ROWCOUNT 0
GO
and I use this code to call that from ASP
strSQL = "EXECUTE SP_Articles @Articles = " & intArticles
rsArt.CursorType = 1
rsArt.Open strSQL, strCon
so just tell me how to use TOP in stored procedure
Thanks
View 9 Replies
View Related
Jan 30, 2004
Hi everybody,
How can I select records from a SP?
For example:
My SP is the following:
CREATE PROCEDURE [dbo].[spExample]
AS
Declare @SELECT_Text AS varchar(500)
Set @SELECT_Text='SELECT * FROM dbo.ExampleTable'
exec(@SQL_Text)
GO
I want to select from it:
SELECT *
FROM dbo.spExample
It doesn't work. Then how can I use the result of the SP???
View 2 Replies
View Related
Mar 16, 2004
Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?
In other words, if I have a stored proc whose last command is:
selectt.[Region_Code],
t.[Country_Code],
sum([Total]) as [Total]
from#tmp t
group by[Region_Code],
[Country_Code]
order by[Region_Code],
[Country_Code]
Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!
View 5 Replies
View Related
Sep 19, 2013
I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.
View 4 Replies
View Related
Jul 20, 2005
HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks
View 6 Replies
View Related
Jul 14, 2006
I have code simililar to the following in a stored procedure:
CREATE PROCEDURE GetGroupMembers
(
@GroupID INT
)
AS
SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID
FROM THonorsMembers LEFT OUTER JOIN
TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN
VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID
WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL
ORDER BY TMembers.MemberID
RETURN
GO
When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.
Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.
View 3 Replies
View Related
Jan 8, 2008
I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:
sp_MyProcedure 'Param1', 'Param2'
My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:
********************************************************************************************************
CREATE TABLE [dbo].[#tmpTable] ([RetValue] NVARCHAR(30))
INSERT INTO [#tmpTable]
EXEC sp_MyProcedure 'Param1', 'Param2'
SELECT * FROM AnotherTable
WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)
drop table [#tmpTable]
********************************************************************************************************
That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?
Thanks in advance,
JimmyR
View 7 Replies
View Related
Apr 20, 2007
hi everyone,
in SQLEXPRESS if I execute
'use SqlTrackingDb exec dbo.GetName'
I can see all entries in GetName.
But I do not know if the following select statement is possible for a stored procedure in e.g. visual studio:
select * from dbo.GetName where Id = "001";
Any ideas?
Thanks in advance,
best regards bg
View 10 Replies
View Related
Dec 7, 2007
I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
View 4 Replies
View Related
Mar 7, 2008
Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
View 4 Replies
View Related
May 12, 2004
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
View 1 Replies
View Related
Nov 15, 2006
Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
View 1 Replies
View Related
Oct 22, 2007
Hi
I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.
I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.
Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.
Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.
Thanks
View 13 Replies
View Related
Oct 17, 2006
Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
View 2 Replies
View Related
Apr 28, 2008
Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.
INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
Table2
(NEW DATA)
SYMBOL LAST TRADE
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
OUTPUT DATA:
(OLD DATA) (NEW DATA)
as
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
View 1 Replies
View Related
Oct 14, 2006
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w
ten sposob co napisalem kilka postow wyzej (select ID as UID from
aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest
wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
View 6 Replies
View Related
Apr 4, 2007
i have this stored procedure: ALTER PROCEDURE dbo.SearchContact@searchCriteria nvarchar(128)AS Select FstNam1,FstNam02 from Contacts where FstNam1 like '%'+@searchCriteria+'%' RETURN In my .aspx i have a SqlDataSource named SqlDataSource1 which have asocciated this stored procedure to select operation and parameter source of 'searchCriteria' is Control and ControlID is "TextBox1". Also i have a gridview with source this sqlDataSource1 and a button . When i click this button i want to take value entered in textbox and send it to above stored procedure and show returned infos in my gridview. i put in Button1_Click(object sender, EventArgs e){ SqlDataSource1.SelectParameters["searchCriteria"].DefaultValue = Session["searchContact"].ToString();xxxxxx } what i need to have to xxxxx to work fine ? I searched for a solution and i find a lot of posibilities, but all are fragmented.........pls give a solution ...thx for help. P.S: Sorry for my english.
View 15 Replies
View Related
Apr 23, 2007
Hi
I have a table with the columns
Id, Date, Downtime, FromDate, ToDate
How the data should look like in the table
33 2007-04-17 00:00:00.000 2 2007-04-17 15:55:52.000 2007-04-17 15:58:17.000
34 2007-04-17 00:00:00.000 68 2007-04-17 15:58:17.000 2007-04-17 17:06:27.000
35 2007-04-17 00:00:00.000 1 2007-04-17 17:06:27.000 2007-04-17 17:06:37.000
36 2007-04-18 00:00:00.000 2 2007-04-18 15:55:52.000 2007-04-18 15:58:17.000
37 2007-04-18 00:00:00.000 68 2007-04-18 15:58:17.000 2007-04-18 17:06:27.000
38 2007-04-19 00:00:00.000 1 2007-04-19 17:06:27.000 2007-04-19 17:06:37.000
I shall generate a report looking like this(Date column should be distinct in a way):
2007-04-17
2 2007-04-17 15:55:52.000 2007-04-17 15:58:17.000
68 2007-04-17 15:58:17.000 2007-04-17 17:06:27.000
0 2007-04-17 17:06:27.000 2007-04-17 17:06:37.000
2007-04-18
2 2007-04-18 15:55:52.000 2007-04-18 15:58:17.000
68 2007-04-18 15:58:17.000 2007-04-18 17:06:27.000
2007-04-19
1 2007-04-19 17:06:27.000 2007-04-19 17:06:37.000
This would been pretty easy in C#, but I need some help to solve this in sql ( the report takes an sql function) Does anyone know if this is possible in sql?
Thanks for help
View 3 Replies
View Related
