stored procedure parameter data type automatically gets the column datatype

Is there a way so that in a stored procedure instead of assigning the data type of the parameter I can pull it from the columndata type.
Because sometimes we change the column datatypes and we do not want to go and change it all over the stored procedures we are in the design processes
I know about
select '@'+column_name+' ',data_type+coalesce('('+cast(character_maximum_length as varchar(10))+')','') from information_schema.columns
where table_name='your_table'
my stored procedure looks like
create PROCEDURE [dbo].[usp_UserValidateLogin]
      @LoginName nvarchar(30),--i want instead or writing nvarchar(30) to pull it automatically
      @OrganizationID int,
      @Password nvarchar(20),      
      @Success bit output
      set @Success=0
      if exists (      
                        SELECT  SP.[User].UserID
                        INNER JOIN
                        SP.[User].UserID = SP.UserOrganization.UserID
      set @Success=1

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
You'd have to do the whole thing as a piece of dynamic SQL so you can pull the data type from information.schema as a string and embed that into another string containing the SQL to execute and then do exec (@mySQLString) or something similar. Not nice to read though.

> --i want instead or writing nvarchar(30) to pull it automatically

No.  The data type has to be declared when you create the procedure. That's not to say to you couldn't use something generic, and validate inside the procedure. But IMO that's not the greatest approach.

> dynamic SQL

That can be unsafe.  If the input variables aren't scrubbed (and they contain malicious sql) the bad sql gets executed in the db ..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.