Get Stored Procedure Result to A Table

  

--USE yourDB 
--GO
----Get Stored procedure result into a table

;with mycte as (
SELECT distinct p.name, r.column_ordinal,r.name colName,r.system_type_name,r.is_nullable 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
WHERE p.name not like'sp_%' --no system sp
and p.name ='getYourSpName'  
)

 

  SELECT   N'If Object_id('''+QUOTENAME('Show_SpResult_'+m1.name)+''',''U'') is not null DROP Table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  + ' Create table '
  + QUOTENAME('Show_SpResult_'+m1.name) 
  +'('+ STUFF((SELECT  ', ' + QUOTENAME(Colname) + ' ' 
  + system_type_name + Case when is_nullable=1 then  ' NULL' Else ' Not NULL' END
	  FROM mycte AS m2
	  WHERE m1.name=m2.name
	  ORDER BY m2.column_ordinal
	  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
	  +');  INSERT INTO '+QUOTENAME('Show_SpResult_'+m1.name)+ ' EXEC '+ Name 
	  + ';  SELECT * FROM '+QUOTENAME('Show_SpResult_'+m1.name) +';' as QueryToBeExecuted
 FROM mycte AS m1
 Group by name

 --Copy the result and execute to get the result (you need to pass in parameters' values if your sp has them )
 --We can make this script to run dynamically

 
 --More information about sys.dm_exec_describe_first_result_set_for_object 
 -- https://msdn.microsoft.com/en-us/library/ff878236%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


 
Advertisements

Sample Query for a Question: “Select IN and OUT time based on flag”

  

create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:44:10.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:45:15.224','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
--insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:44:00.601','O')
insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O') 
 

;with mycte as (
select *, row_number() Over(Partition by EmpID Order by CheckedTime)
-row_number() Over(Partition by EmpID, CheckedType Order by CheckedTime) grp
 from #temp)
 ,mycte1 as (
 Select *
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime ASC) rnFirst
 , row_number() Over(Partition by EmpID, CheckedType, grp Order by CheckedTime DESC) rnLast
   from mycte)

 Select EmpID,CheckedTime, CheckedType
 ,Case when rnFirst=1 and rnLast>1 then 'First_'
 when rnFirst>1 and rnLast=1 then 'Last_'
 End +CheckedType as checkCol 
 from mycte1
  Order by CheckedTime

drop table #temp
 

 

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c9ca548b-af34-4726-9ffe-92351369c981/select-in-and-out-time-based-on-flag?forum=transactsql


A Sample to Transform JSON File from barchart.com to SQL Server Table (SQL Server 2016)

  
--Sample to download json file with your own apikey (paid subscription )
--http://ondemand.websol.barchart.com/getGrainBids.json?apikey=1234yourapikey789&zipCode=00000&maxDistance=100&getAllLocations=1&getAllBids=1

---a table to hold json file data
Drop  table  if exists dbo.RawData_JsonFileTemp

Declare @sql nvarchar(max);
Declare @ColumnHeaders VARCHAR(MAX) 

declare @json1 varchar(max) 
--
SELECT @json1=BulkColumn--,isjson(BulkColumn)
  FROM OPENROWSET (BULK 'C:\temp\JSON\getGrainBids.json', SINGLE_CLOB) t

;with mycteSrc as (
 SELECT  c.[key],  c.[value] 
 FROM OPENJSON(@json1, '$.results') as c
 
)

,myctebids as (
Select  d.value
from mycteSrc
cross apply  OPENJSON ( value,  '$.bids' )   d
)
,myColumnList as (
select 1 as seq, e.[key] ,   'JSON_Value (b.value, ''$.'+e.[key]+ ''') as '+ quotename(e.[key])   col
from myctebids
cross apply   OPENJSON ( value) e
UNION 
Select 2 as seq,  d.[key] ,   'JSON_Value (c.value, ''$.'+d.[key]+ ''') as '+ quotename(d.[key])  
from mycteSrc
cross apply   OPENJSON ( value ) d
)


Select @ColumnHeaders = STUFF( (SELECT  ','   + col  
  FROM myColumnList
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 
 --Print @ColumnHeaders
 Select @sql='SELECT '+@ColumnHeaders+ '
  into RawData_JsonFileTemp from OPENJSON('''+ @json1+'''  , ''$.results'') as c
 CROSS APPLY OPENJSON (c.value, ''$.bids'')  b
 '
 --print @sql
EXEC sp_executesql @sql;


--Result
 SELECT *  FROM dbo.RawData_JsonFileTemp 
 
 


 

Truncation Issue with Importing Text File to SQL Server (“Text was truncated or…”)

You can use right click your database name >>Tasks>>Import data… to launch SQL Server Import and Export Wizard >>click on Next>> choose data source >>pick Flat File Source >> browse to the location of your text file>> there are many settings you can play with and I am describing them here. You can go with default for most settings and give it a try.
Sometimes you may run into an annoying truncation error:
“Data conversion failed. The data conversion for column “xxxxxx” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page “

This is

One or more columns in your text file have larger column sixe than the target table. You can follow the error message to increase your target table column size. The default for all columns are 50.
If you load your text file into a staging table, you can quickly change the default size to a larger one to finish up the loading process.

…. pick Flat File Source >> Choose a data source : browse to the location of your text file >> click on Advanced. Choose the first column and then hold down the shift key to select all columns you want to change size >> Change the Outputcolumnwidth size from 50 to a larger number, for example 100 or 2000. All column sizes will be changed to the new size.
Continue with the process and the truncation error should be fixed.

Another way to import text file is a method that I prefer to use.
Use bcp to import text file data with a format file into staging table. First to create a target table with proper column size and run bcp to create a format file and run bcp command with the format file to load the text file very quickly. The target table columns should have big enough size to receive the text file data to avoid truncation error


Relational Division Sample Query

  

CREATE TABLE tbl_Division(
   DivisionID   INT  NOT NULL PRIMARY KEY 
  ,DivisionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Division(DivisionID,DivisionName) 
VALUES (1,'Division A'),  (2,'Division B'), (3,'Division C');


CREATE TABLE tbl_Region(
   RegionID INT  NOT NULL PRIMARY KEY 
  ,RegionName VARCHAR(100) NOT NULL
);
INSERT INTO tbl_Region(RegionID,RegionName) 
VALUES (1,'North America'),(2,'South America'),(3,'Europe');


CREATE TABLE tbl_DivisionRegion(
   DivisionRegionID INT  NOT NULL PRIMARY KEY 
  ,DivisionID       INT  NOT NULL
  ,RegionID         INT  NOT NULL
);

ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (DivisionID) REFERENCES tbl_Division(DivisionID);
ALTER TABLE tbl_DivisionRegion
ADD FOREIGN KEY (RegionID) REFERENCES tbl_Region(RegionID);


INSERT INTO tbl_DivisionRegion(DivisionRegionID,DivisionID,RegionID) 
VALUES (1,1,1),(2,1,2),(3,2,3), (4,3,1),(5,3,3);


--Option 1 to use EXISTS

--Question 1

SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )

ORDER BY dr.DivisionID, dr.RegionID
/*
DivisionName	RegionName
Division A	North America
Division A	South America
*/

--Question 2
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )

ORDER BY dr.DivisionID, dr.RegionID
/*

DivisionName	RegionName
Division C	North America
Division C	Europe


*/

--Question 3
SELECT d.DivisionName, r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID 
WHERE 
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='North America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='South America' )
AND
Exists (Select 1 from tbl_DivisionRegion dr2 JOIN tbl_Region r ON dr2.RegionID = r.RegionID where d.DivisionID=dr2.DivisionID and r.RegionName ='Europe' )

ORDER BY dr.DivisionID, dr.RegionID
/*

DivisionName	RegionName

*/

 
---Option 2
 --Pass your region checklist
 declare @RegionName1 varchar(50)='North America'  
 declare @RegionName2 varchar(50)= 'South America' -- NULL
 declare @RegionName3 varchar(50)=   NULL   --'Europe'  


 ;with checklist as (
 Select RegionName from (values (@RegionName1),(@RegionName2) ,(@RegionName3)) d(RegionName )
 )

 ,mycteSrc as (
 SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
 )
 
 ,mycteProcessing as (
 SELECT src.DivisionName, CASE WHEN COUNT(src.RegionName) > (SELECT COUNT(RegionName) FROM checklist ) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName)FROM checklist )
            THEN 'match with extra'
            WHEN COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist) AND COUNT(ckl.RegionName) = (SELECT COUNT(RegionName) FROM checklist)
            THEN 'exactly match'
            WHEN MIN(ckl.RegionName) IS NULL
            THEN 'none'
            ELSE 'some' END AS location_status
  FROM   mycteSrc AS src LEFT JOIN  checklist AS ckl   ON src.RegionName = ckl.RegionName
  GROUP BY src.DivisionName )
 
  Select a.DivisionName,a.RegionName  
  from mycteSrc a join mycteProcessing m 
  on a.DivisionName =m.DivisionName 
   Where m.location_status='exactly match'

 



 -- option 3

;with checklist as (
 Select @RegionName1 as RegionName
 Union all
  Select @RegionName2 as RegionName
 Union all
  Select @RegionName3 as RegionName 
 )

 ,mycteSrc as (
 SELECT d.DivisionName, d.divisionID, r.RegionID,r.RegionName
FROM tbl_Division d
LEFT JOIN tbl_DivisionRegion dr ON d.DivisionID = dr.DivisionID
LEFT JOIN tbl_Region r ON dr.RegionID = r.RegionID
 )
 
 
 SELECT src.DivisionName           
  FROM   mycteSrc AS src LEFT JOIN  checklist AS checklist   
  ON src.RegionName = checklist.RegionName
   GROUP BY src.DivisionName     
  Having( COUNT(src.RegionName) = (SELECT COUNT(RegionName) FROM checklist) 
  AND COUNT(checklist.RegionName) = (SELECT COUNT(RegionName) FROM checklist) 
  )
 

 



drop table  tbl_DivisionRegion,tbl_Division, tbl_Region 

--You can search the term Relational division to learn more on this topic


 

https://forums.asp.net/t/2127017.aspx?MS+SQL+Query+Exclusive+Where


Get Next Sunday With Different DATEFIRST Setting

  
 
 SET DATEFIRST 1; 
--SET DATEFIRST 7; 

Declare @TimeByDay Date
Set @TimeByDay = '2017-11-12'

--returns next Sunday in the week with any DATEFIRST value
Select   DATEADD(dd, 6-(((DATEPART(dw,@TimeByDay)+@@DATEFIRST) % 7)+5) % 7 , @TimeByDay) 

--not working  for  DATEFIRST<>1  
select DATEADD(dd,-(DATEPART(dw, @TimeByDay)-7), @TimeByDay) as WeekEndDate

--US default
SET DATEFIRST 7; 

 

A Recursive Sample

  
 
 create table Category (PKey int, Description varchar(20), ParentCategoryKey int)
Insert into Category values(1,'Car',null)
,(2,'Truck',null)
,(3,'Ford',1)
,(4,'Mustang',3)
,(5,'Lexus',1)
,(6,'pickup',2)

;with mycte as (
Select PKey,Description, PKey as PKey2, ParentCategoryKey ,    Cast(Cast(pkey as varchar(10))+ '-'  as varchar(200))   AS pkeys  from Category 
WHERE ParentCategoryKey is null
Union all
Select c.PKey,c.Description , PKey2 , c.ParentCategoryKey,  Cast(m.pkeys+ Cast(c.pkey as varchar(10))+'-' as varchar(200)) 
from mycte m join  Category c on c.ParentCategoryKey=m.pkey)

Select PKey,Description, ParentCategoryKey, pkeys
 from mycte
WHERE   
'-'+(  select pkeys  from mycte 
where Description='Mustang') like '%-'+Cast(pkey as varchar(10))  +'-%'
 

drop table Category

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e263a3cb-af26-4a4a-991a-ffe855a7d4ae/query-tables-parent-key-and-ancestors?forum=transactsql