Link to home
Start Free TrialLog in
Avatar of sohairzaki2005
sohairzaki2005Flag for United States of America

asked on

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

Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

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
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial