x

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)
more ▼

asked Dec 01 '10 at 02:34 AM in Default

Bhuvan gravatar image

Bhuvan
90 6 6 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Dec 01 '10 at 02:51 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x13

asked: Dec 01 '10 at 02:34 AM

Seen: 694 times

Last Updated: Dec 01 '10 at 02:39 AM