Modifying A Lot Of Stored Procedures
I probably know that I don't want to do this, but I have an odd case.
I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.
I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.
I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.
Thanks,
Pete
View Complete Forum Thread with Replies
Related Forum Messages:
Needs Help With Modifying Stored Procedure
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID. I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure? Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View Replies !
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
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 Replies !
[Resolved] Problems Modifying Stored Procedure
I have a sp that I need to modify and having some syntax issues: Here is my script: SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_received, SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS qty_used, SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) - SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS qty_wasted, SUBSTRING(dbo.Job.CompanyJobId, 1 ,3) AS plant_id FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left outer JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid WHERE dbo.Item.UnitOfMeasure = 'TN' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId,dbo.Item.CompanyItemId Now I need to modify this line: SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_received to include: and dbo.Product.CompanyProductId LIKE 'ASPH%' New line should be: SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' and dbo.Product.CompanyProductId when LIKE 'ASPH%'then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_received, I get error saying: Incorrect syntax near the keyword 'and'.
View Replies !
Stored Procedures 2005 Vs Stored Procedures 2000
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 Replies !
All My Stored Procedures Are Getting Created As System Procedures!
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 Replies !
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
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 Replies !
How To Save Stored Procedure To NON System Stored Procedures - Or My Database
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 Replies !
SSIS And Stored Procedures Results Stored In #Tables
Hello I'm start to work with SSIS. We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005. Most of the Stored Procedures ends with the following commands: SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE' EXEC @RETVAL = sp_executeSQL @SQLSTRING How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File) I found a way but I think i'ts only a workaround: 1. Write the #Resulttable to DB (changed Prozedure) 2. create data flow task (ole DB Source - Data Conversion - Excel Destination) Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file? Thanks for an early Answer Chaepp
View Replies !
Stored Procedure Being Saved In System Stored Procedures
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database. For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product. I can't update the sys.Objects or sys.Procedures views in 2005. What effect will this flag (is_ms_shipped = 1) have on my stored procedures? Can I move these out of "System Stored Procedures" and into "Stored Procedures"? Thanks!
View Replies !
Calling Stored Procedures From Another Stored Procedure
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure. The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)? Thanks in advance John
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View Replies !
Stored Procedures
Just a question or 2: Would it be faster in your opinion to attempt to insert a user into a database and should a database user already exist and an exception is caught for duplicate key existing... warn the user that it already exists in the database or rather by using a stored procedure to check. ( I'm trying to remove as much redundancy in code as possible)Now for another instance... I need to create either 3 procedures, 1st to check if a user exists with relevant data... the 2nd to update if there is already data, 3rd to insert should it be null. Or should i just use a single procedure that Checks if the user exists, if the @@rowcount = 1 then update else insert. ( all in 1 compact procedure) Id like to hear some peoples opinions in this matter as everyone seems to have different ways of doing things, I would just like to know whats best.
View Replies !
Stored Procedures
Hi,I have a procedure that insert some values into database. The point is, when somebody doesn't fill up a certain field on the form, I don't want the procedure to insert an empty row into database. The procedure looks like this:@tresc text, @id_test int, @odp text, @correct bit, @odp1 text, @correct1 bit, @odp2 text, @correct2 bit, @odp3 text, @correct3 bit, @odp4 text, @correct4 bit, @odp5 text, @correct5 bit, @odp6 text, @correct6 bit, @odp7 text, @correct7 bit ASDeclare @id_utworzonego_pytania intBeginSet nocount on Insert into Pytanie (tresc,id_test) values ( @tresc,@id_test) select @id_utworzonego_pytania=@@IDENTITY if(@odp is not null) BEGIN Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp,@correct,@id_utworzonego_pytania) END Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp1,@correct1,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp2,@correct2,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp3,@correct3,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp4,@correct4,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp5,@correct5,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp6,@correct6,@id_utworzonego_pytania) Insert into Odpowiedz (odp,correct,id_pytania) Values (@odp7,@correct7,@id_utworzonego_pytania) SET NOCOUNT OFF END And the code:con = new SqlConnection("Data Source=ELF;Initial Catalog=logos;Integrated Security=True"); SqlCommand cmd = new SqlCommand("StoredProcedure2", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@tresc", TextBox1.Text)); cmd.Parameters.Add(new SqlParameter("@id_test", id)); cmd.Parameters.Add(new SqlParameter("@odp", TextBox10.Text)); cmd.Parameters.Add(new SqlParameter("@correct", RadioButtonList2.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp1", TextBox2.Text)); cmd.Parameters.Add(new SqlParameter("@correct1", RadioButtonList1.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp2", TextBox3.Text)); cmd.Parameters.Add(new SqlParameter("@correct2", RadioButtonList3.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp3", TextBox4.Text)); cmd.Parameters.Add(new SqlParameter("@correct3", RadioButtonList4.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp4", TextBox5.Text)); cmd.Parameters.Add(new SqlParameter("@correct4", RadioButtonList5.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp5", TextBox6.Text)); cmd.Parameters.Add(new SqlParameter("@correct5", RadioButtonList6.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp6", TextBox7.Text)); cmd.Parameters.Add(new SqlParameter("@correct6", RadioButtonList7.SelectedValue)); cmd.Parameters.Add(new SqlParameter("@odp7", TextBox8.Text)); cmd.Parameters.Add(new SqlParameter("@correct7", RadioButtonList8.SelectedValue)); con.Open(); cmd.ExecuteNonQuery(); con.Close();It works perfectly if someone fill all the textboxes; but it is not required. So when you leave an empty textbox, the procedure inserts an empty row into database. Please, help!Regards,N.
View Replies !
Using Stored Procedures In Asp.net
Hi,how can I use stored procedures in asp.net. The stored procedure itselve is defined on the sqlserver and works fine.Actual I found out that in my asp.net 2-surrounding there can be the adofunction disabled.I tried to use it with dimension of a new SqlConnection but I'm woundering why within a visual basic part I don't get the codecompletion whenI use the dimensioned object combined with commands.Regards,Ruprecht Helms
View Replies !
Can't See Stored Procedures
Hi, Can anyone help me. I've created a stored procedure in sql server and I'm trying to run it from my asp.net page. On Database Explorer I can't see it, or any for that matter, however I can see tables in the same schema. Also I can't see it when I build a table adapter either. Can anybody help? Thanks Sam
View Replies !
Stored Procedures
cREATE PROCEDURE emp @val varchar(50)AS declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null' execute(@test) GO this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
View Replies !
Help On Stored Procedures
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
View Replies !
Stored Procedures
Hello every one, I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you Thank you
View Replies !
Help With Stored Procedures
Hello I have two stored procedures @ID INT AS SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER FROM (SELECT (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM, (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X and @ID INT AS SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL( (SELECT SUM(TRANSACTION_VALUE) FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1 GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P undefined P The first returns a percentage for player wins, the second gives me a photo and sums the player winnings I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player Thanks in advance for any light you can shine on this.
View Replies !
Stored Procedures
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource? And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"? Now I have around 40 stored procedures, and quite many of them looks the same except the where-part... (Iam just a beginner with SQL)
View Replies !
Stored Procedures
hi i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend looking forward for a replygayathri
View Replies !
I'm New To Stored Procedures
I create my store proc. I want to give it a parameter AccNmbr and I want it to return to me the total of the columns SubNmbr in total variable; total is gonna be an output. So I do this in my Stored Proc: Create Procedure dbo.totalSub@AccNmbr bigint,@total bigint outASSelect @total=sum(SubNmbr) where AccNmbr =@AccNmbr return Go When I run the proc it tells me that parameter total was not provided. I also tried with return (instead of Return @total)it always tells me missing total parameter. But I want total to be my result, not my input. Only AccNmbr is my input.Thanks a lot for your help.
View Replies !
Are Stored Procedures Bad?
I have always thought that you should use stored procedures when ever possible. But, after reading this: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx My world view is shackled. Are stored procedures good or bad?
View Replies !
Stored Procedures ??
hey again.. (btw this topic is unrelated to the other)i have question about stored procedures..what is the advantages of using stored procedures? is there something that can only be done by that? its faster in performance or security reasons ? and also if any1 have a good link with some tutorials about it i would appreciate as well ^^thx for your time
View Replies !
Stored Procedures
hi all..... i am using the codesmith generated classed and its functions..... so how can i add a new stored procedures and how can i call this stored procedures to do the particular actions. pls help me find out if anyone have an idea regarding this problem... thanks
View Replies !
Sql Stored Procedures
Hi, Is it possible to send an array to a stored procedure? this is the case: i have a table called categories and one table called usersincategories, my aspx page has recived the all the categories from the table categories, then when the user has selected afew of the categories i want to send back those to addusersincategories stored procedure to add these users. Do i have to call addusersincategories (SP) for everycategory i have or can i send a list of categories to addusersincategories (SP) that will split the list and insert them into usersincategories ? Patrick
View Replies !
CLR Stored Procedures
Hi, I have some questions: 1. My guess is that a CLR SP is better than the native database SP since the first provides easier debugging....true?2. Why wouldnt one ALWAYS use the CLR SP?3. Does a CLR SP work with SQL Server 2005 Express?4. How can I debug a native SP?Thanks!
View Replies !
Stored Procedures
Hello =)I'm wanting to use stored procedures for SQL2005. I have two questions. My first question is, I want to use a store procedure to verify account information, and, if possible return a value (0-3) based on the status of the user (ex: 0- login OK, 1-login failed, 2-banned, etc)I never used if statements in a stored procedure, so first I want to (1) check user password, return value if its wrong, (2) check if the user is banned and return a value if they are, (3) return a value if their login data is OKWhat would the the stored procedure look like (just the IF statement)? Would it be:if users.banned = true BEGIN return 1END(and so on)It bad to use a stored procedure to do this? My webserver/sql server are on the same server. My next question =)! From ASP.NET 2.0 (C#), how do I get a stored procedure (with parameters)? I can't find any resources on this, and when I do, they suck. If someone could help me out with that =)! It was easy in ASP3. Thanks
View Replies !
Stored Procedures
Hello, First off I'm pretty new to the world of stored procedures. I have made a simple ASP page for computer inventory and it populates three DB's I am trying to relate these tables so that each row in each table relates to one of the other rows in each table. What would be the place to start and/or is there a more proficient way. thanks in advance MattyPee
View Replies !
Stored Procedures MS SQL
I will really appreciate if anyone would be able to help me with the problem of mine... I am building a website where three types of users can access the website... Admin, Students and Teachers... I need to build a stored procedure which allows to sort this thing out... that after going through the stored procedure it would come out with the output value which help direct the user to the appropriate webpage... I need help on building the tables and the stored procedure... Please I am desperate...
View Replies !
Stored Procedures
Hi, I wrote a stored procedure that takes in a value and selects rows based on a match. How can I make it select everything if the value taken by the store procedure is null?
View Replies !
I Need Help With Stored Procedures.
Basically I've got a stored procedure, and I want to declare a variable within that stored procedure then put a value into that varible with a SELECT statement , then INSERT that value, along with some other values, into another table. I hope im explaining myself right.Anyway, here's my code (its wrong)/* This stored procedure adds a new issue*/ CREATE PROCEDURE {databaseOwner}{objectQualifier} [PreciseData_IssueTracker_AddIssue]@moduleId int,@starterUserId int,@typeId int,@subject varchar (50)AS SET @assignedUserId = SELECT userId FROM PreciseData_IssueTracker_Assignments WHERE typeId=@typeId INSERT INTO PreciseData_IssueTracker_Issue( moduleId, starterUserId, assignedUserId, statusId, typeId, subject, startDate)VALUES( @moduleId, @starterUserId, @assignedUserId, 1, @typeId, @subject, getdate())GO
View Replies !
Stored Procedures And VSS
Is there any documentation/blobs for the best practises for storing Stored Procedures (SQL server 2000) in VSS, and rolling back the changes in the SQL server ? I would appreciate to know your views as well. Thanks for your time. =San
View Replies !
How To Use Stored Procedures
Hi All! How can i use stored procedures in my ASP.NET page. I m a newbie in Stored procedures. So please tell me the script of an stored procedure based on following scenario: there is a validity date in a column called dueDate of my table tblValidator. With stored procedure, i want to verify the current date is either greater then dueDate or less. If less then return "ok" else return "denied" Pleas help me in this regards.
View Replies !
Using Stored Procedures
I am getting following error when i execute the stored procedure... Procedure or function TestInsert has too many arguments specified What can i be the reason for this..
View Replies !
Stored Procedures
I am trying to use a STORED PROCEDURE to update data in an SQL Server 2000. I am getting this error: An SqlParameter with ParameterName '@LinkTypeID' is not contained by this SqlParameterCollection. Can anybody help, please. Many thanks Mike STORED PROCEDURE CREATE PROCEDURE spLinkTypeUpd @LinkTypeID [int], @LinkTypeName [varchar](50) AS UPDATE taLinkType SET LinkTypeName = @LinkTypeName WHERE LinkTypeID = @LinkTypeID GO BUTTON CODE Dim connUpdate As SqlClient.SqlConnection Dim cmdUpdate As SqlClient.SqlCommand connUpdate = New SqlClient.SqlConnection("Server=localhost;database=test;UID=sa;pwd=admin") cmdUpdate = New SqlClient.SqlCommand("spLinkTypeUpd", connUpdate) cmdUpdate.CommandType = CommandType.StoredProcedure With cmdUpdate .Parameters.Add("@LinkTypeName", txtLinkTypeName.Text) .Parameters("@LinkTypeID").Value = 34 End With connUpdate.Open() cmdUpdate.ExecuteNonQuery() connUpdate.Close()
View Replies !
Stored Procedures
I have a question on stored procedures in visual studio.net. When I right click on the stored procedure node in the server explorer the only options I get are Refresh and Properties. These are also the only options I get when I right click on Tables or Views. I can’t figure out why I can’t create a new table, view or stored procedure. I believe I have the right permissions set but I can’t figure out why I get no other options. All the help I find on creating stored procedures say to make sure that sql server debugging is enabled for the programs (which it is) and then to right click on stored procedure and select the new stored procedures option from the pop-up window. I do not get this option.
View Replies !
Stored Procedures - Help
Below is a stored procedure i am working with and i am trying to dropthe yesno_holding table if it exists but how do i add it back? Meaningi want it to drop if it exists but i want to add it back if it doesntexist. the reason why i have to drop it is because when i run thestored procedure it creates mulitple rows with the values in them butin my report it takes the top row and insert those values into thereport and i dont want that i want the new values at the bottom row tobe inserted so i need to know how to add the table back once i drop it.Dont know the syntax that should go after the DROP TABLE yesno_holding;line. Any ideas?CREATE PROCEDURE sp_YesNo ASIF EXISTS (SELECT * FROM yesno_holding )DROP TABLE yesno_holding;declare @scheyes intdeclare @scheno intdeclare @schemb intdeclare @scheother intdeclare @howyes intdeclare @howno intdeclare @howmb intdeclare @howother intdeclare @paaexplyes intdeclare @paaexplno intdeclare @paaexplmb intdeclare @paaexplother intset @scheyes = (select count(*) from ConstructionSurvey where sche=1)set @scheno = (select count(*) from ConstructionSurvey where sche=2)set @schemb =(select count (*) from ConstructionSurvey where sche =3)set @scheother = (select count(*) from ConstructionSurvey where not(sche in (1,2,3)))set @howyes = (select count(*) from ConstructionSurvey where howwarr=1)set @howno = (select count(*) from ConstructionSurvey where howwarr=2)set @howmb =(select count (*) from ConstructionSurvey where howwarr =3)set @howother = (select count(*) from ConstructionSurvey where not(howwarr in (1,2,3)))set @paaexplyes = (select count(*) from ConstructionSurvey wherepaaexpl=1)set @paaexplno = (select count(*) from ConstructionSurvey wherepaaexpl=2)set @paaexplmb =(select count (*) from ConstructionSurvey where paaexpl=3)set @paaexplother = (select count(*) from ConstructionSurvey where not(paaexpl in (1,2,3)))GO
View Replies !
Stored Procedures And Other...
Hi,Sorry for completely newbie questions :)* Can you recomend me internet resources on MS SQL Server storedprocedures?* Can MS SQL Server's stored procedures do the same things as Oracle's(I was programing with PL/SQL briefly few years ago)* Is there a special procedural language for SPs in ms sql (smt. likePL/SQL)?I made some simple insert/update/delete stored procedures, but I wouldlike to learn to do much more (like I did with PL/SQL) :)Thanks,Ante
View Replies !
CM For Stored Procedures
What methods are people using out there to keep their stored proceduresunder CM control? We are using CVS for our CM system. Looking for an easyway to get stored procedures scripted out as text so we can check in/checkout of a CM system.Thanks!-- Mike Brown
View Replies !
Stored Procedures
Hi allIm relatively new to using stored procedures and im not sure if it ispossible to do what I am trying to do so any help here is greatlyappreciated. I am using the variable @MachineName which is obviously thelocal machine name mainly in this procedure. What is loop through from thefirst character of the variable to the last and use this data in a selectstatement. I have included the code below for what I have tried so far but Iget an error that "Error 116: Only one expression can be specified in thelist when the subquery is not introduced with EXISTS". So im not sure ifim going about this the right way or not but any help anyone give is greatlyappreciatedThanks/*** Determine Entity Group Memberships*/CREATE PROCEDURE [dbo].[sp_EntityStartup]@MachineName VarChar(50),@UserName VarChar(50)ASDECLARE @MachineLength Char(1) /* Local Machine Name Length */DECLARE @MachInt Char(1) /* Machine Integer Counter */SET @MachInt = 1SELECT @MachineLength = Len(@MachineName)DECLARE @Ttp VarChar(20)WHILE @MachInt <= @MachineLengthBEGINSELECT @Ttp = (SELECT * FROM GrpMachines WHERE MachineGrp LIKELEFT(@MachineName,@MachInt))SELECT @MachInt = @MachInt + 1ENDGO
View Replies !
|
TRACKER
