Here's another way to skin the cat. Try using Common Table Expressions. I'm basically doing a recursive query and appending the values back into the columns and then getting the last row which will have all of my arguments and building the print statement with that. The recursion also works great for running totals building a list of e-mails, etc.

While I was able to join colorder = colorder + 1, you may have to do a ROW_NUMBER() function and partition the data if you don't have a sequential column, i.e. If colorder had a break in the numbers (1,5,7,9...)

Then make your own sequence in the first CTE or before the Recursive Query (recur):

ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder) as SeqID

Remember whatever you do check the execution plan.

declare @tabloIsim varchar(50)

declare @crlf varchar(2)

SET @crlf = CHAR(13)+CHAR(10)

set @tabloIsim = '[YourTable]'

declare @spScript nvarchar(4000)

SET @spScript = ''

; --CTE

WITH MyTable as (

select

sc.name as SC_NAME

,case st.name

when 'int' then ''

when 'tinyint' then ''

when 'datetime' then ''

else '('+cast(sc.length as nvarchar(100))+')' end as SC_LEN

,st.name as ST_TYPE

,sc.colorder

,sc.id

from syscolumns sc

inner join systypes st on sc.xtype = st.xtype

where id = object_id(@tabloIsim)

)

, --Next CTE

Sp_Parts AS (

SELECT

id

,'@' + REPLACE(SC_NAME, ' ', '_') + ' ' + ST_TYPE + SC_LEN as Arg

,'@' + REPLACE(SC_NAME, ' ', '_') AS Parm

,'[' + SC_NAME + ']' AS ColName

,colorder

FROM MyTable

)

, --Next CTE (performs a recursive query to append the values into a single column)

Recur AS (

SELECT

id

, CONVERT(NVARCHAR(MAX), Arg) AS Args

, CONVERT(NVARCHAR(MAX), Parm) AS Parms

, CONVERT(NVARCHAR(MAX), ColName) AS ColNames

,colorder

FROM sp_Parts

WHERE colorder = 1

UNION ALL

SELECT r.id

,(r.Args + @crlf + ', ' + s.Arg ) as sp_Args

,(r.Parms + @crlf + ', ' + s.Parm) as sp_Parms

,(r.Colnames + @crlf + ', ' + s.Colname) as sp_Colnames

,s.colorder

FROM Recur r

INNER JOIN SP_Parts s ON

s.ID = r.ID

AND s.colorder = r.colorder + 1

)

/*-- Next CTE

Assign RowNumber via a reverse sort to make the last row = 1.

The last row will contain all of the

Arguments, columns, params in the 3 columns with a CRLF and comma

Between the rows.

*/

, --Final CTE to Select From

AllTheArgs AS (

SELECT

id

,Args

,Parms

,ColNames

,ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder DESC) as AllArgs

FROM Recur

)

--SELECT * FROM AllTheArgs

SELECT @spScript =

'CREATE PROC [dbo].[ins_' + REPLACE(@tabloIsim,'[','')

+ @crlf

+ ' ' + ARGS

+ @crlf

+ 'AS'

+ @crlf

+ 'Insert INTO ' + @tabloIsim + '('

+ @crlf

+ ' ' + ColNames + ')'

+ @crlf + 'values ('

+ @crlf

+ ' ' + Parms

+ @crlf

+ ')' + @crlf

+ 'GO '

FROM AllTheArgs

WHERE AllArgs = 1

PRINT @spScript --TextView will render it properly

Note: I just read the Barry's post. Good catch on the spaces.

My script also didn't account for spaces in the column names, but it has been modified. So in Sp_Parts I changed as follows:

-- From this:

-- ,'@' + SC_NAME + ' ' + ST_TYPE + SC_LEN as Arg

-- ,'@' + SC_NAME AS Parm

-- ,SC_NAME AS ColName

-- To This:

,'@' + REPLACE(SC_NAME, ' ', '_') + ' ' + ST_TYPE + SC_LEN as Arg

,'@' + REPLACE(SC_NAME, ' ', '_') AS Parm

,'[' + SC_NAME + ']' AS ColName