I am trying to write a stored procedure to delete something from a table but I keep getting sytax error.
This is the code I am using:
DELETE [tablename].[tablerow], [tablename].[tablerow]
FROM [tablename]
WHERE (((tablename.tablerow)="void"));
This works as an Access query but not as a stored procedure.
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.
Hi All!
I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:
1. Production database comes into test database
2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
3. Remove user
4. Add user (this one brings login name since the restored one didn't)
5. Have a nice day
I've got everything except removing the stored procedure so I will really appreciate the help.
Thank you all!
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 RelatedHi,
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
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
I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:CREATE PROCEDURE usp_DeleteBox
/* *******************************************
Delete a record using the Passed ID.
********************************************** */
(
@pID as int = Null
)
AS
DELETE FROM [Boxes]
WHERE ID = @pID
When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally).
When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete.
Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!
Hi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @MemNRIC
END
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Can someone pls help? Thanks!
Is there a way to delete records from table passing parameter as tablename? I won't to delete all records from a table dependent on table selected. i'm trying to do this with stored procedure...Table to delete depends on the checkbox selected.
Current code(works)
Public Function DelAll()
MZKDB = MZKHRFin
If sloption = "L" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";Initial Catalog=" & MZKDB & ";Integrated Security=SSPI;"
ElseIf sloption = "S" Then
sqlConn.ConnectionString = "Server=" & MZKSrv & ";User id=sa;Password=" & MZKPswd & "; Initial Catalog=" & MZKDB & ";"
End If
sqlConn.Open()
sqlTrans = sqlConn.BeginTransaction()
sqlCmd.Connection = sqlConn
sqlCmd.Transaction = sqlTrans
Try
sqlCmd.CommandText = sqlStr
sqlCmd.ExecuteNonQuery()
sqlTrans.Commit()
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Prior records have been deleted from the database." & vbCrLf
SetCursor()
Catch e As Exception
Try
sqlTrans.Rollback()
Catch ex As SqlException
If Not sqlTrans.Connection Is Nothing Then
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " An exception of type " & ex.GetType().ToString() & " was encountered while attempting to roll back the transaction." & vbCrLf
SetCursor()
End If
End Try
frm2.txtResult.Text = frm2.txtResult.Text & " " & TableName & " Records were NOT deleted from the database." & vbCrLf
SetCursor()
Finally
sqlConn.Close()
End Try
ResetID()
End Function
If cbGenFY.Checked Then
sqlStr = "DELETE FROM FIN_FiscalYear"
TableName = "dbo.FIN_FiscalYear"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFY()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFund.Checked Then
sqlStr = "DELETE FROM FIN_Fund"
TableName = "dbo.FIN_Fund"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFund()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenFunc.Checked Then
sqlStr = "DELETE FROM FIN_Function"
TableName = "dbo.FIN_Function"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenFunc()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenObject.Checked Then
sqlStr = "DELETE FROM FIN_Object"
TableName = "dbo.FIN_Object"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenObject()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenCenter.Checked Then
sqlStr = "DELETE FROM FIN_Center"
TableName = "dbo.FIN_Center"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenCenter()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenProject.Checked Then
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "dbo.FIN_CodeBook"
DelAll()
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "dbo.FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "dbo.FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_Project"
TableName = "dbo.FIN_Project"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProject()
timeStepStop = Date.Now
TableName = "dbo.FIN_Project"
DispOneCounts()
End If
If cbGenProgram.Checked Then
sqlStr = "DELETE FROM FIN_Program"
TableName = "dbo.FIN_Program"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenProgram()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenGL.Checked Then
sqlStr = "DELETE FROM FIN_gl"
TableName = "FIN_gl"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenGL()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenRevenue.Checked Then
sqlStr = "DELETE FROM FIN_Revenue"
TableName = "FIN_Revenue"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenRevenue()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbGenBank.Checked Then
sqlStr = "DELETE FROM FIN_VendorBankAccnt"
TableName = "dbo.FIN_VendorBankAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_VendorBank"
TableName = "dbo.FIN_VendorBank"
DelAll()
sqlStr = "DELETE FROM FIN_bankAdd"
TableName = "dbo.FIN_bankAdd"
DelAll()
sqlStr = "DELETE FROM FIN_bankTERMS"
TableName = "dbo.FIN_bankTerms"
DelAll()
sqlStr = "DELETE FROM FIN_bank"
TableName = "dbo.FIN_bank"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenBank()
timeStepStop = Date.Now
TableName2 = "dbo.FIN_bankTERMS"
TableName3 = "dbo.FIN_BankAdd"
TableName4 = "dbo.FIN_VendorBank"
TableName5 = "dbo.FIN_VendorBankAccnt"
DispTwoCounts()
End If
If cbFinAP.Checked Then
sqlStr = "DELETE FROM FIN_Period"
TableName = "FIN_Period"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenPeriod()
timeStepStop = Date.Now
DispOneCounts()
End If
If cbFinVM.Checked Then
sqlStr = "DELETE FROM FIN_vendorClass"
TableName = "FIN_vendorClass"
DelAll()
sqlStr = "DELETE FROM FIN_vendorAdd"
TableName = "FIN_vendorAdd"
DelAll()
sqlStr = "DELETE FROM FIN_vendor"
TableName = "FIN_vendor"
DelAll()
sqlStr = "DELETE FROM FIN_AddressType"
TableName = "FIN_AddressType"
DelAll()
sqlStr = "DELETE FROM FIN_VendorStatus"
TableName = "FIN_VendorStatus"
DelAll()
sqlStr = "DELETE FROM States"
TableName = "States"
DelAll()
sqlStr = "DELETE FROM Country"
TableName = "Country"
sqlStr = "DELETE FROM FIN_IndustrialCodes"
TableName = "FIN_IndustrialCodes"
DelAll()
ClearCounts()
timeStepStart = Date.Now
GenIndCodes()
timeStepStop = Date.Now
DispOneCounts()
DelAll()
ClearCounts()
timeStepStart = Date.Now
FinVendStat()
timeStepStop = Date.Now
TableName = "FIN_VendorStatus"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinAddrType()
timeStepStop = Date.Now
TableName = "FIN_AddressType"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenCountry()
timeStepStop = Date.Now
TableName = "Country"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
GenState()
timeStepStop = Date.Now
TableName = "States"
DispOneCounts()
ClearCounts()
timeStepStart = Date.Now
FinVM()
timeStepStop = Date.Now
TableName = "FIN_Vendor"
TableName2 = "FIN_VendorAdd"
DispTwoCounts()
End If
If cbFinbudget.Checked Then
sqlStr = "DELETE FROM FIN_BudgetAccnt"
TableName = "FIN_BudgetAccnt"
DelAll()
sqlStr = "DELETE FROM FIN_Budget"
TableName = "FIN_Budget"
DelAll()
sqlStr = "DELETE FROM FIN_CodeBook"
TableName = "FIN_CodeBook"
DelAll()
ClearCounts()
TableName = "FIN_Budget"
timeStepStart = Date.Now
FinBudget()
timeStepStop = Date.Now
DispOneCounts()
ClearCounts()
TableName = "FIN_Codebook"
TableName2 = "FIN_budgetAccnt"
timeStepStart = Date.Now
FinCodeBook()
timeStepStop = Date.Now
DispTwoCounts()
ClearCounts()
End If
I can not get this stored procedure to delete my records...
I have a
contact table
RecordID
FirstName
LastName
etc
and a Address table
RecordID
Street
Zip
Town
Country
And a Relation table
RecordID
ContactID
AddressID
CreateDate
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[bc_Contact_Delete]
@ContactID int
AS
--SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete all Adresses
DELETE FROM [Address]
WHERE
RecordId in (SELECT ca.AdressId from [ContactAddress] ca
where
ca.ContactID = @ContactID)
-- Delete all Relations
DELETE FROM [ContactAdress]
WHERE ContactID = @ContactID
--- Delete Kontakt
DELETE FROM [Contact] WHERE (([RecordId] = @ContactID))
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
ROLLBACK TRANSACTION
-- Raise an error with the
-- details of the exception
DECLARE @ErrMsg nvarchar(4000),
@ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
RETURN
My Errormessage is
The DELETE statement conflicted with the REFERENCE constraint "FK_bc_ContactAdress_bc_Address". The conflict occurred in database "bContacts", table "dbo.ContactAddress", column 'AdressID'.
Can someone please post me an advice?
Hi
I have a search page having four fields. Giving any one of the field as input should retrieve search results in Gridview.
In GridView1 i have child Gridview for displaying details related to Gridview 1.
now i have to write storedprocedure for getting values in two grids.
there is one matching column in two tables i.e CNo.from first select statement we have to capture CNo and basing on that retrieve second table values.
I have a stored procedure for that but my problem is
i stored CNo in temp table i.e @MyTable .after doing select statements from two tables
i want to delete @MyTable. But iam not able to. so pls help me
My stored procedure code is here:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
--Now do your two selects
SELECT *
FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select *
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo
END
The output of storedprocedure is like this:
Iam getting all the columns of two tables but the CNo column is repeating twice in both the tables.
so please some one help me.
The CNo colum shouldnot repeat.
Thankyou
Hi,
I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID].
I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03.
I have come up with something like that but it does not seems to be correct.
CREATE PROCEDURE [sp_delete_test01_1] (@id [int])
AS
DELETE [test01] DELETE [test02] DELETE [test03]
WHERE ( [id] = @id)GO
Your advise please. Many Thanks.
I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).
Thanks!
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
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 3 Replies View RelatedGreetings:
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!
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
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!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
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
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
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
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?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
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
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
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?
View 1 Replies View RelatedIs there a website or somewhere i can go to read up on stored procedures??
View 2 Replies View RelatedcREATE 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