« PowerShell BizTalk Project Deployment Settings | Main | Oracle and BizTalk Server, Part 2 »
Monday
May142012

TSQL for insert sproc parameters

Just putting this here so it’s easy for me to find. TSQL that formats parameters for a stored procedure that does a simple insert into a table. Clearly this doesn't support all scenarios but it sure beats typing it out (or paying for a fancy app).

 
-- sproc params 

SELECT
replace(
'@' + c.name + space(1)+
case bt.name when 'varchar' then 'varchar('+convert(varchar(3),c.length)+')'
when 'decimal' then 'decimal('+convert(varchar(3),c.xprec)+','+convert(varchar(3),c.xscale)+')'
else bt.name END + ',', 'varchar(*)', 'varchar(max)')
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID('TableName')
ORDER BY c.colid

-- table columns

SELECT
c.name +','
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID('TableName')
ORDER BY c.colid

-- values

SELECT
'@' + c.name + ','
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID('TableName')
ORDER BY c.colid

-- update

SELECT
c.name + ' = ' + '@' + c.name + ','
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID('TableName')
ORDER BY c.colid


Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>