Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Wednesday, December 26, 2012

Granting execute permission on all stored procedure in SQL

In SQL many times we need to provide execute permission to all stored procedure for specific user for any database. To achieve this you can make the execute statement by below query.

The query for it is just simple:
SELECT 'Grant Execute on ' + name +  ' SQLServerUSERName'FROM sysobjects WHERE xtype IN ('P')

For tables and views:
SELECT 'Grant select,insert,update,delete on ' + name + ' SQLServerUSERName''
from sysobjects where xtype in ('
U','V')'
Enjoy.

Getting Month name from any date

Getting Month name from any date in SQL:

Just use the below query and you will get the result.

SELECT DATENAME(MONTH, DATEADD(MONTH, MONTH(GETDATE()), -1 ))


Example with result:








Done.

Monday, December 24, 2012

Getting Date from Date Time column

While using date time fields in WHERE clause you can face one common problem. Most of time you can't get the records for which you have used the datetime in where clause. For example:
SELECT * FROM tTable WHERE DateTimeColumn='12/24/2012'

In this I don't get the records which have date 12/24/2012, it is because in table the column contains the time also. Then how to GET these records. Its very simple just convert your date time column with below code:

CAST(FLOOR( CAST( SM.DateDownloaded AS FLOAT ) )AS DATETIME) AS DownloadedDate

Converting rows to columns


Using Pivot you can easily convert rows to columns.
USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) p
PIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )
) AS 
ORDER BY VendorID

Enjoy.

Finding foriegn key table names which primary key is using in other tables


Finding table names in which a primary key of any table is used as foriegn key.
SELECT
    ConstraintName = fk.name,
    TableName = t.name,
    ColumnName = c.nameFROM
    sys.foreign_keys fkINNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_IDINNER JOIN 
    sys.tables t ON fk.parent_object_id = t.OBJECT_IDINNER JOIN 
    sys.columns c ON fkc.parent_object_id = c.OBJECT_ID AND fkc.parent_column_id = c.column_idINNER JOIN 
    sys.tables tref ON fk.referenced_object_id = tref.OBJECT_IDINNER JOIN 
    sys.columns cref ON fkc.referenced_object_id = cref.OBJECT_ID AND fkc.referenced_column_id = cref.column_idWHERE
    tref.Name = 'tCurrencyCode'
    AND cref.Name = 'CurrencyId'

Enjoy.

Thursday, March 22, 2012

Searching Text in Procedures, Tables and Functions

In my project there is requirement to change the field length eg CompanyCode. Problem is with this field name there are many tables who not created the foriegn keys and this field is used in many procedures and functions. To find the text company code I found this simple code:

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
 -- tables
 SELECT
  TABLE_NAME
 FROM
  INFORMATION_SCHEMA.TABLES T
 WHERE
  charindex(@text, T.TABLE_NAME)>0
  
 -- columns
 SELECT 
  C.TABLE_NAME, C.COLUMN_NAME
 FROM
  INFORMATION_SCHEMA.COLUMNS C
 WHERE
  charindex(@text, C.COLUMN_NAME)>0

 -- views
 SELECT 
  V.TABLE_NAME AS VIEW_NAME
 FROM 
  information_schema.VIEWS V
 WHERE 
  charindex(@text, V.VIEW_DEFINITION)>0
  
 -- stored procs
 SELECT 
  R.ROUTINE_NAME 
 FROM 
  information_schema.routines r 
 WHERE 
  charindex(@text, r.ROUTINE_DEFINITION)>0
END
Hope this helps you!!!!!

Monday, March 19, 2012

Bulk Insert From XML To Table

If you want to insert large number of data in table with just one shot then you can do it by this article. While doing my project I came to scenario where I want to send the large number to data to SQL and then insert into tables. I found this very useful. What I did is I passed all records in form of XML in SQL Procedure.

Below is the example: Open Query analyzer and paste the below code

1) Declared the XML Variable first and set the value to it.
DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='<ROOT><student>
<id>1</id>
<name>Prashant</name>
<age>32</age>
</student>
<student>
<id>2</id>
<name>Swami</name>
<age>42</age>
</student>
<student>
<id>3</id>
<name>Ash</name>
<age>23</age>
</student>
<student>
<id>4</id>
<name>Kris</name>
<age>12</age>
</student>
<student>
<id>5</id>
<name>Derek</name>
<age>75</age>
</student>
</ROOT>'

2) Now Include this:

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc
OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
Insert into Students SELECT id, name, age from 
OPENXML (@idoc, '/ROOT/student',2) 
WITH (id  int, name varchar(50), age int)
Select * from @Students

Enjoy!!!!!!

Retrieving Last Inserted Identity of Record

While writing procedure in SQL somewhere I want the Identity value of inserted record but confused which way is the best to use and what's the difference between them. I found very useful article on this which I am sharing.

There are 3 ways to get the Identity value of last inserted record in SQL Server procedures:
@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT


SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.


Enjoy!!!!!!

Thanks to Pinal Dave where I found this useful topic.
Reference: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Finding Nth row in SQL Server

Finding nth row in SQL Server. Here is the simple query using ROW_NUMBER() function. In below example I am finding the 2 row:
SELECT * FROM
    (SELECT ROW_NUMBER()
        OVER (ORDER BY id) AS Row,
        id
    FROM Table) AS EMP
WHERE Row = 2

Enjoy!!!!!!

Deleting duplicate records in SQL Server

Deleting duplicate records in SQL Server:
WITH emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY id, firstname ORDER BY id ) AS RNUM FROMtable1 )
--select * from emp
DELETE FROM Emp WHERE RNUM > 1

Enjoy!!!!!!

Thursday, September 15, 2011

Using Nested Transactions In SQL Server 2005

Recently I have done Nested transaction in SQL Server 2005.
In this post, I will show you how to use Nested Transactions.

Below is the inner procedure which will be called from another procedure.
CREATE PROCEDURE pInnerProcdureName1
 AS
 BEGIN
   BEGIN try
     BEGIN TRANSACTION
        PRINT 'pProcdureName1 : ' + cast(@@TRANCOUNT as varchar(max))     
        DELETE FROM tablename
     
        COMMIT TRANSACTION
      PRINT 'pProcdureName1 Completed : ' + cast(@@TRANCOUNT as varchar(max)) 
     END try
     BEGIN catch
       if @@ERROR > 0
       begin
         PRINT error_message()
       end
       rollback transaction
       print 'Rollback Transaction :' + cast(@@TRANCOUNT as varchar(max)) 
     end catch    
   end 
GO

Now creating the outer transaction:
CREATE PROCEDURE pOuterProcedureName
    as
    begin
      begin try
        begin transaction
            print 'pOuterProcedureName : ' + cast(@@TRANCOUNT as varchar(max)) 
                
               exec pInnerProcdureName1
               commit transaction
           print 'pOuterProcedureName Transaction Committed : ' + cast(@@TRANCOUNT as varchar(max)) 
     end try
     begin catch
       if @@TRANCOUNT > 0
       begin
           rollback transaction
       end
      print 'Rollback pOuterProcedureName  : ' + cast(@@TRANCOUNT as varchar(max))
       if @@ERROR > 0
       begin
           SELECT error_message()
       end 
     end catch    
   end


Now just execute your Outer Transaction and Done.

EXEC pOuterProcedureName

Enjoy!!!!!!

Wednesday, September 14, 2011

Inserting Multiple Rows in One Insert Statement In SQL SERVER 2008

You can insert multiple records with one insert statement. Syntax is:

Insert Into TableName (field1,field2)
VALUES 
(value1,value2),
(value3,value4)