T-SQL (SS2K8) :: One Stored Procedure To Do Add / Update / Select And Delete
Apr 29, 2015
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
View 8 Replies
ADVERTISEMENT
May 7, 2007
Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
end
Is this a good approach design, or its bad???
Thanks all
View 5 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Nov 26, 2013
How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.
CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
[code]...
View 5 Replies
View Related
Feb 5, 2008
spaces problem on multi update delete stored procedure
on the the insert i get it like this
58830803, 55329429, 308962604, 55696314, 309128726
as you see only the first empid=58830803 is without spaces
how to fix this
my code
Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 4 Replies
View Related
Dec 4, 2014
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
View 4 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
May 27, 2007
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
View 1 Replies
View Related
Oct 20, 2005
I am trying to update fields in my table based on certain critera.
UPDATE tblALMLoans
SET tblALMLoans.NextRepDate = dateadd(YY,5,tblALMLoans.OriginalDate)
do until tblALMLoans.NextRepDate > getdate()
tblALMLoans.NextRepDate = dateadd(YY,1,tblALMLoans.OriginalDate)
loop
FROM tblALMLoans
WHERE (tblALMLoans.RateFlag = 'A');
I know this is no where near what its supposed to look like, but this is in code what I am looking to do.
Any help would be greatly appriciated.
View 8 Replies
View Related
May 6, 2015
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As BEGIN BEGIN TRAN T1 SET NOCOUNT ON SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate(); UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id; SELECT * FROM #TempTable; DROP Table #TempTable; COMMIT TRAN T1 END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) { List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure; try { Command.CommandTimeout = CONNECTION_TIMEOUT;
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
View 3 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Feb 5, 2015
Imagine the following scenario: two tables (say, "requests" and "details") are joined in a 1:n relationship on MSSQL 2008. Both tables contain an ID (autoincrement field) and a timestamp field for proper concurrency management. Data access in the frontend is provided by a typed dataset in VS 2010. There are SPs on the server which select, update, insert or delete data in each of the two tables (so, 8 SPs alltogether: uspRequestsSelect, uspDetailsInsert etc.). These SPs are used for data access in the dataset. The GUI is a Windows form with 2 datagridviews, one for request datatable and one for the child-relation-based datatable FK_request_details. So, each request shows its details. The form works well so far.
Now, trouble strikes. A business rule says: "the first details row of a request (=row with lowest ID) always has a 0 in column "additional fee". For additional detail rows, this field has to be set to constant value 45". In short: the first detail row of each request is free, second and later details are charged 45 €.
So, I created a SP "uspRequestFeeManager", which recalculates all (!) detail rows of a request. This SP is called in uspDetailsInsert, uspDetailsUpdate and uspDetailsDelete, as each of this cases causes the additional fee to be recalculated for all rows (as rowcount can change). In Management Studio, this works as well!
But: as the uspRequestFeeManager changes data even for rows the user did NOT touch, there's a concurrency exception in my frontend in the following case:
In a request with 2 detail rows (first row has fee = 0, second = 45), the user deletes the row with fee = 0. Committing via TableAdapterManager calls uspDetailsDelete, which calls uspRequestFeeManager, which sets the remaining single details row to a fee value of 0 (which is correct!). This causes TableAdapterManager.UpdateAll to fail ("concurrency exception; delete command has handled 0 of 1 expected records"), as uspRequestFeeManager has "edited" a row which the user didn't touch, and thus updated its timestamp as well. So, the list is out of sync.
The uspRequestFeeManager looked like a good idea... but it seems not to be.
Approach would be: instead of calling the uspRequestFeeManager from within the SP, call it programmatically after TableAfterManager.UpdateAll, and after that, 're-fill' the details datatable with the updated data. But that would transfer business logic from server to client. I don't like that...
View 3 Replies
View Related
Apr 7, 2006
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!
View 3 Replies
View Related
Jan 18, 2008
hi
i have form view that retrieves a single row
i dont want to use SQLDataSource default Select/Update/Insert/Delete buttons
i am using Stored Procedures
I want to have my own buttons, the select has parameters, how to retrieve data for that parameter?
View 6 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
May 16, 2008
Greetings. I have been trying to develop an SSIS package that updates external data (Visual FoxPro tables) from SQL Server 2005. I have tried this various ways: using various Data Flow task components that flow to an OLEB Destination; using an Execute T-SQL Task; and even trying Management Studio interactively with the OpenDataSource('vfpoledb', etc.) statement. For each of these techniques, I have no problem performing a SELECT from the VFP data. Also, I have no problems performing an INSERT of new records using any of these techniques. However, both UPDATE and DELETE of existing records fail.
Is it possible the the OLE DB driver doesn't support UPDATE and DELETE operations? It appears that I'm not allowed to change or delete existing records, only add new ones. Or, are there other techniques I can be trying?
I am aware that updating FoxPro data can be performed by pulling the data from SQL Server into FoxPro. For our purposes, it would be more convenient if the processes could be initiated and managed from the SQL Server side of things instead.
Thanks much,
Randy Witt
View 2 Replies
View Related
May 18, 2006
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
View 4 Replies
View Related
Feb 24, 2006
I grouped everything together so you see it all. I'm not getting any errors but nothing is happening. I had this working and then I converted to Stored Procedures and now it's not.
CREATE PROCEDURE UpdateCartItem(@itemQuantity int,@cartItemID varchar)ASUPDATE CartItems Set pounds=@itemQuantityWHERE cartItemID=@cartItemIDGO
<asp:Button CssClass="scEdit" ID="btnEdit" Runat="server" Text="Update" CommandName="Update"></asp:Button>
Sub dlstShoppingCart_UpdateCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdUpdateCartItem As SqlCommand Dim UpdateCartItem Dim strCartItemID As String Dim txtQuantity As TextBox strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) txtQuantity = e.Item.FindControl("txtQuantity") connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdUpdateCartItem = New SqlCommand(UpdateCartItem, connStr) cmdUpdateCartItem.CommandType = CommandType.StoredProcedure cmdUpdateCartItem.Parameters.Add("@cartItemID", strCartItemID) cmdUpdateCartItem.Parameters.Add("@itemQuantity", txtQuantity.Text) connStr.Open() cmdUpdateCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
____________________________________________________________
CREATE PROCEDURE DeleteCartItem(@orderID Float(8),@itemID nVarChar(50))ASDELETEFROM CartItemsWHERE orderID = @orderID AND itemID = @itemIDGO
<asp:Button CssClass="scEdit" ID="btnRemove" Runat="server" Text="Remove" CommandName="Delete"></asp:Button>
Sub dlstShoppingCart_DeleteCommand(ByVal s As Object, ByVal e As DataListCommandEventArgs) Dim connStr As SqlConnection Dim cmdDeleteCartItem As SqlCommand Dim DeleteCartItem Dim strCartItemID strCartItemID = dlstShoppingCart.DataKeys(e.Item.ItemIndex) connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString")) cmdDeleteCartItem = New SqlCommand(DeleteCartItem, connStr) cmdDeleteCartItem.CommandType = CommandType.StoredProcedure cmdDeleteCartItem.Parameters.Add("@cartItemID", strCartItemID) connStr.Open() cmdDeleteCartItem.ExecuteNonQuery() connStr.Close() dlstShoppingCart.EditItemIndex = -1 BindDataList() End Sub
View 2 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
May 27, 2008
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
View 2 Replies
View Related
Apr 8, 2014
I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring up a Stored Procedure and make a change.... I execute the Stored Procedure... after it finishes.... I exit out without saving to a file.... I go back in and my change was not held.
I can do the exact same process with an old SQL Server 2005 database. Is there a permission I am missing to set to be able to do this on the 2008 database.
View 9 Replies
View Related
Oct 16, 2014
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT
AS
BEGIN
SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b
WHERE a.borrower_id=@Borrower_id
AND a.ISBN = b.ISBN
END
GO
EXEC SP_Task1 10001
View 9 Replies
View Related
Jul 25, 2001
Hi,
I need to create a stored procedure which would
delete just the photo of a student once he graduates.
I found out that a delete SQL statement will delete a whole row.
Is there a way to just delete the Photo and leave the rest of the data
untouched.
Thanks.
Regds,
Ran
View 1 Replies
View Related
Jul 14, 2004
I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table
Please help!
************************************************** ***
My contrat DDL is follows
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
************************************************** ***
My insert stored procedure is follows
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char],
@contactId[int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @contactId = contactId
and @studentId = studentId)
begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
go
View 1 Replies
View Related
May 31, 2006
I want to delete some rows in a table using a Stored procedure. I am using MS sql 2000 using Enterprise manager. I want to delete all rows that have col. status listed as shipped.
/Create Procedure [DeleteOld] AS
/
/Delete Equipment
/FROM Yahoo
/WHERE Status <> 'shipped'
I have two rows of data, one row has a col. names status, and in that row of data says "shipped". The other row says "hold" in the place of "shipped".
The procedure is written, and is setting under that database ready to be run. I do not get any error's when I excute the procedure from SQL query, but it just does nothing when run!
I need help to make this procedure work properly
Thank You,
Ernie
P.S. I am new to this forum!
View 5 Replies
View Related
Sep 24, 2007
i have accidentally deleted a stored procedure is there anyway of getting this back, just found out there is no back up
View 7 Replies
View Related
Mar 9, 2008
Hi, I'm having a hell of a time trying to get a delete query to work properly. I have an existing query that finds records in the database that have expired and now I want to delete those records. I tried wrapping a delete around it, but it deletes everything.
How can I delete the results of this procedure? Thanks!
SELECT rssName, [Days Old] FROM
(SELECT rssName, DATEDIFF(D, timestamp, getdate()) AS [Days Old]
FROM tblRSSFeeds) AS feeds
WHERE [Days Old] > @expireTime
View 4 Replies
View Related
Feb 7, 2008
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert] @CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRYINSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURNEND TRY
BEGIN CATCH
--Execute LogError_Insert SP EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN -1
END CATCH
END
View 2 Replies
View Related
Feb 7, 2008
I have looked at the membership and roles stored procs from Microsoft and noticed that most of them are wrapped into a transaction. Ok some of the stored procs updated more than one table in which case it makes sense to wrap the code into a transaction. Our stored procs are a little simpler and insert, update or delete only one table for the most part. My question is: What is good practice, should I wrap my stored procs in transactions or because I am only updating one table leave it the way it is, see sample below: Please advise, newbie
ALTER PROCEDURE [dbo].[syl_Category_Insert]
@CategoryName nvarchar(64), @LanguageID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [syl_Categories]
VALUES(
@CategoryName,
@LanguageID)
SELECT SCOPE_IDENTITY() AS [CategoryID]
RETURN
END TRY
BEGIN CATCH
--Execute LogError_Insert SP
EXECUTE [dbo].[syl_LogError_Insert];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
View 3 Replies
View Related
Apr 9, 2014
OK, to simplify some annual auditing of DB users (not the SQL logins,) I'm trying to craft a stored procedure that the customer on the server (they're the only customer on this particular server) can run to get a listing of all DB users and what roles they have.
I've got a query that returns this for the currently selected DB, so that part's done.I can use SP_MSFOREACHDB to run it against each DB, with the results going into a temp table to make it easier to copy/paste into an Excel file.What I want to do, and can't seem to see how, is wrap the whole thing in yet another SP of my own, with an EXECUTE AS so that the customer doesn't need sysadmin or any special privileges on the server. When I do this, it runs, but only against master.
Now, from digging it looks like you can't have an "insert #temptable exec sp_whatever" inside another SP. I'd like to avoid dynamic SQL, and while I know there are problems with MSFOREACHDB, it'll work for what we need.how to turn a user created SP, into a system SP so it can be run regardless of the DB you've selected, so at least there's that.
View 7 Replies
View Related
Apr 16, 2014
In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are needed for the main sql. My problem is I do not know how to have the t-sql call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.
The basic dataset is the following:
SELECT SchoolNumber,
SchoolName,
StudentNumber,
from [Trans].[dbo].[Student]
order by SchoolNumber,
SchoolName,
StudentNumber
I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.
The columns that I need from the stored procedure called StudentData will return the following data columns
that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.
Thus can you show me how to setup the sql to meet this requirement I have?
View 2 Replies
View Related
May 16, 2014
I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc:
CREATE PROCEDURE [dbo].[BackupDB]
@dbname varchar(50)
AS
BEGIN
[code]...
View 1 Replies
View Related
Jun 20, 2014
I'm not new to SQL at all, but I'm completely new to backup/restore TSQL.I have the following script, which backs up 2 databases and restores them under different database names.the script runs fine as-is, but I cannot seem to package it within a create stored procedure statement.if I attempt to wrap create procedure <name> AS begin....end around it, the statement simply executes.
--backup LGTY_QA_01, restore to LGTY_DV_01
BACKUP DATABASE LGTY_QA_01
TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupLGTY_QA_01.bak'
WITH FORMAT, CHECKSUM
GO
ALTER DATABASE LGTY_DV_01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
[code]...
View 1 Replies
View Related
