question

Bhuvan avatar image
Bhuvan asked

Need Help in creating Table but the columns should be Procedure Parameters?

Hi I sticked with a problem, that i do not know to go in which way. I will explain in Step wise I had an output which is retrieved from SELECT PARAMETER_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.PARAMETERS WHERE Specific_Name = 'SP_MyProcName' and the output what i am getting is PARAMETER_NAME DATA_TYPE @MessId int @ToAdd nvarchar @FromAdd nvarchar @MessSubject nvarchar @MessContent nvarchar So the problem is i need to create a table by taking column names and datatypes as above output with some size. Every thing should be done dynamically and i should save this logic in a procedure so that when i send any other procedure name as parameter it should create a table Is the above scenario possible? Regards Bhuvan (Student On MS Technologies)
stored-procedurescreate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered
Are you sure you want to create a table based on the parameters? It seems a little weird to want to do that. Based on the columns of a result set, yes, I could understand - although that is, in itself, a bit of a world of pain. Anyway, to do it from parameters (roughly): DECLARE @tableName [nvarchar](MAX), @procName [nvarchar](MAX) SET @procName = '[Results].[stproc_AddTestScriptResult]' SET @tableName = 'MyTable' DECLARE @sql [nvarchar](MAX) SET @sql = '' SELECT @sql = @sql + QUOTENAME(REPLACE([p].[name], '@', '')) + ' ' + [t].[name] + CASE WHEN [t].[system_type_id] IN (165, 167, 173, 175) THEN ' (' + CASE WHEN p.[max_length] = -1 THEN 'MAX' ELSE CONVERT([varchar], [p].[max_length]) END + ')' WHEN [t].[system_type_id] IN (231, 239) THEN ' (' + CASE WHEN p.[max_length] = -1 THEN 'MAX' ELSE CONVERT([varchar], [p].[max_length] / 2) END + ')' WHEN [t].[system_type_id] IN (106, 108) THEN ' (' + CONVERT([varchar], [p].[precision]) + ', ' + CONVERT([varchar], [p].[scale]) + ')' ELSE '' END + ', ' FROM sys.[parameters] [p] INNER JOIN [sys].[types] [t] ON [t].[user_type_id] = [p].[user_type_id] WHERE [p].[object_id] = OBJECT_ID(@procName) SET @sql = 'CREATE TABLE ' + @tableName + ' ( ' + @sql + ')' PRINT @sql
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
Bhuvan - Matt's solution just needs changing from PRINT @SQL to EXEC(@SQL) - it will then execute the create statement. As for outputting the columns - you just need to print the statement, changing the layout to fit what you want.
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
Awesome +1 - I started to faff around with totally inapproprite dynamic pivots, talk about over-engineering! Should have known you'd rattle something off after editing the OP.
0 Likes 0 ·
Bhuvan avatar image Bhuvan commented ·
Hi MATT THANKS... I mean when i execute this query i will get this output SELECT PARAMETER_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.PARAMETERS WHERE Specific_Name = 'SP_MyProcName' OUTPUT:- PARAMETER_NAME DATA_TYPE @MessId int @ToAdd nvarchar @FromAdd nvarchar @MessSubject nvarchar @MessContent nvarchar After this the table should be created dynamically with the columns as MessId int ToAdd nvarchar(25) FromAdd nvarchar(25) MessSubject nvarchar(25) MessContent nvarchar(25) Is it possible?
0 Likes 0 ·
Bhuvan avatar image Bhuvan commented ·
Wow Matt thats excellent, brilliant....what a script? Thanks a Lot Matt....Love this...thanks a lot..my problem is resolved
0 Likes 0 ·
Bhuvan avatar image Bhuvan commented ·
william Thank you ..thanks a lot
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.