Tech NovoGeek

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_GetAlphabets]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^A-Z]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END


--SELECT dbo.udf_GetAlphabets('ABC1234DEFGH789')

Instead if you want to retrieve only numeric values from string replace the pattern '%[^A-Z]%' with '%[^0-9]%'

SQL Server different ways of running a dynamic SQL Query statement:
1.Query with parameters
2.EXEC
3.Sp_Executesql

1. Query with parameters
It is as simple as the following example shows.

DECLARE @emp varchar(75)
SET @emp = 'Anu'
SELECT * FROM employee WHERE EmpName = @emp

2. EXEC

SQL statement is built on the fly

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @ emp varchar(75)
SET @columnList = 'EmpId, Salary, EmpName'
SET @emp = '''Anu'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM employee WHERE EmpName = ' + @emp
EXEC (@sqlCommand)

3. sp_executesql

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @emp varchar(75)
SET @columnList = 'EmpId, Salary, EmpName'
SET @emp = '''Anu'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM employee WHERE EmpName = ' + @emp
EXECUTE sp_executesql @sqlCommand, N'@emp nvarchar(75)', @emp = @emp

So here are three different ways of writing dynamic queries

Declare @sql nvarchar(max)
DEclare @tab nvarchar(50)
set @tab= 'Employeetable'
-- To get column list of table in database
select 'select * from '+@tab+' where ' +  name + '=True' as query1 into Temptable 
from sys.columns where object_id in(select object_id  from sys.tables where name=''+@tab+'') 

declare @colcount int
declare @counter int
DEclare @cmd nvarchar(max)
set @counter=0
select @colcount =COUNT(*) from Temptable
WHILE @counter < @colcount
    BEGIN
      SET @counter = @counter + 1
      SET @cmd =';WITH MyCte AS ( select   query1, RowNum = row_number() OVER ( order by query1 )  
from Temptable ) SELECT query1 FROM MyCte WHERE RowNum = '+CONVERT(nvarchar,@counter)
     EXECUTE sp_executesql @cmd
    END
      
-- The above example is example for executing Dynamic Query Resultset
-- ;WITH MyCte AS ( select   query1, RowNum = row_number() OVER ( order by query1 )  from Temptable ) 
--SELECT query1 FROM MyCte WHERE RowNum = 2
   
DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'EmpId, Salary, EmpName'
SET @city = 'Anu'
SET @sqlCommand = 'SELECT ' + @columnList + ',ROW_NUMBER() OVER( order by EmpId) as rownum 
FROM Employee WHERE EmpName = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city
set @sqlCommand = 'SELECT  *,ROW_NUMBER() OVER( order by query1) as rownum  from Temptable'
EXECUTE sp_executesql @sqlCommand

Here is an example to get hierarchy of categories example

DECLARE	@CATEGORIES	TABLE	
(
CATEGORYID INT,
CATEGORYNAME VARCHAR(100),
PARENTCATEGORYID INT
)
INSERT @CATEGORIES VALUES (1,'VEHICLE'	,NULL)
INSERT @CATEGORIES VALUES (2,'CAR'	,1)
INSERT @CATEGORIES VALUES (3,'BMW'	,2)
INSERT @CATEGORIES VALUES (4,'MERCEDES BENZ',2)
INSERT @CATEGORIES VALUES (5,'BIKES'	,1)
INSERT @CATEGORIES VALUES (6,'YAMAHA'	,5)
SELECT * 
FROM @CATEGORIES
/*
CATEGORYID CATEGORYNAME PARENTCATEGORYID
---------- ------------ -----------------
1          VEHICLE       NULL
2          CAR           1
3          BMW           2
4          MERCEDES BENZ 2
5          BIKES         1
6          YAMAHA        5
*/

;WITH CTE AS(
              SELECT CATEGORYID, 
                     CATEGORYNAME,
                     PARENTCATEGORYID, 
                     CONVERT(VARCHAR(MAX),CATEGORYNAME) TREE
               FROM  @CATEGORIES
               WHERE PARENTCATEGORYID IS NULL
               UNION ALL
               SELECT C.CATEGORYID, 
                      C.CATEGORYNAME, 
                      C.PARENTCATEGORYID, 
                      P.TREE + ' / ' + CONVERT(VARCHAR(MAX),C.CATEGORYNAME)
               FROM @CATEGORIES C
               JOIN CTE P
               ON C.PARENTCATEGORYID = P.CATEGORYID
               )
               SELECT * 
               FROM   CTE 
/*
CATEGORYID CATEGORYNAME PARENTCATEGORYID TREE
---------- ------------ ---------------- ----
1          VEHICLE       NULL            VEHICLE
2          CAR           1               VEHICLE / CAR
5          BIKES         1               VEHICLE / BIKES
6          YAMAHA        5               VEHICLE / BIKES / YAMAHA
3          BMW           2               VEHICLE / CAR / BMW
4          MERCEDES BENZ 2               VEHICLE / CAR / MERCEDES BENZ
*/

One aspect of SELECT statement that seems to confuse many people is the GROUP BY clause. It is very important to group your rows in the proper place. Always push GROUP BY aggregations as far into your nested SELECT statements as possible – if you have a situation in which you are grouping by long lists of columns that are not part of primary keys, you are probably have not structured your query correctly. Here's a classic example that returns total sales per customer, in addition to returning the customer's name and address:

SELECT
  C.CustomerID, C.CustomerName,  
  C.CustomerType, C.Address1, C.City,
  C.State, SUM(S.Sales) as TotalSales
FROM
  Customers C
INNER JOIN Sales S
  ON C.CustomerID = S.CustomerID
GROUP BY
  C.CustomerID, C.CustomerName,
  C.CustomerType, C.Address1, C.City, C.State

If you observe in detail, SELECT's written this way is simply wrong. You should only be grouping on CustomerID, and not on all those other columns. Push the grouping down a level, into a derived table:

SELECT
  C.CustomerID, C.CustomerName,
  C.CustomerType, C.Address1, C.City,
  C.State, S.TotalSales
FROM
  Customers C
INNER JOIN
  (SELECT 
     CustomerID, SUM(Sales) as TotalSales
   FROM
     Sales
   GROUP BY 
     CustomerID) S
ON
  C.CustomerID = S.CustomerID

Now this is a much cleaner SELECT statement now, it is more efficient and it logically makes more sense when you look at it.

One more common mistake is that people just mimic the expressions in their SELECT list in the GROUP BY clause, without thinking logically about what grouping is really necessary. For example:

SELECT LastName + ', ' + FirstName, ... etc ...
FROM Names
GROUP BY LastName + ', ' + FirstName

Again, that is logically wrong and also less efficient. You should not be grouping by the expression itself; you should be grouping by what is needed to evaluate that expression. The correct grouping is:

GROUP BY LastName, FirstName

Too many people just keep stuffing column names and expressions into the GROUP BY clause until the errors go away without stepping back and thinking logically about how grouping works. Take a minute to really consider what you need to return and how it should be grouped, and try using derived tables more frequently when writing aggregate queries to help keep them structured and efficient.
Courtesy: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

For using IN statement in SQL Server and search multiple values from string you can use

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       
      
    select @idx = 1       
        if len(@String)<1 or @String is null  return       
      
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       
          
        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       
  
        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end  

Just lets see a simpel example of how to use the created split function.

DECLARE @INSTRING AS varchar(300)
SET @INSTRING = '1,2,5'
Select * from Split(@INSTRING,',')
SELECT      *
FROM TableA
Where id IN (Select * from Split(@INSTRING,','))

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM[/PM]-- Oct  2 2008 11:01AM          
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2008                  
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd -- 2008.10.02           
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM[/PM]-- Oct  2 2008 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm -- 02 Oct 2008 11:02:07:577     
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm -- 2008-10-02T10:52:47.513
--Craeting date styles with t-sql string functions in SQL Server
SELECT replace(convert(varchar, getdate(), 111), '/', '') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy

While inserting image files into a SQL Server database table using the OPENROWSET function, I got the following error message from the SQL engine. Msg 491, Level 16, State 1, Line 5 A correlation name must be specified for the bulk rowset in the from clause. Here is the t-sql script that is causing the error message :

CREATE TABLE myTable(Document varbinary(max))
INSERT INTO myTable(Document)
SELECT * FROM OPENROWSET(BULK N'D:\DispatchAdvice.png', SINGLE_BLOB)

And the below sql script displays how the correct statement should be built. Take your attention on the "rs" alias name at the end of the script.

CREATE TABLE myTable(Document varbinary(max))
INSERT INTO myTable(Document)
SELECT * FROM OPENROWSET(BULK N'D:\DispatchAdvice.png', SINGLE_BLOB) rs

INSERT INTO sysdiagrams   
([name]    
,[principal_id]    
,[version]    
,[definition])    
SELECT 'TESTNAME'    
,[principal_id]    
,[version]    
,[definition]    
FROM sysdiagrams    
where diagram_id = 6
  
select * from sysdiagrams

select a.* 
from temp1 a, 
patient b 
WHERE a.chart_no = b.chart_no 
and b.group_assignment = 'AAA'

How do I delete only records from temp1?

DELETE FROM TEMP1
WHERE CHART_NO IN(        
SELECT A.CHART_NO         
FROM TEMP1 A,  
PATIENT B 
WHERE A.CHART_NO = B.CHART_NO  
AND  B.GROUP_ASSIGNMENT = 'AAA' )

If you run into the following error message:
An explicit value for the identity column in table ‘<TABLE_NAME>’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.
One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:

SET IDENTITY_INSERT table_name ON

And then turn it off again when done

SET IDENTITY_INSERT table_name OFF

However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:

INSERT INTO target_table_name (column_name1, column_name2…. column_nameN)
SELECT YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name

In SQL SERVER, I'm working with a table, I want to write something that will create an ID column if it doesn't exist and populate it with the next highest ID.
Query ::

IF NOT EXISTS 
(
    SELECT * 
    FROM   INFORMATION_SCHEMA.COLUMNS 
    WHERE  TABLE_NAME = 'SampleProduct'
    AND    COLUMN_NAME = 'SampleProductId'
) 
BEGIN 
    ALTER TABLE SampleProduct 
    ADD   SampleProductId   INT IDENTITY(1,1) NOT NULL 
END

This will assign identity values for all existing rows, and then provide new numbers as new rows are inserted.

The credentials you provided for the SQL server Agent service are invalid. To continue, provide valid account and password for the SQL server Agent Service.

Today, I have decided to install SQL SERVER 2008 on my PC. All was going fine until I came to server configuration page/tab. I is not proceeding further and asking me to insert correct credential to move to the next page. If you are also getting the same error then just enter following data in Account name and password:

Account Name : "NT AUTHORITY\NETWORK SERVICE "
Password : Blank

I hope this will resolve your issue.

To solve this issue
Go to registry editor
Start --> Run --> Regedit
Expand the keys
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM

Export the ShellSEM key, delete it, install SQL 2008 and then restore it.