Is there a way to deal with stray ' in strings, such as: 'SQL Team's', in stored procedures inputs without using variables. Normally I'd correct this by putting + char(39) + ' after that stray apostrophe but when I do that within a stored procedure it spits back an error about the + sign.
Can anyone help?
Just to be clear, the problem I’m dealing with is like the following:
EXEC Database.dbo.StoredProcedure 'SQL Team's'
And I’ve the following as a solution, but it returns “Incorrect syntax near '+'.�:
hi, my question is how to insert into multiple tables using stored procedure? here is my code whcich didnt worked :/USE [DBCars] GO /****** Object: StoredProcedure [dbo].[insertuser] Script Date: 05/23/2008 23:13:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[insertuser] (@Make nchar(10),@Model nchar(10),@City nchar(10),@SellerID varchar(50),@MileAge nchar(10),@Year_Model int) as insert into tbcar values(@Make,@Model,@City,@SellerID,@MileAge,@Year_Model); GO as (@thumb_id varchar(50),@image_id varchar(50),@car_id int) insert into tbimages(@thumb_id,@image_id,@car_id)
I am having a problem with the following SQL code. It will be a stored proc once it works.
I have a variable @CardNumber. The last line of the SELECT Statements says: Where Card.CardNumber=1234/*@cardNumber*/
The Query works when I have the number hard coded on this line. It does not work when I try to use the variable by uncommenting it and removing the 1234.
Please help. This has to be simple!
Thanks!!!
- Mike
Declare @Balance money Declare @Fee money Declare @CustomerNumber int Declare @TransAmount MONEY Declare @TransType Int Declare @CardNumber int
SELECT DISTINCT @TransAmount=100, @CardNumber=1234, @Balance=1000,/*Card.CustomerBalance,*/ @CustomerNumber=Card.CustomerNumber From dbo.Card Card Where Card.CardNumber=1234/*@cardNumber*/
Select @Fee=1.50/*SC.ATMWithdrawalFee*/ From dbo.ServiceCharge SC Where SC.CustomerNumber=@CustomerNumber
If @Balance > @TransAmount + @Fee /*@transamount*/ /* MAKE SURE THAT THEY HAVE*/ /* ENOUGH MONEY INCLUDING FEE*/ Update Card Set CustomerBalance=(@Balance - @TransAmount - @Fee) Where Card.CardNumber=1234
Hi: I was working in SQL MS Enterprise Manager. I opened my DB and then tried to write a new Stored procedure(Stroed Procedure-->right click-->new procedure--> blank SP window). I copied some .sql code from VB. It had a beggining line like:
" If exists in Sytemobjects(newprocedure) drop procedure newprocedure
Create proc newprocedure as " ----
Now when I am copying this code in Enter Manager, the If part just vanishes and the code starts(opening from Enter Manager) from
Create proc newprocedure.
What is the catch here. I tried to see all other SPs in my DB and none of them has the If part.
What is the wrong. Why it does not take the If part.
I know how to use a reader to read in my values but how do u use other methods when dealing with a stored procedure which deals in both single tables and multiple tables......
I've a Stored Procedure that accepts 3 parameters. User can supply values for any 1 or more parameters (depends on the user choice, if he needs to search on one column then he needs to supply only one parameter, if he needs to search on two columns then he needs to supply two parameters, and so on ...
The query inside the Stored Procedure is written dynamically, I mean depending on the parameters provided by the user, a dynamic query is stored in a VARCHAR variable (suppose @ssql). In the last the dynamically written query is executed with following simple statement :
Exec (@ssql)
PROBLEM:
Now the problem is when my Programming team calls this Stroed Procedure from an ASP page, it never returns any Rows in the Recordset Object, though it runs perfect from the Query Analyzer with the same Execution Statement and Paramether Values. It seems that the ASP page is unable to recognize the Field (Column Names) reffered in the Query written dynamically inside the Stored Procedure. But if we write the Static Query in the Stroed Procedure, it always works fine.
Can someone identfy what problem is this about, or may be someone has faced this problem in past also. I would really appreciate if someone can help me in this ...
I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
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?
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!!
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.
Recently i had to write a script in sql to compare multiple tables to get a result of items that do not conform to certain business logic. In doing so i wrote all of this information into a sql parameter which branches out to a few other parameters within the parameter. Now if you need the code just let me ask, but this is a general question to see if it has occured for anyone else. The problem i am recieving is when i access the code from a .net windows application it tells me: Error Message:Insert Error: Column name or number of supplied values does not match table definition.Insert Error: Column name or number of supplied values does not match table definition. Procedure Errored On: val_GetDuplicateItemsFromAssignmentLine Number: 16 However when i run the sql parameter within SQL it accesses it just find. This is using the same parameter values. Does anyone know why this could be happening?
Does any body have any VB (inside of Access) examples of creating SQLproduces inside a access form via vb and then executing those procedures andreturning the results to the screen?Thank you in advance,Mike
Hey all, I am a newbie to the nth degree. I am O(newbie) ;)
Anyways, I have a MS SQL Server with a database on it called MyDatabase. I am creating a web application that accesses this database. My question is the Database has a bunch of tables, stored procedures in it and such. I want to add a new one. What is the process that goes on here. There is a file called SPS.sql and when I open it up, it is a bunch of create procedure functions. The names of the procedures match the names of the procedure objects in the object browser in my sql query analyzer.
I guess, if I want to create a new procedure, do i just open up this file, edit it and save it? I don't think it is exactly working because my changes are happening when I run my web application. (for example i clearly change my procedure called TEST in that SPS.sql file, then when I call it in my VB code it hasn't taken affect!.
Thanks for your help. I am starting to enjoy this "programming".
On a side note: My app is on a server(MS SERVER 2003). Sometomes when I am in the SQL query analyzer.the computer just locks out. The power is on but I cant do anything but turn off the power....
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
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.
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)
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
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?
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
This procedure is missing one importent piece is I need to have a customerID added to the invoice table. The CustomerID comes from another table called Customer_Invoice_cart. I don't know how to convert Customer_Invoice_Cart.CustomerID to a @CustomerID to add to the table. If there is anyone out there who is good at stored procedures (unlike me who is learning) could help me out. FYI I realy cannot get the CustomerID for the .aspx page, it is not called any where.
procedure [dbo].[InvoiceAdd]( @EmployeeID int, @CartID nvarchar(50), @OrderDate datetime, @OrderID int OUTPUT)AS BEGIN TRAN AddInvoice /* Create the Order header */ INSERT INTO invoice( employee_number, invoice_date)VALUES( @EmployeeID, @OrderDate) SELECT @OrderID = @@Identity /* Copy items from given shopping cart to OrdersDetail table for given OrderID*/ INSERT INTO invoice_books( invoiceID, ProductID, TaxID, Quanatity, UnitCost, UnitPrice ) SELECT @OrderID, Inventory_Invoice_Cart.InventoryID, Inventory_Invoice_Cart.TaxID, Inventory_Invoice_Cart.Quanity, products.UnitCost, products.UnitPrice FROM Inventory_Invoice_Cart INNER JOIN products ON Inventory_Invoice_Cart.InventoryID = products.ProductID WHERE Invoice_CartID = @CartID INSERT INTO invoice_books( invoiceID, ServiceID, Service_TaxID, Service_Quanatity, ServiceCost) SELECT @OrderID, Service_Invoice_Cart.ServiceID, Service_Invoice_Cart.TaxID, Service_Invoice_Cart.Quanity, services.price_leval1FROM Service_Invoice_Cart INNER JOIN services ON Service_Invoice_Cart.ServiceID = services.serviceID WHERE Invoice_CartID = @CartID /* Removal of items from user's shopping cart will happen on the business layer*/EXEC Customer_Invoice_Cart_Empty @CartIDEXEC Inventory_Invoice_Cart_Empty @CartIDEXEC Service_Invoice_Cart_Empty @CartID COMMIT TRAN AddInvoice
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
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
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.
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.
hi to all, i am using asp .net 2.0 and SQL Server 2005. i want to use STORED PROCEDURES in my web application. any links or code helps me a lot. thanks in advance.
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.
