Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason

Add a column to table from stored proc

Hello all,

I want to alter a table in a stored proc to add a column dynamically. I am passing the name of the new column to the stored proc param. Does anyone know the syntax for this? Here is the code:

CREATE PROCEDURE procAddNewOptionType

-- new option type parameter

@.OptionName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert new option type into list of types

INSERTINTO dbo.tblClientOptionList(strOptionName)VALUES(@.OptionName);

-- alter client options table to add new column passed to proc as @.OptionName

ALTERTABLE dbo.tblClientOptions ADD @.OptionName BIT;

RETURN 1

END

Thx,

Jason

Hi jason,

You'd have to execute the ALTER TABLE statements dynamically since your column depends on the OptionName being a variable.

declare @.sql nvarchar(100)

SET @.sql = 'alter table dbo.tblClientOptions add ' + @.optionName + ' BIT'
EXEC sp_executesql @.sql

Although, you'd have to take note possible permissions priviledge given by your DBA(ie. they might revoke ALTER TABLE permission)

hth|||

Thank you very much. That works perfectly!

Jason