June 9, 2014 at 12:26 pm
Hi All,
I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
please help me to do so.
Regards,
Abhishek Patil
PUTTU PATIL
June 9, 2014 at 12:30 pm
abhishekcs.patil (6/9/2014)
Hi All,I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
please help me to do so.
Regards,
Abhishek Patil
PUTTU PATIL
It is not clear what you are trying to do. I take it the columns you mentioned above are returned from a stored procedure? How do you know what datatypes to use? I could make some guesses based on the name but I don't think that is what you have in mind. If you can explain more clearly your requirements we can do our best to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2014 at 4:11 pm
I'm not sure exactly what you need help with, but it sounds like you want to be able to construct a table (temporary or variable) to insert the results of a stored procedure into?
If so, and you cannot just examine the stored procedure to determine the result set metadata, then maybe you could use this:
exec [sys].[sp_describe_first_result_set] N'name of your stored procedure'
If the stored procedure meets the requirements of sp_describe_first_result_set, then it will return a result set that describes the structure of the output of your stored procedure, which you can then use to create your table.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 9, 2014 at 9:12 pm
Hi,
What am trying to do is, am dynamically creating a CSV template using SQL Stored procdure, result of the SP is shown below
SlNo CM_Name Processing_Month Processing Year Billing_Date
1 AAA
2 BBB
so, am getting the Column header of the Stored procedure as shown below,
SlNo,CM_Name,[Transaction_Month],[Transaction_Year],''[Payroll lock date]
am trying to create a temp table for the above columns with datatype, and also i wNT TO REMOVE THE '' FROM THE ABOVE STRING.
June 10, 2014 at 7:27 am
abhishekcs.patil (6/9/2014)
Hi,What am trying to do is, am dynamically creating a CSV template using SQL Stored procdure, result of the SP is shown below
SlNo CM_Name Processing_Month Processing Year Billing_Date
1 AAA
2 BBB
so, am getting the Column header of the Stored procedure as shown below,
SlNo,CM_Name,[Transaction_Month],[Transaction_Year],''[Payroll lock date]
am trying to create a temp table for the above columns with datatype, and also i wNT TO REMOVE THE '' FROM THE ABOVE STRING.
Remember that we can't see your screen and have no knowledge of your process other than what you tell us. At this point you haven't actually told us much at all. You say the above is the results of your procedure but you list 6 columns by name with 2 columns of data. Help us to help you by providing us some details. Otherwise we can't do much but guess.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 9:24 am
I think I understand what you are attempting to do. This code example is one that I use to find column names in one table and use them to generate a stored procedure that creates a table that I use and then drop. Note that in this example, the table that I create, "ARealTable" is NOT a #TempTable. The use of "EXECUTE sp_executesql" prevents me from being able to do so as any #TempTable made within the "EXECUTE sp_executesql" would also be deleted as it runs. This method works, but it does pose a concurrency risk. I have to make sure that the table I create has a name that will never be used to make a permant table in the database. I think that for what you are trying to do, you might be particulary interested in the "SET @ColumnNames" and the "SET @execquery" statements. If any other reader knows how to do this with a true #TempTable instead of temporarily creating a permanent table and then deleting it, I'd like to know.
DECLARE @ColumnNames AS NVARCHAR(MAX)
DECLARE @execquery AS NVARCHAR(MAX)
DECLARE @ActiveTable AS NVARCHAR(MAX)
DECLARE @Catalog_Number AS NVARCHAR(MAX)
/* Check for left over temp tables from previous instances and drop them if they exist */
IF OBJECT_ID('ARealTable') IS NOT NULL
DROP TABLE ARealTable
SET @ActiveTable = 'PL043G_REV10' -- This in another table in my database
SET @Catalog_Number = '043-25-3030' -- This is a real value in the Catalog_Number column in the PL043G_REV10 table
/* Get the Label_File_# columns for the product line */
SET @ColumnNames = (SELECT DISTINCT ColumnNamesFromSource FROM
(SELECT DISTINCT T2.name, SUBSTRING((SELECT ', ' + T1.name AS [text()]
FROM sys.columns T1
WHERE object_id = OBJECT_ID('MyDatabase.dbo.'+ QUOTENAME(@ActiveTable)) AND name LIKE 'Label_File_%'
FOR XML PATH ('')), 3, 100000) ColumnNamesFromSource
FROM sys.columns T2 ) T3
GROUP BY ColumnNamesFromSource)
SELECT @ColumnNames AS DebugResult1 --Only needed for debugging
/* Build the query to create and fill a table with all the columns needed for the product line */
SET @execquery = '(SELECT Catalog_Number, ' + @ColumnNames + ' INTO ARealTable FROM Labeling.dbo.' + QUOTENAME(@ActiveTable) + ' WHERE Catalog_Number = ''' + @Catalog_Number + ''')'
SELECT @execquery AS DebugResult2 --Only needed for debugging
/* Run the query */
EXECUTE sp_executesql @execquery
SELECT * FROM ARealTable -- Only needed for debugging
/* Use the data in "ARealTable" */
{more code that I'm still developing
when done using the data, proceed to drop}
/* Drop temp tables that got created */
IF OBJECT_ID('ARealTable') IS NOT NULL
DROP TABLEARealTable
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply