Procedures

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


ADVERTISEMENT

Question About Procedures To Create Procedures In A Different Database

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

All My Stored Procedures Are Getting Created As System Procedures!

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

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

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

Stored Procedures 2005 Vs Stored Procedures 2000

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

Concerning .net And SQL Procedures

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

Can 3 Sql Procedures Be In One ?

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

SQL Procedures

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

SQL Procedures In ASP.NET

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

Stored Procedures

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

Stored Procedures

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

Help With Stored Procedures

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

Stored Procedures

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

Stored Procedures

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

Help On Stored Procedures

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

Stored Procedures

Jul 5, 2007

Is there a website or somewhere i can go to read up on stored procedures??

View 2 Replies View Related

Stored Procedures

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

Lost In Procedures

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

Can't See Stored Procedures

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

Using Stored Procedures In Asp.net

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

Stored Procedures

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

Stored Procedures

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

Asp .net - Using Stored Procedures.

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

Store Procedures

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

Stored Procedures

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

Stored Procedures

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

Using Stored Procedures

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

Stored Procedures And VSS

Jan 8, 2005

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 2 Replies View Related

I Need Help With Stored Procedures.

Jul 11, 2005

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 2 Replies View Related

Stored Procedures

Jul 22, 2005

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 7 Replies View Related

Stored Procedures MS SQL

Aug 31, 2005

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 2 Replies View Related

Stored Procedures

Oct 26, 2005

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 1 Replies View Related

Stored Procedures

Jan 30, 2006

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 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved