sohairzaki2005
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('('+c ast(charac ter_maximu m_length as varchar(10))+')','') from information_schema.columns
where table_name='your_table'
__________________________ __________ ____-
my stored procedure looks like
create PROCEDURE [dbo].[usp_UserValidateLog in]
(
@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.Organizat ion=@Organ izationID)
or
(OrgUsername=@LoginName
and
OrgPassword=@Password
and
UserOrganization.Organizat ion=@Organ izationID)
)
set @Success=1
End
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('('+c
where table_name='your_table'
__________________________
my stored procedure looks like
create PROCEDURE [dbo].[usp_UserValidateLog
(
@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.Organizat
or
(OrgUsername=@LoginName
and
OrgPassword=@Password
and
UserOrganization.Organizat
)
set @Success=1
End
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lee