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
      )
AS
Begin
      set @Success=0
      if exists (      
                        SELECT  SP.[User].UserID
                        FROM        
                        SP.[User]
                        INNER JOIN
                        SP.UserOrganization
                        ON
                        SP.[User].UserID = SP.UserOrganization.UserID
                        where
                                    (LoginName=@LoginName
                                    and
                                    [Password]=@Password
                                    and
                                    UserOrganization.Organization=@OrganizationID)
                               or
                                     (OrgUsername=@LoginName
                                     and
                                     OrgPassword=@Password
                                     and
                                     UserOrganization.Organization=@OrganizationID)
                        )
      set @Success=1
                        
End

sohairzaki2005Asked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
> --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 ..
0
 
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.

Lee
0
All Courses

From novice to tech pro — start learning today.