Stray ' And Stroed Procedures
Apr 18, 2008
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 '+'.�:
EXEC Database.dbo.StoredProcedure 'SQL Team' + char(39) + 's'
View 4 Replies
ADVERTISEMENT
May 23, 2008
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)
View 2 Replies
View Related
Dec 17, 2001
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
View 3 Replies
View Related
Sep 29, 2005
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.
View 2 Replies
View Related
Apr 1, 2004
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......
View 3 Replies
View Related
Jul 13, 2001
Hello All.
DEFINATION:
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 ...
Regards.
Aamir
View 3 Replies
View Related
Jul 23, 2005
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/
View 10 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
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
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Mar 1, 2006
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?
View 1 Replies
View Related
May 8, 2001
How can i call a Procedure that return a value (like a function) in a Select statment.
Ex: Select Field1, Procedure(Field2) From Table
View 2 Replies
View Related
Jun 17, 2005
my asp code
Code:
Sub Hit(thetype,id)
RsForSub1.Open "HitUp2 "&thetype&","&id, database,1,3
if RsForSub1.eof then
RsForSub0.Open "HitUp3 "&thetype&","&id, database,1,3
end if
RsForSub1.close
RsForSub1.Open "HitUp "&thetype&","&id, database,1,3
RsForSub1.close
End Sub
HitUp2
Code:
CREATE PROCEDURE [dbo].[HitUp2]
@Type nvarchar(30),
@ID int
AS
Select * from Tbl WHERE gid=@ID and t=@Type
GO
HitUp3
Code:
CREATE PROCEDURE [dbo].[HitUp3]
@Type nvarchar(30),
@ID int
AS
INSERT INTO Tbl (gid, t)
VALUES (@ID, @Type)
GO
HitUp
Code:
CREATE PROCEDURE [dbo].[HitUp]
@Type nvarchar(30),
@ID int
AS
UPDATE Tbl SET Click=Click+1
WHERE gid=@ID and t=@Type
GO
View 5 Replies
View Related
Sep 13, 2007
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
View 1 Replies
View Related
Aug 30, 2006
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....
View 2 Replies
View Related
Jun 17, 2006
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 1 Replies
View Related
Dec 15, 2006
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
View 1 Replies
View Related
Feb 24, 2007
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 4 Replies
View Related
Feb 25, 2007
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 2 Replies
View Related
May 2, 2007
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 5 Replies
View Related
Jun 19, 2007
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 1 Replies
View Related
Jul 5, 2007
Is there a website or somewhere i can go to read up on stored procedures??
View 2 Replies
View Related
Aug 2, 2007
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 2 Replies
View Related
Sep 18, 2007
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
View 15 Replies
View Related
Nov 18, 2007
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 5 Replies
View Related
Dec 22, 2007
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 2 Replies
View Related
Jan 8, 2008
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 7 Replies
View Related
Jan 10, 2008
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 3 Replies
View Related
May 30, 2008
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.
View 4 Replies
View Related
Jan 27, 2004
hi everyone :)
I've got a store procedure(sp) on the database side ( MS SQL 2000 )
and want to access the sp via ASP.net
so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.
I've tested the SP and it does work fine
can you just give me a template of how to access the sp from ASP.net
mant thanks indeed
M
View 2 Replies
View Related
Mar 1, 2004
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 6 Replies
View Related
Mar 18, 2004
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 6 Replies
View Related
Mar 18, 2004
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 1 Replies
View Related
