This feature isn't as neat as one would wish. You have to actually pass name/path of each parameter that you want to look up from the parameter store.

Template:

AWSTemplateFormatVersion: 2010-09-09

Description: Example

Parameters:

  BucketNameSuffix:
    Type: AWS::SSM::Parameter::Value<String>
    Default: /example/dev/BucketNameSuffix

Resources:

  Bucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub parameter-store-example-${BucketNameSuffix}

If you don't pass any parameters to the template, BucketNameSuffix will be populated with the value stored at /example/dev/BucketNameSuffix. If you want to use, say, a prod value (pointed to by /example/prod/BucketNameSuffix), then you should specify value for parameter BucketNameSuffix, but instead of passing the actual value you should pass the alternative name of the parameter to use, so you'd pass /example/prod/BucketNameSuffix.

aws cloudformation update-stack --stack-name example-dev \
--template-body file://./example-stack.yml

aws cloudformation update-stack --stack-name example-prod \
--template-body file://./example-stack.yml \
--parameters ParameterKey=BucketNameSuffix,ParameterValue=/example/prod/BucketNameSuffix

A not so great AWS blog post on this: https://aws.amazon.com/blogs/mt/integrating-aws-cloudformation-with-aws-systems-manager-parameter-store/

Because passing million meaningless parameters seems stupid, I might actually generate an environment-specific template and set the right Default: in the generated template so for prod environment Default would be /example/prod/BucketNameSuffix and then I can update the prod stack without passing any parameters.

You can make use of Fn::Join here.

Here is some pseudo code.

You will have to take Environment as an parameter, which you are already doing.

Creating the required string in the resources where DatabaseUrl is required.

Resources :
  Instance :
    Type : 'AWS::Some::Resource'
    Properties :
       DatabaseURL : !Join [ "", [ "/database/", !Ref "Environment" , "/url ] ]

Hope this helps.

Note: You cannot assign value to a Parameter dynamically using some computation logic. All the values for defined parameters should be given as Input.

I like Fn::Sub, which is much cleaner and easy to read.

!Sub "/database/${Environment}/url"