Transact SQL :: DML Within Stored Procedure

May 7, 2015

How we can get to know what DML action(INSERT,UPDATE,DELETE) is present inside a Stored procedure, and i want to know if we have any specific columns are present in system tables or system related dynamic views which gives this info. I am aware of the SQL profiler, but in my scenario i want to use it through system tables or system related dynamic views.

View 7 Replies


ADVERTISEMENT

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Transact SQL :: DMV For Stored Procedure

Jun 26, 2015

Why i cannot see my stored procedure in the result of 

select * from sys.dm_exec_procedure_stats

SQL Server DBA

View 12 Replies View Related

Transact SQL :: Stored Procedure To Return Results

May 27, 2015

How can I return results from this SP?

Alter
Procedure  sp_Blocking
as
      SET NOCOUNT
ON
truncate
table blocked

[Code] ....

View 4 Replies View Related

Transact SQL :: Stored Procedure Executed At The Server?

Nov 5, 2015

I could identify the last or all stored procedure, which was performed on a database, +/- something similar to what the profile of sql server identifies as below ?

View 2 Replies View Related

Transact SQL :: Call Cobol From Stored Procedure?

Jun 17, 2015

In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.

View 7 Replies View Related

Transact SQL :: How To Create Stored Procedure Using Agent Job

May 8, 2015

I have 3rd party application implementation I am doing, if I have stored procedure in one database, can it be copied to other database (newly created) on the same server? I will have the name of old and new database when this will be copied. I do need to automate this copy.

View 10 Replies View Related

Transact SQL :: Stored Procedure Return Values

Sep 23, 2015

If I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer?  Is that simply the success flag?

View 5 Replies View Related

Transact SQL :: Delta Process In Stored Procedure

Apr 30, 2015

Currently through my stored procedure, I am pulling the data from my source systems in the way Daily Truncate and loading the data in to my database table. Currently my query looks as follows,

SELECT
     a.col1,a.col2,b.col3,b.col4,c.col5
FROM
a
left outer join b ON a.col1 = b.col1
left outer join c ON b.col1 = c.col1.

Now, I want to change the above one to delta process(I have updateddate field in all the tables). The requirement is any of these 3 tables has updateddate = getdate() then i need to take "a.col1,a.col2,b.col3,b.col4,c.col5" columns and push to destination
table.

I think usually delta process means we will use to consider the key table, in that table if there is an updated record will take that record and join with the other required tables to pull the updated record. But these logic seems different.how to build the query for the above delta process? Since it is an urgent requirement need to implement asap.

View 9 Replies View Related

Transact SQL :: Difference Between Batch And Stored Procedure?

Jun 19, 2015

What is the difference between Batch and Stored Procedure?

View 5 Replies View Related

Transact SQL :: How To Run Stored Procedure In Another Database On Different Server

Jul 13, 2015

I linked MS SQL and MySQL and i would like to have insert trigger on MySQL to import certain fields every time when there's new entry.

View 5 Replies View Related

Transact SQL :: Stored Procedure To Get Data From Table

Oct 26, 2015

I have a table which has data like shown in below image , I want to get count of case numbers which has responses 123 AND 125  ( case should have both responses), like this I have requirement for other responses, how can I do it in Stored procedure .

View 8 Replies View Related

Transact SQL :: Adding Values In Row Using Stored Procedure

Aug 18, 2015

I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.

-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests]
-- Add the parameters for the stored procedure here

[code]....

View 2 Replies View Related

Transact SQL :: How To Create A Table Using Stored Procedure

Sep 1, 2015

Need creating a stored procedure which creates a table.

View 3 Replies View Related

Transact SQL :: Add Headers Name In CSV File From Stored Procedure

May 11, 2015

I want to add some name of the column's header from stored procedure to the CSV file

bcp "EXEC [databasname].[storedprocedure]" queryout C:estbcp_outputTable.csv -c -T -SPC01 -r


I'm using the code above in CMD.

Unfortunately, I cannot change the code that is inside of the stored procedure because it is already used in productionphase.

View 5 Replies View Related

Transact SQL :: Trying To Pass Dates To Stored Procedure

Oct 13, 2015

We've researched this, and some people are stating convert to nvarchar first. I'd like to keep the date as a date.Here is how I am trying to call it:

Declare @FromDate as date
Declare @ToDate as date
Set @FromDate=Convert(date,'09-01-2015',110)
Set @ToDate= Convert(Date,'10-13-2015',110)
Select @FromDate

[code]...

How can I keep the date as a date and still pass it to stored procedure.

View 3 Replies View Related

Transact SQL Debugger Not Stopping In Stored Procedure

Sep 5, 2007

I am trying to use the Transact-SQL debugger to step through a stored procedure on a server to determine where it is failing. I right-click the stored procedure I want to debug in the Query Analyzer's Object Browser and choose Debug... In the Debug Procedure dialog I fill in the values for the parameters, choose the Auto roll back checkbox, and press Execute.

The T-SQL Debugger screen comes up with my stored procedure and a message on the bottom of Waiting for user input. But I can input nothing, neither click on the icons on top, set breakpoints, or doing anything else, as the stored procedure runs to the end and gives me the return code.

If I subsequently set some breakpoints in my stored procedure and try the Go button. Once again the stored procedure runs to the end without stopping at any breakpoint.

In both cases, the stored procedure shows the waiting icon as it proceeds to the end, so that I can not click on anything to stop it.

I have been told this is may be a permissions issue between myself as a client on my machine and the stored procedure on the server. If so, does anybody know what I the things that have to be done on the permissions side to be able to debug the stored procedure using the Transact SQL debugger.

View 1 Replies View Related

Transact SQL :: Prevent Stored Procedure From Getting Deleted

Sep 28, 2015

How could I prevent a stored procedure from getting deleted?

View 3 Replies View Related

Transact SQL :: Database Role For Creating Stored Procedure?

Oct 2, 2015

I want to create database role which can be used for creating stored procedures like db_SPCreate.

User should be able to create SP on Schema PVR; and should not be allowed to create sP in otherthan schema and he can use any schema tables to create procedure.

[URL]

View 4 Replies View Related

Transact SQL :: Calculate Minutes In Stored Procedure Using Datediff

May 9, 2015

I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user  only if he makes the 3 incorrect entries within 30 minutes.

I created a field named "FirstEntryTime" of type datetime  that saves the date of the first incorrect entry.  I tried to make an if statement:

if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30)
Begin 
 Update myTable set ...
 End

View 6 Replies View Related

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Transact SQL :: Call Stored Procedure In Select Query?

Nov 9, 2015

I have a SELECT query that also needs to call a sproc with a column name passed in as a parameter.

Unfortunately I cannot use a TVF, as the sproc code references a TVF on a linked server.

What is the best option?

SELECT
u.UserID,
u.Username,
u.Address,
EXEC dbo.[GetResult] u.UserID AS 'Result'
FROM dbo.UserTests u

View 3 Replies View Related

Transact SQL :: Stored Procedure To Update And Insert In Single SP

Jul 17, 2015

I have Table Staffsubjects with columns and Values
            
Guid  AcademyId  StaffId  ClassId  SegmentId  SubjectId   Status

 1      500       101        007     101       555           1
 2      500       101        007     101       201           0
 3      500       22         008     105       555           1

I need to do 3 scenarios in this table.

1.First i need to update the row if the status column is 0 to 1
2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId
3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and  SegmentId<>@SegmentId and  SubjectId<>@SubjectId

I have wrote the stored procedure to do this, But the problem is If do the update, It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate

Here is the stored Procedure what i have wrote

ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
@AcademyId uniqueidentifier,
@StaffId uniqueidentifier,
@ClassId uniqueidentifier,
@SegmentId uniqueidentifier,
@SubjectId uniqueidentifier

[Code] .....

View 10 Replies View Related

Transact SQL :: Adding Stored Procedure To Master Database?

Apr 24, 2015

I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.

I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?

View 4 Replies View Related

Transact SQL :: How To Call SSIS Package From Stored Procedure

May 21, 2015

I have ssis package which is credated by VS-2010.

I want execute this SSIS package from the stored procedure (SQL server 2005).

View 3 Replies View Related

Transact SQL :: Stored Procedure To Return Multiple Records

Oct 21, 2015

I am looking out for sample  stored procedures returning multiple records

Example: GetOrderDetailsByOrderId

The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.

View 4 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten.  Below is the stored procedure I've created:

ALTER PROCEDURE [dbo].[ap_CalcGrade] 
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,

[Code] ....

And below is the "test query" I'm using: 

--  *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
        @Numeric   int,        -- Numeric Grade
        @Result    int         -- Procedure Status (0 = OK) 
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3, 

[Code] ....

This is resulting in an output of: 

A+ 97
A+ 97
C- 72

but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
 
A+ 97
No Find
C- 72

I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it.  Below is the assignment requirements:

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:

1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

View 6 Replies View Related

Transact SQL :: How (NULL) Value Is Displaying After Encrypting A Stored Procedure

Oct 30, 2015

How 'NULL' value is displaying after encrypting a stored procedure? links to know the procedure behind this how the encrypted procedure is storing and updating with NULL value under the same column after the encryption.

View 7 Replies View Related

Transact SQL :: Using Number Of Days As Stored Procedure Parameter

Aug 7, 2015

We have a stored procedure which currently accepts the following parameter: exec dbo.PurgeOldData <'yyyy-mm-dd'...We want to schedule this sp to run once a (first day of the month) going back to say today's date (or the date when this sp is called to run) minus 365 or purging 12 months data. Basically, the function should convert the today's date minus xxx number of days to yyyy-mm-dd so that the sp accept it as the correct parameter and purges the data successfully. So, if the sp is run on 2015-08-06, it will purge data going back to 2014-08-06 or earlier with the following parameter:

exec dbo.PurgeOldData <'2014-08-06'>

which means, all the data prior to the above date will be purged. Even the sp was run on 2015-08-06, the converted value of 2014-08-06' is entered as the sp parameter.

View 3 Replies View Related

Transact SQL :: Write Parameter Stored Procedure In Server?

Aug 4, 2015

I am having table on which i want to fire a query on.

table structure is as follows :

Table1 

Table1ID    bigint PK
City  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
MiddleName  nvarchar(10)
State  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
LLDCode     nvarchar(20)
MMDCode    nvarchar(20)
LastModified    datetime
CreatedDateTime       datetime
LastUpdatedDateTime       datetime
SSN#  nvarchar(20)

I have to write a parameterised stored procedure where multiple values will be pass as input to SP.

E:g:  1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode

This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.

Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?

Table2 structure.

Table2ID PK
Table1ID FK
Name
Department
SSXML

View 9 Replies View Related

Transact SQL :: Invalid Column Not Detected In Stored Procedure

Aug 17, 2015

When I create/alter a stored procedure I am expecting to get an error because one of the columns was renamed.

However, the stored procedure compiles without error.  Is this correct behavior?

Example:  Create a Test table.  Create stored procedure that reference table along with temp table.  The columns are not verified.  The stored procedure still compiles, even with the join containing bad column names.  Why is an error
not thrown?

Example:
create table Test (col1 int)
GO

create procedure TestSP
as
create table #Temp (col1 int)
select 1
from Test a
join Temp b
on a.bad = b.bad
-- Bad column names
GO

View 2 Replies View Related

Transact SQL :: Select And Update Query In Same Stored Procedure

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







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