Techno Freak Search

Search Results

Tuesday, April 1, 2014

Use of dynamic SQL statement EXEC and sp_exectesql

Use of dynamic SQL statement EXEC and sp_exectesql EXEC : Using exec command you can create Dyanmic SQL statement but you need to pass extra quotes in parameter value. Check below example :
DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = '''Surat''' -- Extra Quotes added in Parameter
SET @DynamicQuery = 'SELECT ' + @columnList + ' FROM Users WHERE City = ' + @city
EXEC (@DynamicQuery )
sp_exectesql : Using this command you can also build the Dynamic SQL query. advantage of using this approach is you didnot bother about extra quote
DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = 'Surat' 
SET @DynamicQuery = 'SELECT '  + @columnList +  '  FROM Users WHERE City= @city '
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city
Reference URL : http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Saturday, March 29, 2014

Find Current Page URL and Check Page Exist or Not + JavaScript

Use : You can find out the current page url through javascript code and also you can check that the page is exist or not. If you found the page then you can take the action.
var currentPageUrlIs = "";
// Find out current Page URL
if (typeof this.href != "undefined") 
{
        currentPageUrlIs = this.href.toString().toLowerCase();
} 
else 
{
      currentPageUrlIs = document.location.toString().toLowerCase();
}
    
//Check Same Page Exist or Not
 if (currentPageUrlIs.indexOf("testpage") != -1) {
         //Action Time
 }
 else {
        // Do Something
 }

Wednesday, March 5, 2014

SQL Server Transactions and Error Handling Code and Test Case of Error Handling

-- Example Of SQL Server Transactions and Error Handling
-- and How to Test ErrorHandling Code

-- Create Test Table 
CREATE TABLE TestPritesh(
    ID INT PRIMARY KEY NOT NULL,
    decision NVARCHAR(5),
    CHECK (decision in ('yes','no')) -- Only allowed Yes and No value
);

BEGIN TRAN

DECLARE @ErrorCode INT

INSERT INTO [Test1].[dbo].[TestPritesh]
           ([id]
           ,[decision])
     VALUES
           (4
           ,'yes')


SELECT @ErrorCode = @@ERROR
IF (@ErrorCode <> 0) GOTO PROBLEM

--Check Constraint Error
INSERT INTO [Test1].[dbo].[TestPritesh]
           ([id]
           ,[decision])
     VALUES
           (5
           ,'nono')


SELECT @ErrorCode = @@ERROR
IF (@ErrorCode <> 0) GOTO PROBLEM
    
COMMIT TRAN

PROBLEM:
IF (@ErrorCode <> 0) BEGIN
                PRINT 'Unexpected error occurred!'
                PRINT @ErrorCode
    ROLLBACK TRAN
END

SELECT * FROM [Test1].[dbo].[TestPritesh]


Thursday, January 30, 2014

Remove Leading Zero from Column + SQL Server


SELECT SUBSTRING(TempColumn, PATINDEX('%[^0 ]%', TempColumn + ' '), LEN(TempColumn)) FROM TempTable

More Details : http://blog.sqlauthority.com/2012/11/19/sql-server-removing-leading-zeros-from-column-in-table/

Monday, January 6, 2014

Script to backup all the SQL Server databases

More Detail Please visit this URL ...

-- Script take the Database backup 

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'E:\TEST\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR

SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('TEST')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor

DEALLOCATE db_cursor

Friday, December 20, 2013

Use LEN Function When Column Contains NULL value + SQL Server

If you are using LEN function in your SQL Stored Procedure ,Key Point which you need to remember.

- IF your column Contains NULL Value
 LEN(ColName)  - your output is NULL  P.S : you can't compare NULL Values with LEN string

- IF your colum contains String.Empty value
LEN(ColName) your output is 0

Followers