Stored procedure parameter mapped to table column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure parameter mapped to table column

Author

Topic

SullSull
Starting Member

2 Posts

Posted - 2009-09-23 : 12:09:11

Hi

I want a stored procedure parameter data type to be the same as the data type of a column in a database. I know that this is possible in Oracle e.g. tablename.columnname%type.
So in a stored proc I want to do something like:

CREATE PROCEDURE [CheckCustomer]
@customerid onl_Customers.UserID%type ....

Is this possible in SQL and if so what's the syntax?

Thanks in advance

Ian.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-23 : 12:52:40

There is no Dynamic pointer that I know of in SQL Server

The benefit of that is, if he datatype change, the sproc inherits the change

It doesn't work that way here

You need to explicitly code the type

CREATE PROC mySproc99
@inpvar varchar(10)
, @outvar int OUTPUT
AS
.....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 12:57:06

Just to aid in typing and to insure you get the names and datatypes correct you can use the view [information_schema].[columns] to generate the parameter code.

Be One with the Optimizer
TG

SullSull
Starting Member

2 Posts

Posted - 2009-09-24 : 05:07:39

Thanks Brett and TG.
I suppose I could use an sql_variant type but that's not very nice.

TG - Could you explain further the "view [information_schema].[columns]". It sounds useful but I've been unable to write it? Could you give me a code example?

Ta

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 06:40:53

quote:
Originally posted by SullSull

Thanks Brett and TG.
I suppose I could use an sql_variant type but that's not very nice.

TG - Could you explain further the "view [information_schema].[columns]". It sounds useful but I've been unable to write it? Could you give me a code example?

Ta

Something like this

select '@'+column_name+' ',data_type+coalesce('('+cast(character_maximum_length as varchar(10))+')','') from information_schema.columns
where table_name='your_table'

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 10:13:30

I believe I have some code that does this...Yep - found it. It was not heavily used so you may need to beef it up or make corrections but it worked for the columns in my tables at the time


declare @tableName nvarchar(128)

--------------------------------------------
--set inputs:
use <db>
set @tablename = N'<tableName>'
--------------------------------------------

select '@' + column_name + replicate(' ', 40-len(column_name))
       + data_type 
       + case 
              when data_type like '%char' or data_type like '%binary' then ' (' 
              + case 
                     when convert(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' then 'max' 
                     else convert(varchar, CHARACTER_MAXIMUM_LENGTH) 
                     end + ')'
              when data_type in ('decimal', 'numeric') then ' (' + convert(varchar, NUMERIC_PRECISION) + ', ' + convert(varchar, numeric_scale) + ')' 
              when data_type = 'float' then '(' + convert(varchar, numeric_precision) + ')'
              else ''
              end
       + case when IS_NULLABLE = 'NO' then ' NOT' else '' end + ' NULL'

from   information_schema.columns
where  table_name = @tableName
order by ordinal_position

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-24 : 10:21:30

quote:

Originally posted by TG

I believe I have some code that does this...Yep - found it. It was not heavily used so you may need to beef it up or make corrections but it worked for the columns in my tables at the time


declare @tableName nvarchar(128)

--------------------------------------------
--set inputs:
use <db>
set @tablename = N'<tableName>'
--------------------------------------------

select '@' + column_name + replicate(' ', 40-len(column_name))
       + data_type 
       + case 
              when data_type like '%char' or data_type like '%binary' then ' (' 
              + case 
                     when convert(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' then 'max' 
                     else convert(varchar, CHARACTER_MAXIMUM_LENGTH) 
                     end + ')'
              when data_type in ('decimal', 'numeric') then ' (' + convert(varchar, NUMERIC_PRECISION) + ', ' + convert(varchar, numeric_scale) + ')' 
              when data_type = 'float' then '(' + convert(varchar, numeric_precision) + ')'
              else ''
              end
       + case when IS_NULLABLE = 'NO' then ' NOT' else '' end + ' NULL'

from   information_schema.columns
where  table_name = @tableName
order by ordinal_position

Be One with the Optimizer
TG

This reminds me my post about Generate SQL Script

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53007

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 10:42:40

I'll bet most serious sql developers have come up with similar code. I know I always prefer to spend an extra hour or two coding a reusable solution then mindlessly type column names and attributes over and over. I've got an older version (pre information_schema) that has options for
(create table, produce SELECT list, insert VALUES clause, generate params) :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources