June 10, 2014 at 12:00 am
Hi All,
I have created a stored procedure for retrieving column name, as shown below
CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]
now i trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.
PUTTU PATIL
June 10, 2014 at 12:29 am
Can you please provide more inputs on the same.
1. What is your SP doing.
2. If possible, please provide SP script.
3. Your question with expected resultset.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 10, 2014 at 12:53 am
Can you please provide more inputs on the same.
1. What is your SP doing.
2. If possible, please provide SP script.
3. Your question with expected resultset.
ALTER PROCEDURE [dbo].[proc_PDC_CDC_Dynamic_Generate_CSV_Table]
@Condition varchar(50),
@userid varchar(50),
@output varchar(50) out,
@Message varchar(max) out
AS
BEGIN
--SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
DECLARE @listCol VARCHAR(2000)
DECLARE @cols1 varchar(max),@finalCols varchar(max)
DECLARE @sql varchar(max)
if(@Condition='PDC')
BEGIN
SELECT @listCol = STUFF(( SELECT'],[' + CAM_Name FROM Calendar_Activity_Master
where CAM_Type='PDC' and CAM_Set_Target='Y'
ORDER BY '],[' + CAM_Name asc FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols1=Replace(@listCol, ',', ',''''')
set @cols1=@cols1
select @finalCols=CONCAT('''''',@cols1)
set @listCol = 'CM_id,CM_Name,''''[Transaction_Month],''''[Transaction_Year],' +@finalCols
set @sql = 'select ROW_NUMBER() OVER(ORDER BY CM_id)[SlNo],' + @listCol + 'from ELCSPortal.dbo.Corporation_Master where CM_Status =''A'' '
print @sql
exec ( @sql )
set @Message = 'CM_id,CM_Name,[Transaction_Month],[Transaction_Year],'+@finalCols
print @Message
--CM_id,CM_Name,''[Transaction_Month],''[Transaction_Year],''[Billing lock date]
--CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Billing lock date]
select @Message
END
ELSE
BEGIN
SELECT @listCol = STUFF(( SELECT'],[' + CAM_Name FROM Calendar_Activity_Master
where CAM_Type='CDC' and CAM_Set_Target='Y'
ORDER BY '],[' + CAM_Name asc FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols1=@listCol
set @cols1=@cols1
select @finalCols=@cols1
set @listCol = 'CM_id,CM_Name,[Transaction_Month],[Transaction_Year],' +@finalCols
set @sql = 'select ROW_NUMBER() OVER(ORDER BY CM_id)[SlNo],' + @listCol + 'from ELCSPortal.dbo.Corporation_Master where CM_Status =''A'' '
print @sql
set @Message = 'CM_id, CM_Name, [Transaction_Month], [Transaction_Year], '+@finalCols
print @Message
select @Message
--CM_id,CM_Name,''[Transaction_Month],''[Transaction_Year],''[Billing lock date]
--exec ( @sql )
END
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC PROC_ALCS_RECORDEXCEPTION 'proc_PDC_CDC_Generate_CSV',@USERID
SET @OUTPUT = 0
END CATCH
END
--EXEC proc_PDC_CDC_Dynamic_Generate_CSV_Table 'CDC', 'user', '',''
OutPut From The Above SP
--CM_id,CM_Name,[Transaction_Month],[Transaction_Year],[Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]
So Above are the columns generated dynamically, i need to create temp table with data type as varchar(50) for each
June 11, 2014 at 4:44 am
Is this what you are after?
CREATE TABLE dbo.Corporation_Master(Col1 INT,Colb Varchar(100),Colc MONEY NULL)
GO
WITH cte(ColName,Ordinal_Position) AS(
SELECT 'CM_id',1
UNION ALL SELECT 'CM_Name',2
UNION ALL SELECT 'Transaction_Month',3
UNION ALL SELECT 'Transaction_Year',4
UNION ALL
SELECT
Column_Name,Ordinal_Position+4
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Schema='dbo'
AND Table_name='Corporation_Master')
,cte2(ColName,Ordinal_Position)
AS (SELECT
TOP (1024)
ColName,
Ordinal_Position
FROM cte ORDER BY Ordinal_Position)
SELECT 'CREATE TABLE dbo.#SomeTempTable(' +
(SELECT STUFF((SELECT
TOP (1024)
','+ColName+' VARCHAR(50)'
FROM cte2 FOR XML PATH('')),1,1,''))+')'
GO
DROP TABLE dbo.Corporation_Master
GO
-- Result
-- CREATE TABLE dbo.#SomeTempTable(CM_id VARCHAR(50),
-- CM_Name VARCHAR(50),Transaction_Month VARCHAR(50),
-- Transaction_Year VARCHAR(50),Col1 VARCHAR(50),
-- Colb VARCHAR(50),Colc VARCHAR(50))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply