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

Question

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)

Answer 1

0

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

answered Dec 01 '10 at 02:51 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

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.

Dec 01 '10 at 02:57 AM WilliamD

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?

Dec 01 '10 at 03:00 AM Bhuvan

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.

Dec 01 '10 at 03:04 AM WilliamD

Wow Matt thats excellent, brilliant....what a script? Thanks a Lot Matt....Love this...thanks a lot..my problem is resolved

Dec 01 '10 at 03:06 AM Bhuvan

william Thank you ..thanks a lot

Dec 01 '10 at 03:07 AM Bhuvan

(comments are locked)

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

Follow this question

Related Questions